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.

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.

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!

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.