To avoid messing up your formulas, you can lock the cells that contain formulas while keeping all other cells in your Microsoft Excel spreadsheets unlocked. We’ll show you how.
TIED TOGETHER: How to lock cells in Microsoft Excel to prevent editing
How to lock the cells with formulas in Excel?
By default, when you protect your worksheet, Excel locks all cells in your sheet, not just those that contain formulas. To get around this, you must first unlock all your cells, select the cells that contain formulas, and then lock those cells with formulas.
This allows users to edit the values of all cells in your worksheet except those that contain formulas.
Lock a formula cell in Excel
To avoid changing your formula cells, start your spreadsheet with Microsoft Excel first.
Select all cells in your spreadsheet by pressing Ctrl+A (Windows) or Cmd+A (Mac). Then right-click any cell and choose Format Cells.
In the Format Cells window, select the Protection tab from the top. Then uncheck the “Locked” option and click “OK”.
All cells in your worksheet are now unlocked. Now, to lock the cells that contain formulas, first select all these cells.
To do this, click the Home tab at the top of the Excel ribbon. Then, in the Editing section, choose Find & Select > Go To Special.
In your spreadsheet, Excel has highlighted all the cells that contain formulas. To lock these cells now, right click on any of these cells and select “Format Cells”.
In the Format Cells window, access the Protection tab. Then check the “Locked” option and click “OK”.
The cells that contain formulas in your worksheet are now locked. To prevent their modification, select the “Review” tab at the top of the Excel ribbon.
On the Review tab, click the Protect Sheet option.
You will see a “Protective Sheet” box. Optionally enter a password in the “Password to Unprotect Sheet” field. Then click OK”.
If you used a password in the previous step, re-enter that password in the Confirm Password box that opens and click OK.
And that’s it. All cells containing formulas in your worksheet are now locked. If you or anyone else tries to change the contents of these cells, Excel will display an error message.
To allow users to edit these formula cells later, unprotect your worksheet by accessing the “Review” tab and selecting “Unprotect Sheet”.
And this is how you avoid your formulas getting mixed up in your Excel spreadsheets. Very helpful!
Don’t want your charts to move around in Excel? If yes, there is a way to lock the position of your charts in this spreadsheet program. Check out our guide to learn how to use it.
TIED TOGETHER: How to lock the position of a chart in Excel
This article was previously published on Source link
