Display all Formulas at Once in Excel

How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily.

Steps to Display all Formulas at Once in Excel

1. Go to the worksheet where you want to view the formulas (this feature is worksheet specific).
2. Go to the Formulas tab and then click Show Formulas or use the keyboard shortcut  Ctrl + ` (that is an accent, the button located above the Tab key on the left side of the keyboard usually).

Here is the worksheet before displaying the formulas:

Here is the worksheet after displaying the formulas:

You can see that, not only are the formulas displayed, but all of the number formatting that was applied to the cells is removed.  There is no more currency or percentage formatting and this is done in an attempt to make it easier to read the spreadsheet, though that is not always how it works out.

Steps to Go Back to Normal and Hide the Formulas

This is the same as showing the formulas.

1. Go to the worksheet where you want to hide the formulas and go back to normal (this feature is worksheet specific).
2. Go to the Formulas tab and click Show Formulas or use the keyboard shortcut  Ctrl + ` (that is an accent, the button located above the Tab key on the left side of the keyboard usually).

Quickly View Ranges Used in Formulas

Once you have shown the formulas, you only have to select a cell to show all of the cell references that are used in the formula.  Normally you have to double-click a cell to do this.  In some cases, it will also enlarge the text of the selected cell so it is easier to read.

Navigate with the arrow keys to do this quickly and it helps to troubleshoot a spreadsheet even more.

Why Should You Show Formulas in Excel?

The benefit of this feature is that it allows you to quickly see all of the formulas in a worksheet at once so that you can troubleshoot them or change them.  This eliminates the, quickly annoying, task of having to double-click a cell to see what is in it or to click a cell and look to the formula bar each time.

With this feature, you can quickly scan the worksheet to see what, if anything, needs to be done.

I often use this feature when troubleshooting complex spreadsheets, especially if the spreadsheet was sent to me by another person and I'm not sure exactly what they did to make it.

Don't forget to download the Excel file that accompanies this tutorial so you can try this out.