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.
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.
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, enters a password in the “Password to UnprotectSheet” 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!
