Displaying Excel Formulas

Normally when you enter a formula into Microsoft Excel you want to see the result of the formula, not the formula itself. However, a situation may arise where you need to see the formulas in a spreadsheet and not the answers to the formulas. This need usually occurs when you have to ‘debug’ a problem spreadsheet.

The easiest way to look at a formula is to move the cell pointer to a cell with a formula and look at the formula bar as shown in the next screen shot.


However, this method is only effective if you have a spreadsheet with very few formulas. When you are working with a large spreadsheet it may be difficult to to find each formula. The following spreadsheet, while not large, has a lot of formulas. In fact, only three of the cells displaying numbers are actual data entry cells. Everything else is a formula.


The easiest way to display formulas in Excel is to hold the CTRL key and press `. {The key to the left of the 1 key on the keyboard.} This toggles the display between showing formulas and showing results. Starting with the 2007 version of Excel Microsoft added an icon to show formulas. It is logically placed (surprisingly) on the Formulas tab on the ribbon. This icon is also a toggle: alternating between displaying formualas and results.


Using either method on the demonstration loan comparison spreadsheet displays all of the formulas, including an extra one in cell A10 that was not displayed due to the formatting applied to that cell.


Note: The keyboard technique also works for LibreOffice Calc.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd