Learning how to see formulas in Excel is one of the most underrated skills that separates casual spreadsheet users from confident analysts. By default, Excel hides the underlying logic and only displays the calculated results, which can make auditing, debugging, or learning from someone else's workbook a frustrating experience. Whether you inherited a complex financial model, you are reviewing a colleague's report, or you simply want to verify that a SUM range is correct, knowing how to reveal every formula at once is essential.
This guide walks you through every reliable method to display formulas in Excel, from the famous Ctrl+grave keyboard shortcut to the Show Formulas ribbon button, the FORMULATEXT function, and advanced auditing tools like Trace Precedents and the Watch Window. You will also learn how to print formulas, how to display them in a single cell, and how to protect formulas so coworkers can see them without accidentally editing them.
Excel is far more than basic arithmetic. Power users rely on functions like vlookup excel, INDEX/MATCH, XLOOKUP, and dynamic arrays to build sophisticated dashboards, and being able to reveal those formulas instantly is what makes troubleshooting possible. The same techniques that help you see a simple addition formula also work on nested IF statements that stretch across 400 characters of logic.
We will cover Windows shortcuts, Mac equivalents, Excel for the web behavior, and what to do when Show Formulas refuses to work because cells are formatted as Text. You will see how to widen columns automatically when formulas appear, how to toggle the view per worksheet rather than across the whole workbook, and how to use conditional formatting to highlight every formula cell in color so they jump off the screen.
By the end of this article, you will have a complete toolkit for inspecting, documenting, and learning from any Excel formula you encounter. We will include screenshots-worthy step-by-step instructions, real examples, and answers to the questions accountants, analysts, students, and operations managers ask most often. Each method is explained with the keyboard shortcut, the menu path, and the situation where it shines brightest.
If you are studying for an Excel certification, preparing for a job interview, or simply tired of staring at numbers without understanding where they came from, this is the resource you need. Bookmark this page, share it with your team, and let us turn formula visibility from a mystery into a one-second toggle that you control completely.
Before we dive in, remember that formula visibility is a view setting, not a data change. Toggling Show Formulas on or off does not alter the underlying values, so you can experiment freely without risking your workbook. The shortcut is fully reversible, lightning fast, and works identically in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web with minor variations we will note as we go.
The fastest method on Windows. Press Ctrl + ` (the key above Tab, below Escape) to toggle every formula on the active worksheet. Press again to return to calculated values. Works in every modern Excel version with zero menu hunting required.
Navigate to the Formulas tab, find the Formula Auditing group, and click Show Formulas. The button stays highlighted while active. This is the discoverable, mouse-friendly equivalent to the keyboard shortcut and is ideal for training sessions.
Type =FORMULATEXT(A1) into any empty cell to display the formula stored in A1 as plain text. Perfect when you want to see one formula without changing the entire view, and great for documentation columns next to your live calculations.
Go to File, Options, Advanced, and scroll to Display options for this worksheet. Check Show formulas in cells instead of their calculated results. This persistent setting saves with the workbook and is the only way to make formulas permanently visible.
Select a cell, go to Formulas, Trace Precedents to draw blue arrows showing every cell that feeds into the formula. Combined with the Watch Window, this is the professional auditor's approach to understanding complex models without cluttering the screen.
The Ctrl + grave accent shortcut (Ctrl + `) is the single most important keyboard combination for anyone who works with spreadsheets regularly. The grave accent key sits in the top-left corner of US English keyboards, directly below the Escape key and to the left of the number 1. When you press Ctrl + `, every cell on the active worksheet instantly switches from showing calculated values to showing the underlying formulas, and pressing it again reverses the change. There is no confirmation dialog, no animation, and no risk of overwriting your data.
Behind the scenes, the shortcut toggles the same setting found in File, Options, Advanced, under Display options for this worksheet. Because the setting is per-worksheet rather than per-workbook, you can have Sheet1 displaying formulas while Sheet2 displays values. This is incredibly useful when you want to keep a clean dashboard for stakeholders on one tab while you audit the calculation engine on another. Excel also widens columns automatically when formulas appear so the longer text is readable.
On a Mac, the equivalent shortcut is Control + ` (the same grave accent key), not Command + `. This catches many users off guard because Mac shortcuts usually rely on the Command key. If your keyboard does not have a grave accent, you can use the Excel ribbon path instead: Formulas tab, Formula Auditing group, Show Formulas button. The button has a small fx icon and stays highlighted in a darker shade while the view is active, giving you a clear visual indicator of the current state.
Excel for the web supports the same Ctrl + ` shortcut, but the experience is slightly different because browsers sometimes intercept the keystroke. If the shortcut does nothing in your browser, try clicking inside a cell first, then pressing the combination. Some Chromebook layouts and international keyboards remap the grave key entirely, in which case the ribbon button remains the most reliable fallback. Microsoft 365 desktop versions always honor the shortcut regardless of region.
One frequently overlooked detail is that the shortcut affects only formulas, not constants. Text strings, numbers typed directly, and dates remain unchanged when you toggle the view. Only cells whose content begins with an equals sign will switch display modes. This is great news because it means you can quickly visually scan a worksheet to count how many real formulas exist versus how many cells are hardcoded values, a common source of audit problems in financial models built by multiple authors.
For learners who are still building muscle memory, try this exercise. Open any workbook, press Ctrl + ` and notice which cells changed. Then press it again to confirm the toggle reverses cleanly. Repeat across several worksheets to feel how the per-sheet behavior works. Within a week, this shortcut becomes as automatic as Ctrl + C and Ctrl + V, and you will wonder how you ever debugged a spreadsheet without it. Power users often pair it with Ctrl + [ to jump to precedent cells.
If you frequently switch between value view and formula view, consider adding Show Formulas to your Quick Access Toolbar. Right-click the ribbon button, choose Add to Quick Access Toolbar, and the icon appears at the very top of your Excel window. You can then assign it to Alt + 1, Alt + 2, or another number depending on its toolbar position, giving you a second keyboard shortcut that works even on keyboards without a grave accent key.
Prepare for the Microsoft Excel exam with our free practice test modules. Each quiz covers key topics to help you pass on your first try.
The FORMULATEXT function returns the formula of a referenced cell as a text string. Syntax is simple: =FORMULATEXT(A1) returns the formula contained in cell A1, or a #N/A error if A1 contains a constant. This is perfect for documentation: place FORMULATEXT references in column C next to your live formulas in column B so reviewers can see both the result and the logic side by side without toggling Show Formulas mode.
FORMULATEXT works across worksheets and workbooks, so =FORMULATEXT(Sheet2!D5) is valid. It even respects external references when the source workbook is open. Use it in training materials, audit reports, and reconciliation tabs where transparency matters more than aesthetics. Combine it with conditional formatting to highlight cells whose formulas contain specific text like VLOOKUP or SUMIFS for fast pattern matching during reviews.
Show Formulas mode is the global toggle activated by Ctrl + ` or the ribbon button. It is fastest when you need to scan an entire worksheet, count formulas, or compare neighboring calculations visually. Columns widen automatically and number formatting drops away so long formula strings remain readable. The toggle is purely visual and never affects calculation, so you can use it freely on production workbooks without any risk of damaging data.
The downside of Show Formulas mode is that you lose the calculated values temporarily, which makes it hard to verify results while inspecting logic. If you need both at once, FORMULATEXT in an adjacent column is the better choice. Many analysts toggle Show Formulas, take a screenshot for documentation, then toggle it back off to resume normal work. It is the right tool for big-picture review, not granular debugging.
Trace Precedents draws colored arrows from a selected cell to every cell that feeds into its formula. Activate it from the Formulas tab, Formula Auditing group. Blue arrows point to cells on the same sheet, while dashed black arrows with worksheet icons indicate cells on other sheets. Double-click a dashed arrow to jump directly to the source. This is the gold standard for understanding nested formulas that reference dozens of inputs across multiple tabs.
The companion Trace Dependents tool works in reverse, showing every cell that depends on the selected cell. Use both together to map data flow through a model. Remove Arrows clears the visual overlay when you are done. For very complex models, the Watch Window pinned to the bottom of the screen lets you monitor specific cell values continuously as you scroll, an invaluable trick for financial analysts checking how upstream assumptions ripple through to bottom-line results.
Press F5, click Special, select Formulas, and click OK to highlight every formula cell on the sheet. Now apply a yellow fill color, and you have a permanent visual map of where logic lives versus where hardcoded values sit. This single trick prevents 90 percent of audit errors in financial models because constants accidentally typed over formulas become impossible to miss at a glance.
Sometimes you press Ctrl + ` and absolutely nothing happens. Before assuming Excel is broken, walk through this troubleshooting sequence. The most common culprit is that the cell containing your formula is formatted as Text rather than General or Number. When Excel sees Text formatting, it treats the equals sign as a literal character rather than the start of a formula, so there is no formula to display in the first place. Select the cell, change the format to General via Ctrl + 1, then delete the equals sign and retype it to force recalculation.
Another frequent issue involves keyboard layouts. International keyboards, especially French AZERTY and German QWERTZ, place the grave accent in unexpected positions or require AltGr combinations. If Ctrl + ` does nothing, try Ctrl + Shift + ` or Ctrl + AltGr + the appropriate key. As a universal fallback, click the Show Formulas button on the Formulas ribbon tab, which works regardless of keyboard layout. You can also assign a custom shortcut by adding Show Formulas to the Quick Access Toolbar and using Alt plus a number.
Workbook protection can also interfere. If a worksheet is protected and the original author disabled Select unlocked cells, you may be unable to toggle the view in expected ways. Unprotect the sheet via Review, Unprotect Sheet, provide the password if one is set, then try the shortcut again. Conversely, if you want coworkers to be able to see your formulas but not edit them, protect the sheet with default settings, which leaves formula visibility intact while preventing accidental changes to the underlying logic.
If formulas show as text strings starting with apostrophes, your cells were formatted as Text before the formulas were entered. The apostrophe is invisible but tells Excel to treat the entire content as a literal string. Fix this by selecting the affected range, opening Find and Replace with Ctrl + H, replacing = with = and clicking Replace All. This unusual trick forces Excel to re-evaluate each entry, recognizing the equals sign as the start of a formula and converting the text strings into live calculations.
Calculation mode matters too. If your workbook is set to Manual calculation (Formulas tab, Calculation Options, Manual), formulas exist but values may be stale. Press F9 to force recalculation across the entire workbook, or Shift + F9 for just the active sheet. Show Formulas mode still works in manual calculation, but the displayed values when you toggle back may not match what you expect until you trigger a recalculation. Most users prefer Automatic mode unless they are working with very large models.
Macros and VBA can also override default behavior. A previous developer may have written a Workbook_Open routine that forces Show Formulas off or hides certain ranges. Press Alt + F11 to open the Visual Basic Editor and inspect the ThisWorkbook module for any code containing DisplayFormulas, FormulaHidden, or similar properties. Comment out problematic lines temporarily to test whether the macro is causing the issue, but always save a backup before editing inherited VBA code.
Finally, if you are using Excel for the web through a corporate tenant, your IT administrator may have disabled certain auditing features through policy. The shortcut will still toggle the view in most cases, but Trace Precedents arrows and the Watch Window may be unavailable. Switching to the desktop version usually restores full functionality, or you can request an exception through your IT help desk. Documenting the exact behavior you observe helps administrators diagnose policy conflicts faster.
Printing formulas in Excel requires more planning than printing values because formula strings are often longer than the values they produce. Start by activating Show Formulas mode with Ctrl + `, which automatically widens columns to fit the formula text. Then go to Page Layout, Page Setup, and click the Page tab. Choose Landscape orientation and set scaling to Fit to 1 page wide so long formulas wrap reasonably. Add Page Layout, Print Titles, Rows to repeat at top to maintain headers across multiple printed pages.
Before printing, consider whether you actually need a paper copy or whether a PDF would serve better. File, Export, Create PDF/XPS Document produces a searchable PDF that captures formulas exactly as displayed on screen. This is ideal for audit documentation, knowledge transfer to new team members, or attaching to compliance reports. PDFs preserve column widths and any conditional formatting you applied, which is something that often breaks when you copy and paste formula text into Word or email.
For sharing formula logic with coworkers who do not have Excel open, screenshots are often more practical than full prints. Use the Windows Snipping Tool or macOS Command + Shift + 4 to capture a clean image of the formula bar showing a single formula, or the entire worksheet in Show Formulas mode. Annotate the screenshot with arrows pointing to key inputs, and you have a teaching resource that travels easily through Slack, Teams, or email without requiring the recipient to install Excel.
When collaborating in shared workbooks via Microsoft 365 or OneDrive, Show Formulas mode is a per-user view setting that does not affect what other collaborators see. You can toggle it on your screen while a coworker continues to see calculated values on theirs. This is fantastic for live audit sessions over Teams where the auditor wants to see formulas while the model owner explains the values. The shared workbook itself remains unchanged regardless of which view either person prefers.
For long-term documentation, consider building a dedicated audit worksheet that uses FORMULATEXT extensively. In column A, list cell references like Sheet1!B5, Sheet1!C5, Sheet1!D5. In column B, use =FORMULATEXT() to extract each formula as text. In column C, write plain-English descriptions of what each formula does. This three-column layout becomes a living reference document that updates automatically whenever you change a formula, and it is invaluable when training new analysts or handing off models to successors who need to learn the logic quickly.
If you are interested in deeper auditing, the freeze panes in excel feature pairs beautifully with Show Formulas mode. Freeze the top row and leftmost column so headers stay visible as you scroll through long formula strings. This lets you read every formula in context without losing track of which row and column you are inspecting. The combination of Show Formulas, frozen panes, and the Watch Window is the professional auditor's standard workspace setup for any non-trivial financial model.
Finally, never forget to toggle Show Formulas back off when you are done. Saving a workbook in formula view mode is harmless but disorienting for the next person who opens it. Build the habit of pressing Ctrl + ` twice at the end of an audit session: once to confirm you are in the normal value view, and once more to verify the toggle still works as expected. This two-keystroke ritual catches situations where keyboard remapping or other settings have silently changed your shortcut behavior.
Now that you understand the mechanics of seeing formulas, let us focus on practical tips that turn this knowledge into everyday productivity. First, make Show Formulas part of your standard file-receipt ritual. Whenever a coworker sends you a workbook, press Ctrl + ` immediately to scan the structure. Within ten seconds you will know whether the file is well-built (formulas in consistent patterns) or a disaster (random hardcoded values scattered through what should be calculation columns). This habit alone saves hours of debugging downstream.
Second, train your team to use FORMULATEXT in audit columns rather than relying on screenshots or verbal explanations. When everyone documents formulas the same way, knowledge transfers smoothly between team members, and there is no ambiguity about what a calculation actually does. Pair this with a naming convention for important cells using Excel's Name Manager, and your formulas become almost self-documenting. =Revenue - Costs reads far better than =B5 - B6 in any auditing context.
Third, learn to spot the warning signs that formulas need attention. Long nested IF statements, hardcoded numbers inside formulas, and references to cells in distant parts of the workbook are all red flags. Show Formulas mode makes these patterns leap off the page. Replace deeply nested IFs with IFS or SWITCH, extract magic numbers into named cells with comments, and use Trace Precedents to map dependencies before reorganizing. Each cleanup improves both readability and performance dramatically.
Fourth, use Show Formulas mode as a teaching tool whenever you onboard new analysts. Open a well-built model, toggle the view, and walk through the formula patterns row by row. New hires absorb formula structure ten times faster when they can see the logic alongside the values it produces. Encourage them to take screenshots of particularly elegant formulas and build a personal reference library. Within a few months, they will have a portfolio of patterns to apply in their own work.
Fifth, integrate Show Formulas into your monthly close or reporting cycle. Before publishing any financial report, spend five minutes in formula view scanning for changes. New hardcoded numbers, broken references showing #REF! errors, or formulas that drift outside expected patterns become instantly visible. This five-minute investment has prevented countless embarrassing errors in board packs and investor decks across thousands of finance teams worldwide.
Sixth, do not overlook the ISFORMULA function for automated checking. =ISFORMULA(A1) returns TRUE if A1 contains a formula and FALSE if it contains a constant. Combine this with COUNTIF to count how many formulas exist in a range, or with conditional formatting to highlight every formula cell automatically. This dynamic highlight updates as formulas are added or removed, giving you a real-time view of your model's structure without needing to toggle Show Formulas mode manually.
Finally, remember that knowing how to see formulas in Excel is just the foundation. The real power comes from combining this skill with strong formula writing, clear documentation, and disciplined model design. Practice on real workbooks, take the linked quizzes to test your knowledge, and explore related Excel topics like freeze panes, data validation, and the Analysis ToolPak. Every hour you invest in mastering these tools pays back many times over in faster work, fewer errors, and the confidence that comes from truly understanding every number you produce.