You may need to see every formula in your Excel workbook to audit calculations or find errors. Excel has a dedicated view mode that displays formulas in cells instead of their results. This article explains how to activate this view using the keyboard shortcut and menu options.
Key Takeaways: Show All Formulas in Excel
- Ctrl+` (Backtick): Toggles the formula view on and off for the entire active worksheet instantly.
- Formulas > Show Formulas: Activates the same view from the ribbon, useful if you forget the shortcut.
- File > Options > Advanced > Display options for this worksheet: Controls formula display for a single sheet without affecting others.
Understanding Excel’s Formula View Mode
Normally, an Excel cell shows the calculated result of a formula. The formula view mode changes this display. Instead of showing the result, each cell shows the exact formula text as it was entered. This view applies to the entire active worksheet. All columns automatically widen to make longer formulas visible. No data is changed or calculated differently; only the display alters. This mode is essential for checking logic, debugging errors, and documenting spreadsheet structure.
Steps to Display All Formulas in a Worksheet
You can enable the formula view using a quick keyboard shortcut or through the Excel ribbon. Both methods achieve the same result.
Method 1: Use the Ctrl+` Keyboard Shortcut
- Open your Excel workbook
Navigate to the specific worksheet where you want to see the formulas. - Press Ctrl+` (Backtick)
Hold the Ctrl key and press the backtick key. This key is usually located on the top-left of your keyboard, above the Tab key and to the left of the number 1 key. - Review the worksheet
All cells containing formulas will now display the formula text. Column widths will adjust. Press Ctrl+` again to return to the normal results view.
Method 2: Use the Ribbon Command
- Go to the Formulas tab
Click the Formulas tab on the Excel ribbon at the top of the window. - Click Show Formulas
In the Formula Auditing group, click the Show Formulas button. The button acts as a toggle. Click it once to show formulas, click it again to hide them.
Method 3: Configure Display Options for One Sheet
- Open Excel Options
Click File > Options to open the Excel Options dialog box. - Navigate to Advanced settings
In the left pane, select the Advanced category. - Find the display settings
Scroll down to the Display options for this worksheet section. - Enable formula display
Use the drop-down menu to select the specific worksheet. Then, check the box for Show formulas in cells instead of their calculated results. Click OK to apply.
Common Mistakes and Limitations
When working with the formula view, users often encounter a few specific issues.
Pressing the Wrong Key for Ctrl+`
The backtick key (`) is not the single quote key (‘). On most US keyboards, the backtick is on the same key as the tilde (~). If Ctrl+` does not work, check your keyboard layout. You can always use the ribbon button as an alternative.
Formulas View Slows Down Scrolling
In very large workbooks, showing all formulas can make scrolling less smooth. This happens because Excel must render the formula text for every cell instead of simple values. For better performance, apply the view only to the specific sheet you are auditing.
Printing While Formulas Are Visible
If you print a worksheet while formula view is active, the printout will show the formulas, not the results. Always toggle back to normal view with Ctrl+` before printing if you need the calculated results on the page.
Keyboard Shortcut vs Ribbon Button: Key Differences
| Item | Ctrl+` Shortcut | Formulas > Show Formulas Button |
|---|---|---|
| Speed | Instant one-key toggle | Requires mouse navigation |
| Accessibility | Requires knowing key location | Always visible on the ribbon |
| Scope of control | Toggles view for active sheet only | Toggles view for active sheet only |
| Persistence | Setting is saved with the workbook | Setting is saved with the workbook |
You can now quickly audit any worksheet by displaying all its formulas. Use the Ctrl+` shortcut for the fastest toggle between formulas and results. For a more permanent setting, use the advanced options to enable formulas for a single sheet. Try using the Watch Window under Formulas > Formula Auditing to monitor key formulas while you edit other parts of your sheet.