Review formula in a protected sheet

If you receive spreadsheets that are protected at a cell level, it can sometimes be annoying that Excel allows you to click on a cell and see the underlying formula, but when you want to follow the formula using either the Formula Auditing tool, or by pushing F2 and using the coloured boxes, it won’t allow it. There is a trick to allow you to review formula in a protected sheet.

Watch the video below to see how it is done or read the text below. Note for the video, the shortcut to the show formula trick is clicking CTRL + ~ which alternates between showing the results in the cells versus the formula.

So as shown below, if we want to use the tools to see what is happening with cell E13, the Formula Auditing buttons don’t work (greyed out) and clicking on the cell and using F2 results in an error message.

review formula in a protected sheet
The Formula Auditing buttons are greyed out, and clicking in the cell gives you an error message

This means that you have to manually follow the links by remembering the cell references.

However there is a tool that allows a bit of a compromise.

Notice that below, whereas the Formula Auditing buttons like Trace Precedent are greyed out, the Show Formula button is still active. If you click this it shows the formulas in the underlying cells (instead of the resultant values), but more importantly notice that the coloured boxes now work and you can visually see where the formula is looking by simply clicking on the cell. If you need to do this often, the shortcut is CTRL + ~ .

review formula in a protected sheet 2
Use Show Formula to make the coloured cells work on a protected sheet

Although this is not as good as using the Formula auditing toolbar, it is better then manually trying to understand how a formula works and is at least a start to reviewing formula in a protected sheet

Obviously the best is to get the password so that you can properly review the spreadsheet or use a Spreadsheet Auditor to review the spreadsheet.

Excel protection for POPI and GDPR Compliance- Free course on all the options