Learning how to display formulas in Excel is one of the most useful skills for auditing spreadsheets, training new analysts, and troubleshooting calculation errors that hide behind result values. By default, Excel shows the calculated output of each formula in a cell, which is what end users want to see, but spreadsheet authors, teachers, and reviewers often need to flip the view so every cell reveals its underlying formula text instead.

This guide walks through every reliable method, including the Show Formulas toggle on the Formulas ribbon, the Ctrl plus grave accent keyboard shortcut, the FORMULATEXT function, custom cell formatting, and VBA tricks for advanced users.

The need to display formulas in Excel comes up constantly in real work. Maybe you inherited a budget workbook with 4,000 cells and need to verify which references are hard-coded numbers versus live calculations. Maybe you are writing documentation for a finance team and need a printable copy of every formula on a sheet. Maybe a cell shows a #VALUE error and you want to inspect the formula alongside neighboring cells without clicking each one. In each case, the standard worksheet view hides the very information you need most.

Excel offers two fundamentally different display modes. The first is the standard Values view, where formulas are evaluated and only their results appear in cells. The second is Formula view, sometimes called Formula Audit Mode, where every cell that contains a formula displays the formula text itself and columns automatically widen to accommodate the longer strings. Toggling between these modes is instant and reversible, and it affects only the on-screen and printed view, not the stored values or any downstream calculations.

Beyond the global toggle, Excel provides surgical ways to expose individual formulas. The FORMULATEXT function pulls the formula from a referenced cell and displays it as a string in another cell, which is perfect for building self-documenting worksheets where users can see both the answer and the calculation side by side. Custom number formats and apostrophe prefixes can convert a single formula into displayed text without affecting the rest of the sheet. Power users add Watch Windows and the Evaluate Formula dialog to step through complex nested calculations one operation at a time.

This article also touches on closely related skills that often come up in the same workflow. Excel users who need to audit formulas frequently also want to know how to merge cells in Excel for cleaner report layouts, how to freeze a row in Excel so headers stay visible while scrolling through long formula lists, and how to remove duplicates Excel offers when consolidating audit results. The vlookup excel function appears in many of the formulas you will be displaying, so understanding how lookup references render in Formula view saves confusion later.

By the end of this guide you will know exactly when to use each display method, how to print a worksheet with formulas visible instead of values, how to protect formulas from being seen in shared workbooks, and how to use formula auditing tools like Trace Precedents and Trace Dependents to map calculation chains visually. Whether you are debugging a single broken cell or documenting an entire financial model, these techniques turn Excel from a black box into a transparent calculation engine you can read at a glance.

The methods covered work in every modern version of Excel, including Excel 2016, 2019, 2021, Microsoft 365, and Excel for the Web, with minor differences noted where the interface diverges. Keyboard shortcuts are given for both Windows and Mac, since the grave accent key sits in slightly different positions on different keyboards. By the time you finish reading, displaying formulas will feel as natural as typing them in the first place, and your spreadsheet audits will move several times faster than they do today.

The fastest way to display formulas in Excel for an entire worksheet is the Ctrl plus grave accent keyboard shortcut. The grave accent key, sometimes called the backtick, sits just below the Escape key in the top-left corner of most US English keyboards, sharing space with the tilde character. Press Ctrl and that key together and every formula on the active sheet appears in place of its result. Press the same combination again and the values return. The shortcut affects only the active worksheet, so other sheets in the same workbook remain in normal Values view.

If you prefer clicking, the ribbon route is equally effective. Open the Formulas tab on the main ribbon and look for the Formula Auditing group near the middle. The Show Formulas button has an icon resembling a magnifying glass over the fx symbol. Clicking it toggles Formula view on, and the button highlights to show the state is active. Clicking again restores Values view. This button is especially handy when you are already working in the Formulas tab for other auditing tasks like Trace Precedents or Evaluate Formula.

