To display the formulas in a worksheet instead of their results, press Ctrl + ` (grave accent key, usually above Tab) on Windows or Control + ` on Mac. This toggles between showing all formulas and showing their calculated values. You can also use the ribbon path Formulas > Formula Auditing > Show Formulas.
Why You Would Want to See All Formulas at Once
Seeing every formula in a sheet is useful for auditing, teaching, or debugging. Instead of clicking each cell and reading the formula bar, the entire grid switches to formula view. This makes it easy to spot inconsistent references, missing operators, or hardcoded numbers that should be formulas. It also helps when you need to print a hard copy of the logic behind a model—formula view prints exactly what you see on screen.
The toggle is a worksheet-level view setting, not a document property. It affects only the active sheet. If you want to see formulas on multiple sheets, you must toggle each one individually, or select multiple sheets (Ctrl+click sheet tabs) before toggling.
Method 1: Keyboard Shortcut (Fastest)
The quickest way is the keyboard shortcut:
- Windows:
Ctrl + `(grave accent, left of the 1 key). - Mac:
Control + `(same key).
Press the same combination again to switch back to normal view. This shortcut works in Excel for Microsoft 365, Excel 2021, 2019, 2016, and earlier versions. It also works in Excel for the web, but the grave accent key may be harder to find on some laptop keyboards—if it doesn’t work, use the ribbon method below.
Method 2: Ribbon Command (Easy to Remember)
If you prefer menus:
- Go to the Formulas tab.
- In the Formula Auditing group, click Show Formulas.
This button toggles the same view. When active, it appears highlighted (shaded) on the ribbon. Click it again to turn it off. This path is identical in Excel for Microsoft 365 on Windows and Mac. In Excel for the web, the button is under Formulas > Show Formulas as well.
Method 3: Excel Options (Default for New Sheets)
If you want formulas displayed by default in every new workbook, change a global setting:
- Go to File > Options (Windows) or Excel > Preferences > Calculation (Mac).
- In the Advanced category (Windows) or Calculation pane (Mac), scroll to Display options for this worksheet.
- Check the box Show formulas in cells instead of their calculated results.
Note: this setting applies to the current worksheet only, not all sheets. To apply it to multiple sheets, select the sheet tabs you want before changing the option. This is a per-sheet setting, not a workbook-wide one.
What Formula View Looks Like and How It Affects Printing
When you enable formula view, several things change:
- Every cell that contains a formula shows the formula text (e.g.,
=A1+B1) instead of the result. - Cells that contain constants (numbers, text) remain unchanged.
- Column widths may appear wider because formulas are longer than results.
- Cells with formulas lose their formatting preview—you only see the formula text, not the formatted number.
When printing, the formula view is what gets printed. If you want a clean printout of formulas, set the print area to the range you need and adjust column widths manually. To print formulas with gridlines, go to Page Layout > Sheet Options and check Print under Gridlines.
Common Problems and Fixes
Problem: The shortcut Ctrl + ` does nothing.
Fix: On some laptops, the grave accent key is shared with the tilde (~) and requires pressing Shift. Try Ctrl + Shift + ` or use the ribbon button. Also, check that you are not in cell edit mode (press Esc first).
Problem: Formulas show but the sheet looks messy.
Fix: That’s normal. Formula view is for auditing, not for presentation. Use the toggle to switch back after reviewing.
Problem: Only some formulas show, others show numbers.
Fix: The toggle applies to the entire sheet. If some cells show numbers, they likely contain constants, not formulas. To confirm, click a cell and look at the formula bar—if it shows a value, it’s a constant.
Problem: I want to see formulas in multiple sheets at once.
Fix: Select the sheet tabs (Ctrl+click each) before toggling. Then the view applies to all selected sheets.
Alternatives: Trace Precedents and Evaluate Formula
If you only need to see how one formula is built, you don’t need to toggle the whole sheet. Use these tools:
- Trace Precedents: On the Formulas tab, click Trace Precedents to draw arrows from the formula to its referenced cells. Click Remove Arrows to clear.
- Evaluate Formula: Select a cell with a formula, then click Evaluate Formula in the Formula Auditing group. This steps through the calculation one part at a time, showing intermediate results.
- Formula bar: Click any cell and read the formula in the formula bar above the grid. Press
F2to edit it directly.
These methods are better for debugging a single complex formula because they don’t change the whole sheet’s view.
Frequently Asked Questions
Does the Show Formulas shortcut work in Google Sheets?
No. Google Sheets does not have a direct keyboard shortcut to toggle all formulas. Instead, use the menu View > Show > Formulas (or View > Show formulas in some versions). In Excel for the web, the shortcut works if your browser supports it, but the ribbon button is more reliable.
Why do my formulas show as text with an apostrophe?
If a formula appears as text like '=A1+B1, it means the cell is formatted as text or you typed an apostrophe before the equals sign. Remove the apostrophe and ensure the cell format is General or Number, then re-enter the formula.
Can I print only the formulas without the results?
Yes. Enable formula view (Ctrl+`) and then print. The printed output will show formulas instead of results. To make it readable, adjust column widths and set the print area to the relevant range.
Will the Show Formulas setting affect other worksheets in the same workbook?
No. It only affects the active worksheet. If you want it on multiple sheets, select their tabs before toggling.
Is there a way to see formulas in a cell without toggling the whole sheet?
Yes. Click the cell and look at the formula bar, or double-click the cell (or press F2) to see the formula in place. For a quick pop-up, you can also press Ctrl+Shift+U to expand the formula bar.
Related
- XLSX or XLS Viewer: Which File Format Should You Use?
- Excel Wrap Text in Quotes? How to Add Quotation Marks
- Excel Weekly Schedule Template: How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for NPV: How to Use ItThe Excel formula for net present value (NPV) is =NPV(rate, value1, [value2], ...). For example, if your discount…
- Excel Transpose Bottom to Top: Formulas and Fixes GuideTo transpose a vertical list from bottom to top into a horizontal row in Excel for Microsoft 365,…
- How to Format Proper Names in Excel with PROPER FunctionTo capitalize names in Excel, put this formula in a blank helper column beside the first name: =PROPER(A2).…
- Excel Formula for Last 4 Digits: RIGHT and FixesTo return the last four characters from a value in cell A2, use =RIGHT(A2,4). For example, if A2…
- Using IF in Excel for Dates: Rules, Formulas, and ExamplesYes. In Excel, use IF to test a date against another date, a fixed cutoff, or a date…
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.