You opened a workbook someone else built. You clicked a cell that should show a total. You saw a number instead of the formula behind it. That is default Excel behavior, and most of the time it is exactly what you want.

But when you are auditing a model, training a colleague, or hunting a stray reference that breaks your numbers, you need formulas visible across every cell. Excel gives you that view a few different ways — a keyboard flick, a settings change, or a workaround that shows one formula as text without flipping the whole sheet.

This guide walks the full picture. Keyboard shortcut, ribbon button, Excel Options route, FORMULATEXT for single cells, the apostrophe trick, and the print setup tweaks you need so the output is actually readable. Want a quick test? Try our Excel formulas and functions practice test when you finish.

Each technique is paired with the scenario where it shines. The keyboard shortcut is for fast audits. The ribbon button is for environments where shortcuts are blocked or forgotten. Excel Options is for baking the view into a saved workbook. FORMULATEXT is for documentation columns. The apostrophe prefix is for one-off notes. Knowing all five lets you pick the right tool without thinking.

One thing worth flagging up front: showing formulas is a viewing tool, not a calculation tool. It does not change what your formulas compute. It only changes what your eyes see in each cell. Flip it on, audit, flip it back off. Your data, your dependencies, and your charts stay exactly where they were before you pressed the shortcut.

The fastest way to show formulas in Excel is the keyboard shortcut Ctrl + `. The second key is the grave accent, the one under the tilde (~) on a US keyboard, just left of the number 1.

Hit it once and every cell on the active worksheet switches from showing calculated results to showing the underlying formulas. Hit it again and you are back to normal view. The toggle is per-worksheet, not per-workbook, so flipping it on Sheet1 leaves Sheet2 untouched.

On a Mac, the same shortcut is Control + ` (use Control, not Command). UK layouts have the backtick in the same spot as US. German layouts need Shift plus the key above Tab. If you cannot find the key, jump to the ribbon method — same result.

One detail catches people off guard. When formula view turns on, Excel widens every column automatically so the whole formula fits. That throws your layout off and makes printed sheets ugly. Excel remembers the original widths and restores them when you toggle back off, so just press the shortcut a second time.

Not everyone is comfortable with keyboard shortcuts, and some training environments lock them down. The ribbon route works in every modern Excel version — Microsoft 365, Excel 2024, 2021, 2019, 2016, 2013. The wording shifts slightly between versions but the path is the same.

Click the Formulas tab on the ribbon. In the Formula Auditing group near the right end, you will see a button labeled Show Formulas. The icon is a small worksheet with the letters fx on it. Click it. Every cell on the active sheet now displays its formula.

The button stays highlighted while the view is active, which is a handy visual reminder you are not looking at calculated results. Click it a second time to switch back. The state is per-worksheet, so the button changes appearance as you switch tabs.

The same Formula Auditing group also gives you Trace Precedents, Trace Dependents, Evaluate Formula, and the Watch Window. If you are showing formulas because you are debugging, those four belong in your back pocket. Evaluate Formula in particular walks through nested logic one step at a time — faster than mentally parsing a long IF.

Both the shortcut and the ribbon button toggle the same underlying setting, and that setting lives in Excel Options. You can change it from there too. Useful when you need to bake the formula view into a workbook so it opens that way every time, or when you want to control it through VBA.

Go to File > Options > Advanced. Scroll until you reach Display options for this worksheet. A dropdown lets you pick which sheet the settings apply to. Below it sits the checkbox labeled Show formulas in cells instead of their calculated results. Tick it and click OK.

The setting is per-worksheet, not per-workbook. For a 12-tab workbook, enable it on each sheet individually — or run a one-line macro. The VBA property is ActiveWindow.DisplayFormulas = True. Loop across Worksheets and you are done in under a second.

