How to Show Formulas in Excel Instead of Results
By ExcelReady TeamUpdated 5 min read
The short answer
To show formulas in Excel instead of their results, press Ctrl+ on Windows or Control+ on a Mac. You can also click Formulas > Show Formulas. Every cell then displays its formula, and the same shortcut switches back to results. To see one formula, click the cell and read the formula bar.
Key takeaways
- Ctrl+
(Control+on a Mac) or Formulas > Show Formulas switches the whole sheet between formulas and results. - The setting is per sheet and is saved with the file, so turn it off before you send the workbook.
- Show Formulas makes typed-in numbers stand out in a column of formulas, a common cause of wrong totals.
- FORMULATEXT shows one cell's formula as text next to its result (Excel 2013 or later).
- If only one cell shows its formula, it is stored as text; that is a different problem from Show Formulas.
On this page+
To show formulas in Excel instead of their results, press Ctrl+** on Windows or **Control+ on a Mac, or click Formulas > Show Formulas. Every cell on the sheet then displays the formula behind it, which is the fastest way to check how a sheet works or why a total is wrong. This guide covers the shortcut, how to see a single formula, how to print formulas, and how to fix a sheet that is stuck showing them.
How to show all formulas in Excel#
Show Formulas switches the whole sheet between results and formulas.
| Method | Windows | Mac |
|---|---|---|
| Keyboard | Ctrl+` | Control+` |
| Ribbon | Formulas > Show Formulas | Formulas > Show Formulas |
The ` key is the grave accent, usually to the left of the 1 key and above Tab. Press the same shortcut again to go back to results.
When Show Formulas is on, you will notice a few changes:
- Columns get wider, so long formulas fit.
- Number formats disappear: 4.5 shows as 4.5, not $4.50.
- When you select a formula cell, the cells it uses get coloured outlines.
Nothing about the data changes. It is only a different view.
How to see the formula in one cell#
To check a single formula, click the cell and look at the formula bar above the grid. It always shows the formula, while the cell shows the result.
For a closer look, press F2 (Control+U on a Mac) or double-click the cell. Excel shows the formula inside the cell and colours each reference, with a matching coloured box around the cells it uses. Press Esc to leave without changing anything.
To show a formula as text in another cell, use FORMULATEXT:
=FORMULATEXT(D2)If D2 holds =B2*C2, this returns the text =B2*C2 while D2 still shows 27. FORMULATEXT needs Excel 2013 or later. It is handy for teaching or documenting how a sheet works.
Use Show Formulas to find mistakes#
Show Formulas makes problems jump out, because every cell in a column of formulas should look almost the same. Take a simple invoice:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Total |
| 2 | Printer paper | 6 | 4.50 | =B2*C2 |
| 3 | Ink cartridge | 2 | 18.00 | 36 |
| 4 | Envelopes | 3 | 5.25 | =B4*C4 |
With results showing, D3 looks fine: 36 is correct today. With formulas showing, you can see someone typed the number. If the quantity changes, D3 won't update and the invoice total will be wrong. This is one of the most common causes of bad totals, and the Debugging SUM lesson trains you to find it.
Look for these signs when formulas are showing:
- A plain number in a column of formulas (a hard-coded value).
- A range that stops short, such as
=SUM(D2:D3)when the data goes to row 4. - A reference that jumps, such as
=B5*C4where you'd expect=B5*C5.
The guide to building an invoice with formulas shows how a clean version should look.
How to print formulas#
Printing the formulas is useful for checking a sheet on paper or sharing how it works.
- Press **Ctrl+
** (Control+on a Mac) to show formulas. - On the Page Layout tab, under Headings, tick Print, so row numbers and column letters print too.
- Print as usual with Ctrl+P (⌘P on a Mac).
- Press Ctrl+` again to go back to results.
Wide formulas may spill onto extra pages. Choose Landscape orientation or Fit All Columns on One Page in the print settings.
How to turn off Show Formulas#
If every cell on a sheet shows its formula, Show Formulas is on. Press Ctrl+` again, or click Formulas > Show Formulas to switch it off.
The setting belongs to each sheet and is saved with the file. So if you send a workbook with formulas showing, the person who opens it sees formulas too. You can also find the option in File > Options > Advanced, under the display options for the worksheet: "Show formulas in cells instead of their calculated results". On a Mac, it is in Excel > Settings > View.
When only one cell shows its formula#
If one cell shows =B2*C2 as text while the rest show results, Show Formulas is not the cause. The cell is storing the formula as text, usually because:
- The cell was formatted as Text before you typed the formula.
- The formula starts with a space or an apostrophe, like
'=B2*C2.
Change the format to General, then click in the formula bar and press Enter. The guide to why an Excel formula shows as text walks through every cause. If a formula shows a result but it is out of date, that's a calculation setting instead: see why an Excel formula is not updating.
How to find every formula on a sheet#
Show Formulas is a visual check. For a precise one, let Excel select the formulas for you. Press F5 (or Ctrl+G), click Special, choose Formulas and click OK. Excel selects every formula cell on the sheet. Choose Constants instead to select every typed-in value. Give the selection a fill colour and you have a map of which cells are calculated and which are typed, which is a quick way to audit a file someone else built.
Read formulas with confidence#
Showing formulas only helps if you can read them. Remember that Excel follows the order of operations: brackets first, then multiplication and division, then addition and subtraction. And if you are deciding between a formula and a one-off fill, Flash Fill vs formulas explains why formulas are easier to check later.
Practise it#
Reading and checking formulas is a core test skill. Start with your first formulas in a live spreadsheet to see how formulas recalculate, then find the hidden bugs in the Debugging SUM lesson. Each step is checked instantly. Try the line-total exercise on this page first.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Add the first line total: click D2, type =B2*C2 and press Enter. In Excel, you could then press Ctrl+` to see the formula instead of the result.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Total | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 |
Tip: Multiply the quantity in B2 by the price in C2 with *.
Practise this in a lesson
Frequently asked questions
What is the shortcut to show formulas in Excel?
Press Ctrl+ on Windows or Control+ on a Mac. The ` (grave accent) key is usually to the left of the 1 key. Press it again to show results.
Why is Excel showing formulas instead of results?
If every formula on the sheet shows, Show Formulas is on: press Ctrl+` to turn it off. If only one cell shows its formula, that cell is formatted as Text or starts with an apostrophe or space.
How do I print formulas in Excel?
Turn on Show Formulas with Ctrl+`, then print as usual. Tick Print under Headings on the Page Layout tab so the row numbers and column letters print too.
How do I show the formula of one cell next to its result?
Use FORMULATEXT in another cell, for example =FORMULATEXT(D2). It shows the formula in D2 as text while D2 still shows its result. It needs Excel 2013 or later.
What if Ctrl+` doesn't work on my keyboard?
On some keyboard layouts the ` key is elsewhere or needs another key. Use Formulas > Show Formulas on the ribbon instead, which works on every keyboard.