Protect the formulas in a sSpreadsheet from being changed–Excel 2010 (June 2015)

This has to be set on each sheet in a workbook. These steps assume you want to be able to make new entries in blank areas.

Be aware that all worksheets by default, start with all cells locked, which does nothing until protection is turned on. So you have to unlock the cells you want to use, and then be sure the cells with formulas are locked. Then apply the protection. This is a good, if not the best, way to apply protection and not miss any cells. Like everything with computers there’s 2-5 ways to do something.

1. Select all the cells of the worksheet
(Click in the blank above row 1 and to the Left of A, see pink circle.) All cells in the spreadsheet are highlighted.

2. On the Home tab, ClickFormat, Lock Cell. Before you click, the lock cell icon has a gold border. Clicking it will unlock all cells. The lock cell will not have a gold border, if you want to look, (don’t click).

3. Click Find & Select (last icon), Formulas. This selects all formulas.

4. Click Format, Lock Cell. Before you click, the lock cell is clear. Clicking it will lock the formula cells.

5. Click Format, Protect Worksheet, Click OK. You do not have to enter a password, unless you want to keep unwanted people away from making changes to formulas. If you want, you can give permission to do some things to formula cells, e.g. you might want to highlight some totals with color.

If no password is set, all anyone has to do to remove the protection is click Format, Unprotect Worksheet.

Make a sample spreadsheet to test some of the things you may want to allow after formulas are protected. Then go back and try unchecking it. Note that Sort and auto filter do not work in a protected sheet.

2015-06-13