One quirk to know: the Options route does not toggle. It explicitly sets the state. If you script it as part of a workbook open event, the macro always shows formulas, never hides them. To create a true toggle in VBA you read the current value first, then flip it. Most people leave the macro as a one-way switch and use Ctrl + ` for the reverse.

Sometimes you do not want the whole sheet flipped. You want to show a formula in one specific cell — maybe a documentation column next to your live calculations — while the rest of the worksheet calculates normally. Three clean ways:

The FORMULATEXT function. Introduced in Excel 2013. It takes a single cell reference and returns the formula in that cell as a text string. If A1 contains =SUM(B1:B10), then =FORMULATEXT(A1) in cell C1 displays the literal text =SUM(B1:B10). The original cell keeps its calculated result. Gold standard for documentation columns.

Format the cell as text before typing. Select the cell, press Ctrl + 1, switch to Text under the Number tab, click OK. Anything you type, including a formula that starts with equals, gets stored as a literal string. Useful for tutorial spreadsheets, but careful — formulas you paste in later stay broken until you reformat and re-enter.

The apostrophe prefix. Type a single straight apostrophe (') before your equals sign — like '=SUM(A1:A10) — and Excel treats the whole thing as text. The apostrophe itself does not display in the cell, only in the formula bar. Fastest one-off trick when you just need to write a quick note.

Which one wins depends on context. FORMULATEXT is the right answer for permanent documentation. Apostrophe is the right answer for a single ad-hoc note. Text format is right when you are building a tutorial sheet where the formulas themselves are the content. Mix them as needed — they coexist on the same worksheet without conflict.

If you are turning formula view on to print an audit trail, you need two extra steps or the output will look terrible. First, Excel widens columns automatically when formulas show, and the page spills across many printed sheets. Second, the default page setup has no gridlines or row/column headings, which makes a formula printout almost useless for reviewing.

Open Page Layout > Page Setup and click the small dialog launcher in the bottom-right of the group. Under the Sheet tab, tick both Gridlines and Row and column headings. Under the Page tab, switch orientation to Landscape and set Scaling to Fit to 1 page wide.

For very large worksheets, also turn on Repeat rows at top under the Sheet tab and pick row 1. Otherwise the column headers vanish after page one and the printout becomes a wall of disconnected formulas with no labels. Most teams skip this step and end up with stacks of paper nobody can navigate.

Finance, accounting, and operations teams routinely save printed or PDF formula views as part of model documentation. When an external auditor or new analyst asks how a number was built, you hand them a single sheet that shows the formulas, the cell addresses, and the headings. It beats walking them through the live workbook cell by cell.

Four problems trip people up when they try to show formulas. Each has a quick fix once you know what to look for.

Problem one: the formula displays as text and never calculates. The cell is formatted as Text, or the formula has an apostrophe prefix, or the workbook is in manual calculation mode. Fix the first with Ctrl + 1 > General, then F2 and Enter to re-enter. Fix the second by deleting the apostrophe. Fix the third under Formulas > Calculation Options > Automatic.

Problem two: Ctrl + ` does nothing. Either your keyboard layout has the grave accent in an unexpected place, or another app intercepted the shortcut. Test by switching keyboard input language temporarily, or just use the ribbon. Some non-US layouts use Ctrl + Shift + ` instead.

Problem three: only some cells show formulas, others show results. The "working" cells likely contain text that looks like a formula — pasted from a screenshot or notes — not a real formula. Click one. If the formula bar shows the same string as the cell rather than calculated output, the formula is broken at the cell level. Rebuild it.

Problem four: formulas show but values look wrong. This is not a display bug. It is a real calculation problem hiding behind correct-looking formulas. Common causes are mixed absolute and relative references that drag wrong when copied, broken named ranges that silently return zero, and #REF errors that propagate through downstream formulas. Use Trace Precedents and Evaluate Formula to diagnose.

Knowing how to toggle the view is half the skill. Knowing when matters just as much.

Use formula view when you inherit a workbook and need a fast overview of which cells calculate and which are hardcoded numbers. Hardcoded values hide inside what looks like a calculated column, and the bug only shows up months later when totals stop matching reality. Flipping the view exposes them in seconds.

Use it when training a colleague on a model and you want to walk through the logic out loud. Showing the formula in context, with surrounding labels visible, beats reading from the formula bar one cell at a time. The audience follows the structure naturally.

Use it when you suspect a circular reference but the warning bar is missing. Excel's circular reference detection is reliable but quiet. With warnings off, broken loops sit unnoticed. Formula view lets you eyeball every cell at once and catch a stray reference back to itself.

Use it before sending a financial model to an external reviewer. A quick scan reveals copy-paste errors, hardcoded overrides someone forgot to remove, and inconsistent formula patterns across rows that should be identical. Skip it during normal work — numbers communicate faster than formulas, and that is the point of the tool.

Two advanced tricks save real time once you are comfortable with the basic toggle.

Find every formula on a sheet without flipping the view. Press F5, click Special, choose Formulas, click OK. Excel selects every formula cell and leaves hardcoded values alone. Color-format the selection — say, light blue background — to permanently mark which cells calculate. Run it again any time to verify nothing has changed. Faster than reading formula view and the result lasts.

Compare two versions of a workbook by formula. If you have Excel Professional Plus or Microsoft 365 with the Inquire add-in enabled, Compare Files shows a side-by-side diff at the formula level. Changed cells are highlighted by category — entered values, calculated values, formulas, formatting. The add-in is hidden by default; turn it on under File > Options > Add-ins > Manage: COM Add-ins > Go > tick Inquire.

Neither trick replaces showing formulas across the whole sheet. They sit alongside it as part of a complete audit toolkit. Skilled Excel users move between all three depending on what they need to see, and that fluency is a real productivity multiplier in roles where spreadsheet integrity actually matters.

Excel makes showing formulas easy once you know the routes. Ctrl + ` for the keyboard. Formulas > Show Formulas for the ribbon. File > Options > Advanced for the saved setting. FORMULATEXT for one cell. The apostrophe prefix for a quick one-off. Each method has a moment where it is the right tool.

Want to keep building? Our Excel basics practice test covers workbook navigation and formatting fundamentals. The Excel formulas and functions practice test drills nested logic, lookup functions, and error handling you actually meet on the job.

The more time you spend toggling formula view on real workbooks, the faster you spot trouble in models other people built — and the cleaner your own work looks when someone audits it next. Excel rewards the people who treat their spreadsheets like code, and showing formulas is one of the simplest habits that separates careful builders from everyone else.

One final note. Many Excel users go years without learning the Ctrl + ` shortcut because nothing in the default ribbon really screams "you should know this." But once you pick it up, you will reach for it constantly — every single time you inherit a spreadsheet, debug a number that does not match, or onboard a new analyst on the team. Spend ten honest minutes practicing on a workbook you already know well, then keep the muscle memory sharp.