Display all Formulas at Once in Excel

Add to Favorites
Author: don

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Print All Worksheets at Once in Excel
Tutorial: How to print all worksheets at once from Excel. This saves you the time of having to go to...
Quickly Resize Multiple Columns or Rows at Once in Excel
Tutorial: How to quickly resize multiple columns and rows at once in Excel.  This avoids having to ...
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
Macro: This Excel macro allows you to filter a data set on multiple columns and criteria at once....
View Different Parts of a Worksheet at Once in Excel
Tutorial: View different sections of the same worksheet when the sections are far apart.  This allo...
Protect and Unprotect All Worksheets at Once
Macro: This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn'...
3D References - Formulas that Reference Multiple Sheets at Once in Excel
Tutorial: Have one simple formula that will reference the same cell or range of cells on multiple w...