How to Show Formulas in Excel:
Learn to Display Cell Formulas In 2 Clicks
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
When you enter a formula into Excel, it disappears and gets replaced by the result of the function.
Which is great. But sometimes you want to look back on the formula to see what it is.
Excel has some built-in tools that can help, though. We’ll take a look at them here, and you’ll never have to struggle to get the details on your formulas again.
Yes, I know that you can click on a cell and look at the formula bar.
But the first thing we’re going to talk about is why that isn’t the best way to go.
Kasper Langmann, Co-founder of SpreadsheetoWhy not just click a formula cell?
The simplest way to find a formula is to click on a cell and look at the formula bar:
This often works well.
But there are some cases where it doesn’t.
For example, if you want to see the HYPERLINK formula behind a URL in your sheet, clicking on the cell will open the link.
And if you want to see more than one formula at a time, this method doesn’t work at all.
To solve problems like these, we’ll show you how to view all the formulas in your spreadsheet at once.
Kasper Langmann, Co-founder of SpreadsheetoFormula auditing mode
To make Excel show you the formula behind every cell in your spreadsheet, you’ll need to engage formula auditing mode.
The keyboard shortcut for this is simple: Ctrl + ` (that’s known as the “grave accent,” and you’ll find it to the left of the 1 key on your keyboard, above the Tab button).
Protip: Also check out these 7 little-known shortcuts.
Just hit those two button on your keyboard and your formulas will show themselves:
This will show every formula in your spreadsheet, but won’t change any other cells.
Kasper Langmann, Co-founder of SpreadsheetoPress Ctrl + ` to display formula results in cells again.
You can also find this option in the Formulas tab of the Ribbon. Click on the Show Formulas button in the Formula Auditing section.
Pro tip: trace formula relations
In the Formula Auditing box, you’ll also see the Trace Precedents and Trace Dependents buttons.
These buttons overlay arrows onto your spreadsheet to show you how different cells are connected:
If you need a visual reminder of the flow of your spreadsheet, this is a great tool for it.
When you’re done, click the Remove Arrows button to clear them from your spreadsheet.
Never lose formulas again
With a simple key combination, your can view all of the formulas in your Excel spreadsheet.
No more trying to figure out how you got that number or ended up with a strange string of text!
And if you use precedents and dependents, you’ll get even more information about your formulas with just a couple clicks.
This is a trick that a lot of people don’t know, so it also gets you one step closer to Excel mastery!
Kasper Langmann, Co-founder of Spreadsheeto