If you’re a Microsoft Excel user, you know how to lock formulas. But what if you want to lock formulas for all sheets in a workbook? That’s easy - just use the Formula button on the ribbon.
To avoid getting your formulas messed up, you can lock the cells that contain formulas while keeping all other cells unlocked in your Microsoft Excel spreadsheets. We’ll show you how to do just that.
RELATED: How to Lock Cells in Microsoft Excel to Prevent Editing
How Do You Lock the Cells Containing Formulas in Excel?
By default, when you protect your worksheet, Excel locks all the cells in your sheet and not just the ones containing formulas. To get around that, you’ll have to first unlock all your cells, select the cells containing formulas, and then lock these cells with formulas.
This way, users can edit the values of all cells in your worksheet except for the ones that have formulas in them.
Lock a Formula Cell in Excel
To avoid getting your formula cells altered, first, launch your spreadsheet with Microsoft Excel.
In your spreadsheet, select all cells by pressing Ctrl+A (Windows) or Command+A (Mac). Then right-click any one cell and choose “Format Cells.”
On the “Format Cells” window, from the top, select the “Protection” tab. Then disable the “Locked” 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, in Excel’s ribbon at the top, click the “Home” tab. Then, from the “Editing” section, choose Find & Select > Go To Special.
In the “Go To Special” box, enable the “Formulas” option and click “OK.”
In your spreadsheet, Excel has highlighted all the cells containing formulas. To now lock these cells, right-click any one of these cells and choose “Format Cells.”
On the “Format Cells” window, access the “Protection” tab. Then enable the “Locked” option and click “OK.”
The cells containing formulas in your worksheet are now locked. To prevent their modification, from Excel’s ribbon at the top, select the “Review” tab.
In the “Review” tab, click the “Protect Sheet” option.
You’ll 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, then in the “Confirm Password” box that opens, re-enter that password and click “OK.”
And that’s it. All the cells containing formulas in your worksheet are now locked. If you or someone else attempts to alter the contents of these cells, Excel will display an error message.
Later, to allow users to edit these formula cells, then unprotect your worksheet by accessing the “Review” tab and choosing “Unprotect Sheet.”
And that’s how you avoid getting your formulas messed up in your Excel spreadsheets. Very useful!
Don’t want your charts moved around in Excel? If so, there’s 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
