How To Show Formulas in Excel for Clarity

Last updated: August 18, 2026

Switching the view is the easy part. The questions that actually cost people time are the ones after it. Why won't the shortcut fire on my keyboard? How do I show the formula in just one cell without flipping the whole sheet? How do I print the formulas? And why is a single cell stuck showing a formula when nothing else is?

This guide covers the six ways to display formulas, then the troubleshooting and printing cases that the shortcut alone does not solve.

Which method should I use? A comparison table

Excel offers six ways to reveal formulas, and they are not interchangeable. Some flip the entire worksheet, one shows a single formula as text, and one is for auditing rather than display.

Rule of thumb: Show Formulas is a per-worksheet display setting that saves with the workbook. FORMULATEXT is a formula that lives in a cell. Everything else is an auditing tool.

How do I show formulas with a keyboard shortcut?

Press Ctrl + `. The grave accent sits above the Tab key on a US keyboard, sharing a key with the tilde. You do not hold Shift.

Two things happen at once, and both are normal:

• Every formula on the sheet displays as text instead of its result.
• Excel roughly doubles the column widths so the formulas fit. It restores the original widths when you toggle back.

Press Ctrl + ` again to return to normal. On Excel for Mac the shortcut is the same.

Rule of thumb: if all your columns suddenly got wider, you are in Show Formulas mode. That is the fastest way to recognise it.