When Formula view turns on, several things happen at once. Columns containing formulas widen automatically to fit the longest formula string in each column, which can make the layout look quite different from the original. Numbers stored as values stay unchanged because they are not formulas. Dates that were formatted to display as friendly strings revert to their underlying serial numbers in some cases. Conditional formatting still applies, but the visual emphasis may shift because cell widths have changed. None of these display changes affect the stored data.

For displaying just one formula without disturbing the whole sheet, the FORMULATEXT function is the cleanest tool. Its syntax is simple: =FORMULATEXT(reference) where reference points to the cell whose formula you want to see. If A1 contains =SUM(B1:B10), then typing =FORMULATEXT(A1) in cell C1 shows the literal text =SUM(B1:B10) in C1 while A1 continues to display its calculated sum. This is invaluable for training materials, side-by-side comparisons, and self-documenting spreadsheets where users need to understand the math without flipping modes.

FORMULATEXT returns the #N/A error when the referenced cell does not contain a formula, such as when it holds a typed number or text string. It also returns errors for cells in closed workbooks, cells on protected sheets where the formula is hidden, and cells outside the workbook. Wrap the function in IFERROR to handle these gracefully: =IFERROR(FORMULATEXT(A1),"No formula") returns a friendly message instead of an error code when A1 happens to hold a static value rather than a calculation.

Beyond these primary methods, Excel offers the File Options route for users who want the formula display setting to persist with the workbook. Go to File, then Options, then Advanced. Scroll down to the section titled Display options for this worksheet, and check the box for Show formulas in cells instead of their calculated results.

Click OK and Formula view turns on. Because this setting is stored at the worksheet level inside the workbook file, it travels with the file when you share it. Recipients see formulas immediately when they open the workbook, which is sometimes exactly what you want and sometimes very confusing.

A more surgical option is the apostrophe prefix. Typing a single apostrophe before the equals sign tells Excel to treat what follows as plain text rather than as a formula. So entering '=SUM(A1:A10) in a cell displays the literal characters =SUM(A1:A10) with no apostrophe visible and no calculation performed. This is the easiest way to show a sample formula in a documentation cell, training handout, or instructional comment. To convert the text back to a working formula later, delete the apostrophe and Excel evaluates the expression normally.

The FORMULATEXT function deserves a deeper look because it is the most flexible way to display formulas in Excel without changing the global view. Unlike the Show Formulas toggle, FORMULATEXT lets you reveal individual formulas in dedicated cells while the rest of the sheet continues to show calculated results normally. This makes it ideal for building self-documenting workbooks where every key calculation has a neighboring cell explaining what it does. Auditors love FORMULATEXT because it pulls the exact formula syntax as entered, including any errors or odd references that might be hidden by the result.

A typical FORMULATEXT pattern looks like this. Column B holds calculations such as =SUM(Sales[Q1]) and =VLOOKUP(A2,PriceList,3,0). In column C, enter =FORMULATEXT(B2), =FORMULATEXT(B3), and so on. Column C now reads like a code listing alongside column B's results, giving anyone reviewing the workbook a complete picture without having to click each cell to inspect the formula bar. Combine FORMULATEXT with IFERROR to display a friendly message when the referenced cell contains a static value: =IFERROR(FORMULATEXT(B2),"Static value") shows the formula or, when there is none, a clear note.

Excel's Formula Auditing toolbox extends well beyond Show Formulas. The Trace Precedents button draws blue arrows from the selected cell to every cell that feeds it. Trace Dependents draws arrows in the opposite direction, showing every cell that consumes the selected cell's value. These visual arrows are invaluable for understanding how a single change ripples through a financial model. The Remove Arrows button clears the visualization when you are done. Watch Window adds another layer by letting you monitor specific cells across multiple sheets in a floating panel as the workbook recalculates.

