Normally when you create a formula in Excel it will return a calculated value in the cell. But you might want to see the formula and not the value it generates. This can be very useful for inspecting your formulas for potential errors. Showing your formulas will also help you to familiarize yourself with a new spreadsheet and what items are calculations and which are static data. When the need to view your formulas arises, how do you show them in the grid instead of the values? Get your copy of the example workbook used in this post and read on to find out!
Show Formulas from the Formula Bar
This method is the most simple and straightforward way to view your formulas. When you select any cell, it will show any formula in the formula bar. This is also the most limited method as it will only allow you to view one formula at a time.
Show Formulas with Edit Mode
This method will still only allow you to view one formula at a time, but with the added functionality that it will show you any formula precedents. Select the cell with the formula which you want to view and then press the F2 key to go into edit mode. You can also double-click on a cell to enter edit mode. This will show the formula in the grid and dependent ranges will be highlighted on the screen. Press Enter or Esc to exit edit mode and return to the normal mode.
Show Formulas from the Formula Tab
Excel has a feature that will allow you to see all the formulas in your sheet at once. This can be found in the Formula tab. Follow these steps to show all your formulas on a sheet. When you select this option all the formulas in the current sheet will be shown instead of their calculated values. As you select a cell it will also show the formula precedents in the current sheet similar to the edit mode. š Note: Show Formulas is a sheet-level setting so you will need to turn it on for each sheet you want to show formulas on. When this feature is enabled, you will see the formulas in the grid and the Show Formulas button in the Formulas tab will be highlighted. Click on the Show Formulas button to turn off the formula view and return to the normal calculated value view.
Show Formulas with a Keyboard Shortcut
There is a very convenient keyboard shortcut to show and hide the Show Formulas view.
Press Ctrl + on your keyboard to view the formulas in your sheet.
š Note: The grave accent key can usually be found above the Tab key on most keyboards.
Press Ctrl + ` again to hide the formulas in your sheet.
This is a very easy way to quickly show and hide the formula view in any sheet.
Show Formulas from the Excel Options
The Show Formulas option can also be enabled from the Excel Options menu. Open the Excel Options menu and follow these steps. This is the exact same as showing the formulas through the Formulas tab or with the keyboard shortcut but itās much less convenient.
Show Formulas with the FORMULATEXT Function
All the previous methods are only able to show either one or all the formulas at the same time. But you might want to show only a selected range of formulas while showing the calculated values for all others. This is possible and you can use the FORMULATEXT function to accomplish this. This function will convert the formula in a cell into a text value. The above formula will convert the formula in cell E3 into text and it will be delayed in the cell as text. This is a great way to add documentation to your spreadsheets as you can show the users the exact formula that is being used without much effort. It is also a dynamic solution since the text will update if you change the formula in the reference cell!
Show Formulas with Text Format
You can also show formulas instead of a calculated value by formatting the cell as text. This is often a frustration for users. When a formula doesnāt calculate and only shows the formula text, itās likely a text formatted cell thatās causing this. Follow these steps to format your cells as text. This will convert the calculated results of any existing formula into text values. In this example, you can see the results are left aligned indicating they are text values instead of the previous currency values. But they still are showing the calculated values and not the formula text. Any new formulas you enter into the text formatted cells will not calculate and will instead show the formula text in the cell. You can also re-enter the current formulas by pressing F2 and then Enter while on the cell. This causes the cell to display the formula text. The text formatting will force Excel to interpret anything you enter in the cell as text. Even a formula starting with an equal sign = is interpreted as text.
Show Formulas with Find and Replace
Another way to force Excel to interpret your formula as text is with the apostrophe character ’ at the start. You can manually add the apostrophe character before the equal sign ‘= and the formula will show as text on the sheet. If you want to use this trick on multiple cells, then you can use the find and replace feature to find any equal signs = and replace them with ‘=. This will add a leading apostrophe to all the formulas and cause them to display as text. The Apostrophe is only visible in the formula bar when the cell is selected and not visible in the grid. ā ļø Warning: Removing these leading apostrophes canāt be done with the Find and Replace menu. If you try to find ‘= and replace it with =, Excel will warn you it wasnāt able to find anything to replace. An alternative option to the apostrophe is to use any character that isnāt used in your formulas. For example, the pipe character | isnāt frequently used and will result in an easier removal to convert the cell back into a formula. You can find = and replace it with |=. The only drawback to using another character like the pipe character | is it will show up in the grid. But you will be able to convert these into calculating formulas much easier by finding |= and replacing it with =.
Show Formulas with VBA
The Show Formulas command in the Home tab is a very convenient way to display all the formulas in a sheet. But what if you want to show the formulas in all the sheets? You would have to turn this feature on across all your sheets. In large workbooks, this would be a tedious task. Thankfully, you can use a VBA macro to turn on or off the Show Formulas option across all your sheets. Go to the Developer tab and select the Visual Basic option to open the VBA code editor. You can also open the editor with the Alt + F11 keyboard shortcut. Go to the Insert tab in the visual basic code editor and select Module from the options. This will create a new module where you will be able add your VBA code. Add the above code to your new module. This will loop through all the sheets in your workbook and toggle the Show Formula setting on or off depending on the status of the active sheet when you run the code. You can then run this VBA macro to quickly turn on or off the Show Formulas feature across all the sheets.
Conclusions
Viewing the formulas in your Excel files is an essential activity when troubleshooting your calculations or familiarizing yourself with someone elseās work. The formula bar or the cell edit mode are great ways to view one formula at a time, but other methods are needed if you want to show more than one formula in your sheet. This is when you will need the Show Formula feature and it can be accessed from the Home tab, keyboard shortcut, or the Excel Options menu. There are also options such as text formatting, the FORMULATEXT function, or using the Find and Replace menu to convert your formulas to text. These allow you to show formulas in a selected range while all other formulas in your sheet show the calculated values. VBA can also be used to toggle on or off the Show Formulas feature across all your sheets. This can save you a lot of clicks in larger workbooks. Have you ever used any of these methods for showing your formulas in the grid? Let me know in the comments below!
