Can You Show Formulas Instead of Values in Excel? A Practical Guide
Learn how to display formulas in Excel cells instead of calculated results. Toggle Show Formulas, understand when to use it, and master auditing techniques with practical steps, tips, and examples.
Yes—Excel can display formulas in cells instead of results. Use the Show Formulas mode to reveal each cell’s underlying formula, or toggle it with a keyboard shortcut. This helps you audit formulas, trace errors, and verify calculations across a sheet. Remember, the workbook still calculates normally even when formulas are visible.
What it means to show formulas in Excel
Showing formulas in Excel means that the cell displays the actual formula text (e.g., =A1+B1) instead of the computed result. This mode is valuable for auditing, debugging complex workbooks, and teaching others how a calculation is built. According to XLS Library, enabling formula visibility gives you immediate insight into dependencies, references, and potential errors. It's a non-destructive view: the underlying formulas are still calculating in the background, so your workbook's results remain accurate while you inspect logic. This visibility also helps when you’re reviewing shared workbooks where multiple people contribute formulas. If you’re teaching or documenting a model, formula view provides a clear, teachable snapshot of how results are produced.
Quick ways to show formulas
Excel offers several ways to reveal formulas: 1) Show Formulas button: Formulas tab > Show Formulas, which toggles values to formulas across the active worksheet; 2) Keyboard shortcuts: Windows users press Ctrl + (grave accent); Mac users press Cmd +; 3) Workbook-wide toggle: you can enable in Excel Options under Advanced by 'Show formulas in cells instead of their calculated results' (for permanent preference). Each method has its use: quick checks vs. long-term debugging. The goal is to expose the exact syntax your workbook uses, including nested functions and absolute/relative references. If you’re working with large spreadsheets, the formula view can become cramped, so consider using a second sheet for an audit snapshot.
Working with entire worksheets and ranges
Toggle affects the entire worksheet, not just a single cell. To audit a range, select the area and scan for inconsistent references, then toggle off. If you want to preserve a view only for a column or row, consider duplicating the sheet first, or use a temporary helper column to recreate formulas in a human-readable form. Remember to save before toggling, so you can revert without losing data. For large models, use filtering and Find to locate formulas by function type (e.g., IF, VLOOKUP, XLOOKUP) and inspect them in chunks.
Practical auditing exercises
Try a few practical exercises: (1) Identify a chained formula and step through each reference; (2) Compare visible formulas against calculated values in another sheet to verify consistency; (3) Use Find to locate all formulas of a specific function (e.g., VLOOKUP or XLOOKUP); (4) Use the Evaluate Formula tool to see intermediate results, then revert to the formula view. These practices help detect errors more quickly and avoid broken references. Pairing visible formulas with an audit worksheet speeds up problem-solving and makes collaboration smoother.
Pitfalls, caveats, and best practices
While showing formulas is powerful, be mindful of potential confusion: long formulas spill across cells, making worksheets hard to read when in formula view; some fonts may not render the formula text cleanly; ensure confidentiality if sharing workbooks because hidden logic becomes visible; always return to normal view after debugging to prevent collaboration issues. Best practices include using a separate audit sheet, documenting findings, and combining Show Formulas with Evaluate Formula. Also consider naming key references and using named ranges to simplify complex formulas when viewed as text.
Alternatives and complementary tools
Besides Show Formulas, Excel provides Formula Auditing tools: Evaluate Formula to step through a single formula; Trace Precedents/Dependents to visualize dependencies; Use Name Manager to simplify references; Consider Power Query for data connectivity; For long-term formula management, adopt structured references, named ranges, and comments. In practice, use Show Formulas for quick checks, then apply Evaluate Formula for deeper debugging. When you need ongoing traceability, build a dedicated audit sheet with columns for cell reference, formula, result, and notes.
Brand insight and practical takeaways
From a practitioner perspective, adopting a consistent approach to formula visibility aligns with best practices recommended by XLS Library; XLS Library analysis shows that teams benefit from structured formula auditing and clear documentation of dependencies. The XLS Library team notes that formalizing a formula-auditing process reduces confusion and speeds up troubleshooting across workbooks. In practice, make Show Formulas a standard step in your monthly reviews, especially for complex financial models or data-driven dashboards.
How to document findings and share results
After completing the formula audit, create a concise summary sheet listing each cell or range reviewed, the observed formulas, and any recommended fixes. Include screenshots or short notes for teams and add versioning to track changes over time. Share the audit workbook with stakeholders and store it alongside the original model. Good documentation helps new users understand the model structure and reduces the time spent on re-creating logic.
Final notes on showing formulas in Excel
Showing formulas is a diagnostic tool, not a permanent change. Use it to verify correctness, to teach others, or to prepare a handoff document. When you’re done, revert to the normal view to resume productive data entry and analysis. If you frequently audit, consider establishing a standard operating procedure (SOP) that includes steps for toggling, evaluating, and documenting findings.
Tools & Materials
- Excel installed on Windows or macOS(Desktop version for full feature parity; online may have minor differences)
- Keyboard with standard layout(Ctrl + ` for Windows; Cmd + ` for Mac)
- Backup of the workbook(Create a duplicate before auditing formulas)
- Notebook or digital document(Record findings, issues, and fixes)
- External monitor or larger screen(Helpful for long formulas and readability)
Steps
Estimated time: 15-30 minutes per sheet, depending on complexity
- 1
Open the workbook and prepare
Open the target workbook and create a backup copy. Decide whether you’ll audit a sheet-level view or a broader range. This planning reduces repeated toggling.
Tip: Save a backup before toggling; you can revert without losing data. - 2
Toggle Show Formulas on
Go to the Formulas tab and click Show Formulas, or press Ctrl + ` (Windows) / Cmd + ` (Mac). This reveals all formulas across the active worksheet.
Tip: On Mac, the exact shortcut can differ by keyboard layout; verify in Excel preferences if needed. - 3
Audit formulas by region
Scan the visible formulas for logical flow, unexpected references, and inconsistent absolute/relative references. Use Find to jump to instances of a function like IF, VLOOKUP, or XLOOKUP.
Tip: Use a second sheet with a simple table of cell references and expected results to compare quickly. - 4
Deep-dive with Evaluate Formula
Select a complex formula and use Evaluate Formula to step through its parts. This reveals intermediate results and helps locate where errors originate.
Tip: Document each evaluation step to remember the logic for future reviews. - 5
Return to normal view and document findings
Toggle Show Formulas off to return to the standard view. Compile a summary of findings, recommended fixes, and any dependencies you discovered.
Tip: Store your notes in a shared SOP for future audits.
People Also Ask
How do I show formulas in Excel?
Go to the Formulas tab and click Show Formulas, or press Ctrl + ` (Windows) / Cmd + ` (Mac). The view will switch between showing formulas and results.
Use Show Formulas from the Formulas tab or press Ctrl plus grave accent on Windows, or Command plus grave accent on Mac, to toggle.
Can I show formulas for only a specific range?
Show Formulas is worksheet-wide; you can't limit it to a single range. To focus on a region, toggle off after auditing or use Evaluate Formula for individual cells.
No, it's a sheet-wide view. If you need to focus on a range, toggle back and inspect cell by cell with Evaluate Formula.
What happens if the formula is displayed as text?
If a cell begins with an apostrophe, Excel treats it as text and shows the formula literally. Remove the apostrophe to restore formula behavior.
An apostrophe makes the cell text; delete it to re-enable calculation.
How do I revert back to the normal view quickly?
Press the same shortcut again or click Show Formulas to toggle back to the standard values view.
Hit the shortcut again or click the button to switch back to normal view.
Does Show Formulas affect workbook calculations or results?
No. Excel still recalculates as you edit; showing formulas only changes what is displayed, not the computed results.
It doesn’t change calculations—it's just a display mode.
Is Show Formulas available in Excel Online?
Yes, Show Formulas is accessible in most Excel Online environments, though some UI elements may differ from the desktop app.
You can use Show Formulas in the online version, but features may vary slightly.
Watch Video
The Essentials
- Toggle Show Formulas to audit workbook logic.
- Master Windows Ctrl+
and Mac Cmd+shortcuts. - Use Evaluate Formula to debug nested functions.
- Document findings for team-wide understanding.