What if Ctrl + ` does nothing on my keyboard?

This is the most common complaint about the shortcut, and it is a keyboard layout problem rather than an Excel problem.

• Non-US layouts. On UK, French, German, and Nordic layouts the grave accent is often a dead key, or lives somewhere other than above Tab. On a UK keyboard, try the key to the left of the number 1. On layouts where the accent is a dead key, you may need to press it twice.
• It is not Ctrl + ~. A lot of guides write the shortcut as Ctrl + ~ because the tilde is printed on the same key. Adding Shift changes it. In Excel, Ctrl + Shift + ~ applies the General number format, which is a different command entirely.
• It is not Ctrl + '. The apostrophe is a separate key. Ctrl + ' copies the formula from the cell above into the current cell.
• Laptop function layers. Some compact keyboards put the grave accent behind an Fn layer.

The reliable fallback: use the ribbon button. It does exactly the same thing and does not depend on your keyboard layout at all.

Rule of thumb: Ctrl + backtick, no Shift. If Shift is involved, you are running a different command.

How do I show formulas from the ribbon?

The ribbon route is layout independent and shows you the current state, because the button stays highlighted while the mode is active.

1. Open your workbook and go to the Formulas tab.

   

2. Find the Formula Auditing group, where the Show Formulas button sits.

   

3. Click Show Formulas. The worksheet immediately displays the formula in each cell instead of its result.

   

Click it again to toggle back. Microsoft documents the behaviour in Display or hide formulas.

Rule of thumb: the highlighted button is your state indicator. If you are not sure whether the mode is on, look at the ribbon rather than guessing from the cells.

How do I make the setting stick for one worksheet?

Show Formulas is a worksheet-level setting, not a workbook-level one. The shortcut and the ribbon button both apply to whichever sheet is active. If you want to switch it on for a specific tab without navigating to it first, use Excel Options.

1. Click File, then Options.
2. Go to the Advanced tab.
3. Scroll to Display options for this worksheet. The dropdown next to that heading is the important part. It names which worksheet the setting will apply to.
4. Tick Show formulas in cells instead of their calculated results.
5. Click OK.

   

Because it is per worksheet, this setting saves with the file. Anyone who opens that workbook sees the formulas on that tab until someone toggles it off. That is a frequent cause of a colleague reporting that your spreadsheet is broken.

Rule of thumb: if a workbook opens showing formulas on one tab only, someone left this checkbox ticked and saved the file.

How do I show the formula in only one cell?

Show Formulas is all or nothing per sheet. When you want a single formula visible next to its live result, use the FORMULATEXT function instead.

The syntax is =FORMULATEXT(reference). It returns the formula in the referenced cell as a text string.

Worked example. Cell D2 contains =B2*C2. In cell E2, enter:

=FORMULATEXT(D2)

Cell E2 now displays =B2*C2 as text, while D2 continues to show the calculated result. Fill E2 down the column and you have a self-documenting model, with every formula printed beside the number it produced.

Three things to know before you rely on it:

• If the referenced cell holds a value rather than a formula, FORMULATEXT returns #N/A. Wrap it as =IFERROR(FORMULATEXT(D2),"") to leave those rows blank.
• It updates live. Change the formula in D2 and the text in E2 follows.
• It is available in Excel 2013 and later. Older versions return #NAME?.

Rule of thumb: FORMULATEXT is the only method that shows a formula and its result on screen at the same time.

How do I find and highlight every cell that contains a formula?

Sometimes the question is not what the formulas say but where they are, particularly when you have inherited a workbook and need to know which numbers are typed in and which are calculated.

1. Go to the Home tab and click Find & Select in the Editing group.
2. Choose Formulas. Excel selects every formula cell on the sheet at once.
3. With them still selected, apply a fill colour so the map stays visible after you click away.

For finer control, use Find & Select > Go To Special, choose Formulas, and tick only the result types you care about. Selecting just Errors, for example, jumps you straight to every broken cell in the sheet.

Rule of thumb: Go To Special > Formulas > Errors is the fastest audit in Excel. It finds every #REF!, #VALUE!, and #N/A on a sheet in two clicks.

How do I trace what a formula depends on?

Displaying a formula tells you what it says. It does not tell you where its inputs come from, which is usually the real question when a number looks wrong. Three tools in the same Formula Auditing group handle that.

• Trace Precedents. Select a cell and click it. Excel draws arrows from every cell that feeds this formula. Click again to go one level further back.
• Trace Dependents. The reverse. It shows every formula that relies on the selected cell, which is what you check before deleting anything.
• Evaluate Formula. Steps through a nested formula one operation at a time, showing the interim result at each stage. This is the fastest way to find which argument in a long IF or lookup is failing.

Use Remove Arrows in the same group to clear the tracing lines when you are done.

If a formula is displaying rather than calculating, that is a separate problem. Our guide to Excel formulas not calculating covers the causes.

Rule of thumb: Show Formulas tells you what the sheet is doing. Trace Precedents tells you why.

How do I print an Excel sheet with the formulas visible?

Printing formulas is a standard requirement in accounting reviews and in most Excel coursework. Excel has no separate print formulas command, so the process is to switch the view on first and then handle the layout, which is where it usually goes wrong.

1. Turn on Show Formulas with Ctrl + ` or the ribbon button.
2. Go to Page Layout > Page Setup and open the Sheet tab.
3. Tick Gridlines and Row and column headings. Without the headings, a printed formula sheet is very hard to follow, and most instructors require them.
4. On the Page tab, set orientation to Landscape and use Fit to 1 page wide by [blank] tall. Leave the height blank so the sheet can run over several pages vertically rather than being shrunk to nothing.
5. Check File > Print in the preview, then print.
6. Press Ctrl + ` again afterwards to restore the normal view and your original column widths.

The reason a formula printout so often comes out unreadable is step 4. Show Formulas doubles the column widths, so a sheet that fitted on one page in normal view now spills across three or four. Fit to 1 page wide solves it.

Alternative: if you want the formulas and the results on the same printout, build a FORMULATEXT column next to your data and print in the normal view.

Rule of thumb: always turn on row and column headings before printing formulas. Without them the reader cannot tell which cell any formula belongs to.

How do I hide formulas so other people cannot see them?

The reverse case matters when you send a workbook out and want the results visible but the logic private. Excel handles this with two cell attributes that only take effect once the sheet is protected.

1. Select the cells that contain the formulas you want to hide.

   

2. Right-click and choose Format Cells.

   

3. Go to the Protection tab and tick Hidden. Leave Locked ticked as well. Hidden on its own does nothing.

   

4. Go to Review > Protect Sheet, set a password if you want one, and click OK. Nothing is hidden until this step is done.

   

5. Click one of those cells now. The result displays in the cell, and the formula bar is empty.

Two honest caveats. Sheet protection is a deterrent, not security, and it is straightforward to remove. And Show Formulas will not reveal hidden formulas on a protected sheet, so if a shared workbook seems to have blank formula bars, check whether protection is on.

Rule of thumb: Hidden plus Locked plus Protect Sheet. Miss any one of the three and the formulas stay visible.

Why is my cell showing the formula instead of the result?

This is the opposite problem, and it lands people on this page constantly. You did not turn anything on, yet a cell displays =SUM(A1:A10) as plain text. There are four causes, and one quick test tells them apart.

The test: does it affect the whole sheet or just one cell? Whole sheet with wide columns means Show Formulas. A single cell with normal column widths means a formatting or content problem in that cell.

The Text formatting case catches almost everyone, and the important detail is that changing the format is only half the fix. You have to re-enter the formula afterwards, because Excel decided the cell was a text cell at the moment you typed it. For a whole column, use Data > Text to Columns > Finish to re-commit every cell in one pass.

Rule of thumb: whole sheet means a setting, one cell means that cell. That single question splits the four causes in half instantly.

How do I show formulas in Google Sheets?

If you are working across both platforms, the shortcut is identical but the menu path is different.

• Shortcut: Ctrl + ` on Windows, Cmd + ` on Mac.
• Menu: View > Show > Formulas.
• Google Sheets also supports FORMULATEXT with the same syntax, so a documentation column built in Excel survives the conversion.

One difference worth knowing. In Google Sheets the setting applies to your view of the file only, so turning it on does not change what your collaborators see. In Excel the setting saves with the workbook.

Rule of thumb: Excel saves the formula view into the file, Google Sheets keeps it local to you.

Final Thoughts

For everyday checking, Ctrl + ` is all you need. The cases worth remembering are the awkward ones. Use FORMULATEXT when you need one formula visible beside its result, turn on row and column headings before printing, and remember that Hidden formatting does nothing until the sheet is protected.

