[ad_1]
To avoid cluttering your formulas, you can lock the cells that contain formulas while keeping all other cells unlocked in your Microsoft Excel worksheets. We’ll show you how to do just that.
RELATED: How to lock cells in Microsoft Excel to prevent editing
How do you lock cells containing formulas in Excel?
By default, when you protect your worksheet, Excel locks all the cells in your sheet, not just the ones that contain formulas. To prevent that, you’ll need to unlock all your cells first, select the cells that contain formulas, and then lock these cells with formulas.
This way, users can edit the values of all cells in your spreadsheet, except those with formulas.
Lock a formula cell in Excel
To prevent cells in your formula from being tampered with, first start your spreadsheet with Microsoft Excel.
In your spreadsheet, select all cells by pressing Ctrl + A (Windows) or Command + A (Mac). Then right click on any cell and choose “Format Cells”.
In the “Format Cells” window, from the top select the “Protection” tab. Then disable the “Blocked” option and click “OK”.
All cells in your worksheet are now unlocked. To now lock the cells that contain formulas, first select all these cells.
To do that, on the Excel ribbon at the top, click the “Home” tab. Then, from the “Edit” section, choose Find & Select > Go To Special.
In your spreadsheet, Excel has highlighted all the cells that contain formulas. To now lock these cells, right click on any of these cells and choose “Format Cells”.
In the “Format Cells” window, go to the “Protection” tab. Then enable the “Blocked” option and click “OK”.
Cells containing formulas in your worksheet are now locked. To prevent its modification, from the Excel ribbon at the top, select the “Review” tab.
On the “Review” tab, click the “Protect Sheet” option.
You will see a “Protect Sheet” box. Here, optionally, enter a password in the “Password to unprotect sheet” field. Then click “OK”.
If you used a password in the previous step, in the “Confirm Password” box that opens, re-enter that password and click “OK”.
And that is. All cells containing formulas in your worksheet are now locked. If you or someone else tries to change the contents of these cells, Excel will display an error message.
Later, to allow users to edit these formula cells, unprotect your worksheet by going to the “Review” tab and selecting “Unprotect Sheet”.
And that’s how you keep your formulas from getting mixed up in your Excel spreadsheets. Very useful!
Don’t want your charts to move around in Excel? If so, 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.
RELATED: How to Lock the Position of a Chart in Excel
[ad_2]