The Evaluate Formula dialog is the deepest auditing tool Excel offers. Select a cell with a complex nested formula, click Evaluate Formula on the Formulas tab, and Excel opens a window showing the formula text with one operation underlined. Click Evaluate and Excel replaces that operation with its result. Click again and the next operation evaluates. Step by step you watch Excel compute the formula from the innermost function outward, which makes debugging deeply nested IFs, INDEX-MATCH combinations, and complicated lookups dramatically easier than guessing at why a formula returns the wrong answer.

For formulas you want to keep secret from end users, Excel offers the Hidden attribute. Right-click the relevant cells, choose Format Cells, go to the Protection tab, and check both Locked and Hidden. Then protect the sheet via the Review tab. Once protection is active, the formula bar shows nothing when the locked and hidden cells are selected, even though calculations still run normally. This is useful for proprietary models, scoring formulas, and any case where the calculation logic is sensitive but the results need to be visible.

Custom number formats can also force a cell to display its own formula. Although the trick is rarely needed in modern Excel because FORMULATEXT exists, it remains popular in some specialized scenarios. The apostrophe prefix discussed earlier is the simplest form, but advanced users can also apply the text format category and then re-enter the formula as a string. For workflows that need to remove duplicates excel users typically rely on the dedicated Remove Duplicates button on the Data tab, which becomes much easier to use after displaying all the source formulas to verify which columns drive the dedupe logic.

Finally, Excel exposes a Name Manager that shows every named range and named formula in the workbook. Open it via the Formulas tab and Define Name group. The Name Manager dialog lists each name alongside its current value and the formula or reference it points to. This is essentially a workbook-wide formula display for named items, and it is the only place to audit named formulas because they do not appear in cells the way standard formulas do. Use Name Manager whenever you inherit a workbook with mysterious names you need to understand.

Printing a worksheet with formulas displayed is one of the most common reasons people learn to display formulas in Excel. To print formulas instead of values, first toggle Formula view on with Ctrl plus grave accent or the Show Formulas ribbon button. Then open Page Layout to adjust scaling, orientation, and print area. Long formulas often demand landscape orientation and Fit to One Page Wide scaling to keep them readable. Preview the output through File, Print and check that no formulas truncate at column edges before sending the job to the printer or saving as PDF.

When sharing workbooks that contain sensitive formulas, you have two paths. Either send the file in Formula view so recipients see every calculation up front, or hide the formulas using worksheet protection so recipients see only values. Most professional workflows favor the second approach because it keeps the workbook usable while protecting proprietary logic. Combine the Hidden cell attribute with sheet protection from the Review tab, and remember to set a password if the protection genuinely needs to resist tampering rather than just preventing accidental edits.

Excel for the Web supports Show Formulas through the same Ctrl plus grave accent shortcut and through the Formulas ribbon, though some advanced features like Evaluate Formula are not available in the browser version. FORMULATEXT works the same way in the web app. If your team collaborates on workbooks in OneDrive or SharePoint, formulas display consistently across desktop and web, which makes co-authoring audits much smoother than it used to be. Just be aware that named formulas and certain dynamic array behaviors render with subtle differences in the web client.

Macros and VBA give you programmatic control over formula display. The line ActiveWindow.DisplayFormulas = True toggles Formula view on for the active window, and setting it to False returns to Values view. You can wrap this in a custom button or shortcut for one-click toggling during repetitive audit workflows.

For automated formula extraction, the Range.Formula and Range.FormulaR1C1 properties return the formula text for any cell, which you can then write to another sheet or export to a documentation file. These techniques pair naturally with how to freeze a row in excel adjustments so headers stay anchored while you scroll through long formula listings.

One sometimes-overlooked detail is that Formula view treats array formulas, dynamic arrays, and legacy CSE array formulas slightly differently. Modern dynamic arrays show the formula only in the anchor cell, with spill cells displaying nothing in Formula view because they do not contain their own formulas. Legacy CSE array formulas wrap in curly braces when displayed, signaling that they were entered with Ctrl plus Shift plus Enter rather than just Enter. Understanding these visual cues helps you reverse-engineer inherited workbooks that mix old and new array conventions in ways that can confuse even experienced analysts.