And if a cell is showing a formula you did not ask it to show, check whether it is the whole sheet or just that one cell. That question answers it faster than anything else.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions

What is the shortcut to show formulas in Excel?

Press Ctrl + `, the grave accent key above Tab, without Shift. It toggles the active worksheet between showing formulas and showing results. The same shortcut works in Excel for Mac and in Google Sheets.

Why is Ctrl + ` not working on my keyboard?

The grave accent is in a different position on non-US layouts and is sometimes a dead key. Note that Ctrl + Shift + ~ applies the General number format instead, and Ctrl + ' copies the formula from the cell above. Use Formulas > Show Formulas in the ribbon as a layout-independent alternative.

Can I display formulas in only one worksheet?

Yes. Show Formulas is a per-worksheet setting. Toggling it affects only the active sheet. To set it on a specific tab without navigating there, use File > Options > Advanced and pick the worksheet from the Display options for this worksheet dropdown.

How do I show the formula in a single cell?

Use FORMULATEXT. If cell D2 contains =B2*C2, entering =FORMULATEXT(D2) in another cell displays =B2*C2 as text while D2 keeps showing its result. It returns #N/A if the referenced cell has no formula, so wrap it in IFERROR when filling a column.

How do I print an Excel sheet with formulas visible?

Turn on Show Formulas, then go to Page Layout > Page Setup. On the Sheet tab, tick Gridlines and Row and column headings. On the Page tab, choose Landscape and Fit to 1 page wide, leaving the tall box blank. Show Formulas doubles column widths, which is why unadjusted printouts spill across pages.

How do I hide formulas from other people?

Select the cells, open Format Cells > Protection, and tick both Hidden and Locked. Then go to Review > Protect Sheet. The formula bar stays empty for those cells while the results remain visible. Sheet protection is a deterrent rather than real security.

Why does my cell show the formula instead of the result?

If every formula on the sheet is affected and the columns are wider than usual, Show Formulas is on, so press Ctrl + `. If only one cell is affected, the cell was formatted as Text before entry, or the formula bar starts with an apostrophe or a space. Fix the format, then press F2 and Enter to re-commit the formula.

Does the Show Formulas setting save with the file?

Yes. In Excel it is stored per worksheet and travels with the workbook, so anyone who opens the file sees formulas on that tab until someone toggles it off. Google Sheets treats it as a local view setting instead, so collaborators are unaffected.

Related Articles

How to Fix Excel Formulas Not Calculating, Working, or Updating

Everything You Need to Learn on Auditing Formulas in Excel

How To Remove Formula in Excel While Keeping the Data

Excel Conditional Formatting Based on Another Cell

How to Use PI in Excel

How to Sort by Date in Excel