Excel 2024: Protect All Formula Cells

October 21, 2024 - by Bill Jelen

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.

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.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Kaffeebart on Unsplash