Excel tables, also known as ListObjects, display formulas using structured references such as =[@Quantity]*[@Price] rather than traditional A1-style references. When you toggle Formula view on, every table column formula shows in this structured form, making it easy to confirm that the table is properly normalized with one consistent formula per column. If different rows within a column show different formulas, the table has been edited inconsistently and should be cleaned up. This is one of the fastest audit wins available from a five-second formula display toggle.

Lastly, version-control teams who track Excel files in git or similar systems benefit enormously from Formula view exports. By saving a copy of the workbook with Formula view active and converting it to PDF or printing it to a flat image, you create a human-readable record of the calculation logic at a specific point in time. Pair this with a Values view snapshot and your audit trail captures both what the spreadsheet was computing and what answers it produced, satisfying the requirements of most financial controls frameworks without expensive add-ins.

Practical tips can turn formula display from a one-off trick into a daily productivity tool. Map the Ctrl plus grave accent shortcut to muscle memory by using it the first time you open any new workbook you need to understand. The five-second scan reveals more about a spreadsheet's structure than any amount of clicking through individual cells. You will quickly spot inconsistent formulas, surprising hard-coded numbers, and broken references that lie dormant in Values view. This habit alone catches roughly half of all spreadsheet errors before they cause real problems downstream.

When teaching Excel to others, build training workbooks with FORMULATEXT in a documentation column. Place the live calculation in column B and =FORMULATEXT(B2) in column C with the header Formula. Learners see both the answer and the underlying syntax simultaneously, which reinforces understanding far better than slides or screenshots. This technique also works beautifully for cheat sheets you print and post near workstations. A single sheet of paper showing twenty common formulas next to their results becomes a permanent reference your team uses for months.

For audit engagements with strict documentation requirements, save two versions of every reviewed workbook. Save the original in Values view as Filename_Values.xlsx and a duplicate in Formula view as Filename_Formulas.xlsx. Auditors can then verify both the calculation logic and the result set independently, and the file pair becomes the formal record of what was tested. This dual-save approach satisfies SOX, internal audit, and external auditor expectations without expensive specialized tools and works equally well for monthly close reviews and annual financial statement audits.

If you work with shared dashboards, consider using how to create a drop down list in excel techniques to let users toggle which view they see. A drop-down menu in a control cell can drive an IF formula that switches between displaying calculated values and FORMULATEXT versions of the same cells. While this requires more setup than a simple Ctrl plus grave accent press, it gives non-technical viewers a self-service way to see the underlying logic without you having to walk them through the ribbon, which is especially valuable in remote-work and asynchronous review environments.

Pay attention to how Formula view interacts with conditional formatting. Some conditional formats reference cell values directly, and when those cells display formulas instead of values, the formatting rules may still apply based on the underlying values rather than the displayed text. This means a cell showing =SUM(A1:A10) in Formula view might still be highlighted red because its underlying value exceeds a threshold. Test your conditional formatting rules in both views during workbook design to confirm the visual feedback you intended actually appears in the situations you expect.

Finally, develop a personal shortcut habit for the most common formula display workflow. Many power users assign a custom Quick Access Toolbar button to Show Formulas with Alt plus a single digit such as Alt plus 4, which is even faster than Ctrl plus grave accent and works on any keyboard layout.

Right-click the Show Formulas button on the Formulas ribbon and choose Add to Quick Access Toolbar. Excel assigns the next available digit shortcut automatically, and your formula audits speed up by another second or two per toggle, which adds up to real time savings over a year of regular use.

Treat formula display as one tool among many in your auditing arsenal. Combine it with Trace Precedents, Evaluate Formula, Watch Window, and Name Manager for comprehensive workbook inspections. Document your findings in a separate review sheet within the same workbook so future auditors can see what you checked and what you concluded. Over time these habits transform Excel from a tool you use into a discipline you practice, and the quality of every spreadsheet you touch improves measurably as a result.