MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Protect All Formula Cells


October 17, 2019 - by Bill Jelen

Excel Protect All Formula Cells. Photo Credit: Stéphan Valentin at Unsplash.com

The use of worksheet protection in Excel is a little strange. Using the steps below, you can quickly protect just the formula cells in your worksheet.

It seems unusual, but all 16 billion cells on a worksheet start out with their Locked property set to True. You need to unlock all of the cells first:

  1. Select all cells by using the icon above and to the left of cell A1.
  2. Press Ctrl+1 (that is the number 1) to open the Format Cells dialog.
  3. In the Format Cells dialog, go to the Protection tab. Uncheck Locked. Click OK.
Select all cells and change the Locked property to off.

While all cells are still selected, select Home, Find & Select, Formulas.


At this point, only the formula cells are selected. Press Ctrl+1 again to display the Format Cells dialog. On the Protection tab, choose Locked to lock all of the formula cells.

With only the formula cells selected, set Locked property to On.

Locking cells does nothing until you protect the worksheet. On the Review tab, choose Protect Sheet. In the Protect Sheet dialog, choose if you want people to be able to select your formula cells or not.

Note

Scroll down a few rows in the Protect Sheet dialog box to reveal popular choices: Use AutoFilter and Use PivotTable & PivotChart. If you want allow people to interact with these features, scroll down and select them.

Caution

Don’t bother putting in a password. Passwords are easily broken and easily lost. You will find yourself paying $39 to the Estonians who sell the Office password-cracking software.

Use the Protect Sheet icon on the Review tab. You can choose what people can do to a protected sheet. In this screenshot, only Select Unlocked Cells is available.

Title Photo: Stéphan Valentin at Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.