How to Display Formulas Instead of Values in Excel Cells
Learn practical steps to display formulas instead of values in Excel, including keyboard shortcuts, auditing methods, and best practices for clear documentation across worksheets.
According to XLS Library, you can display formulas in cells by turning on Show Formulas. This reveals each cell's formula rather than its calculated result. Use the keyboard shortcut Ctrl+` (grave accent) or go to Formulas > Show Formulas, and ensure workbook calculation is set to automatic. These methods let you audit formulas quickly across worksheets.
Why you might want to display formulas
In many Excel workflows, understanding the underlying structure of a worksheet is as important as seeing the results. Displaying formulas replaces numeric results with the actual text formula in each cell, making it easier to audit, troubleshoot, and document complex models. According to XLS Library, the deliberate practice of formula auditing helps teams identify logic errors, broken references, and inconsistent pattern usage early in a project. When you display formulas, you can immediately see nested IFs, LOOKUPs, or array formulas that drive decisions. This visibility also aids training new team members because it exposes them to real formulas rather than just outcomes. Note that this view is display-only; it does not alter the calculated values when you switch back. For ongoing projects, you may want to pair Show Formulas with a separate documentation sheet that lists each formula and its purpose. This approach aligns with best practices for reproducible analytics and governance. By adopting this habit, Excel users at all levels can raise the quality and reliability of their models.
Throughout this process, keep in mind that this feature primarily serves auditing and learning purposes. It helps verify that formulas reference the correct cells and use the intended functions. Excel’s built-in auditing tools—such as Trace Precedents and Evaluate Formula—complement the Show Formulas view, offering a deeper look at how data flows through a model. The combination of display and auditing tools is a powerful approach to maintaining accuracy in financial models, budgets, or operational dashboards.
wordCountApproximation
Tools & Materials
- Microsoft Excel (desktop or web)(Any modern version supports Show Formulas; features may vary slightly between Windows, macOS, and online.)
- Backup copy of workbook(Save a separate copy before toggling formulas to protect the original data.)
- Keyboard and mouse(Ctrl+` toggles Show Formulas; use arrow keys and Go To for navigation.)
- Formulas tab (ribbon access)(Alternative path if you prefer the ribbon; handy for occasional use.)
- Notes/documentation tool(Optional sheet or external document to record observed formulas and changes.)
Steps
Estimated time: 15-30 minutes
- 1
Create a backup copy
Open the workbook and save a duplicate in a secure location to protect original data before toggling views.
Tip: Label the copy clearly as a 'Formulas View Audit'. - 2
Toggle Show Formulas using keyboard
Press Ctrl+` to switch the worksheet display to formulas. If nothing changes, ensure you're on a worksheet with at least one formula.
Tip: If your keyboard layout differs, check the exact key mapping in Excel Help. - 3
Audit a sample of cells
Navigate to cells that contain formulas and verify that the displayed text matches the underlying formula.
Tip: Use Find (Ctrl+F) to locate cells with formulas quickly. - 4
Use the Formulas tab for broader scope
On the Formulas tab, click Show Formulas for the entire workbook or per sheet.
Tip: If you need per-sheet accuracy, toggle on each sheet separately. - 5
Use auditing tools for complex workbooks
Utilize Trace Precedents, Evaluate Formula, and Formula Auditing to understand dependencies.
Tip: Document any unexpected references and their impact on results. - 6
Document your findings
Create a simple notes sheet or external document listing where formulas were found and any anomalies.
Tip: Attach links or cell references to each observation for traceability. - 7
Return to normal view
Once auditing is complete, press Ctrl+` again to restore the standard value view.
Tip: Save your audit notes before reverting to avoid losing context.
People Also Ask
How do I display formulas across all sheets in a workbook?
Excel does not have a single one-click global toggle for all sheets. You must apply Show Formulas to each sheet, or use a short macro to loop through sheets.
There isn't a global one-click option; you may apply Show Formulas sheet by sheet or automate with a macro.
Will Show Formulas affect your worksheet calculations?
No. Show Formulas only changes the display; all calculations continue to occur as usual.
No, it only changes what you see; calculations still happen as normal.
How can I display a specific range of formulas?
Select the range, then press Ctrl+` to toggle. For multiple areas, repeat on each range or use a macro.
Select the range and toggle Show Formulas; repeat or automate for multiple areas.
Can I print a sheet with formulas visible?
Yes, enable Show Formulas before printing; the printed output will show formulas instead of results. You may need to adjust column widths.
Yes—turn on Show Formulas before printing so formulas appear in print.
What should I do to protect sensitive formulas?
Be cautious: Show Formulas reveals formulas and references. Use workbook protection or restrict sharing when needed.
Be careful: formulas and references are exposed; protect sheets as needed.
What alternatives help inspect formulas without showing them?
Use the Evaluate Formula tool and Formula Auditing features to understand formulas without revealing their text.
Use Evaluate Formula and auditing tools to inspect without displaying the text.
Watch Video
The Essentials
- Enable Show Formulas to audit cells quickly.
- Use keyboard shortcuts for efficiency.
- Document findings to prevent future confusion.
- Combine display with auditing tools for deep insight.
