How to quickly lock (protect) all of the cells containing formula

There are many values and formulas on the sheet. We want to protect all formulas against overwriting. Other cells must remain unlocked for entry. We don’t need to manually go through cell by cell.

This is a table with some formulas:

Let’s see how to do that:

Step 1

Select all of cells (you can use CTRL+A, click on the  button on the top left  or just select cells by mouse). Then do right click in the selected area and select Format Cells…

Uncheck box Locked and press OK.

Step 2

Now go to Home on the Ribbon and select Formulas in the Find & Select menu.

Excel will select only the cells containing the formula.

Now do right click on one of the selected cell and go to the Format Cells… menu, as in Step 1. Check Locked checkbox and press OK. So now we’re ready and we can lock the sheet.

Step 3

Go to the Review and select Protect Sheet.

In the Protect Sheet menu you can add password to unprotect sheet and you can allow some action in protected sheet. And that’s it, click OK and the formulas will be protected against overwriting.

See this in the video tutorial: