MrExcel Publishing
Your One Stop for Excel Tips & Solutions

See all Formulas

September 06, 2017 - by Bill Jelen

See all Formulas

Excel usually shows you the results of your formulas. What if you would rather see all of the formulas? This article will show you the simple Show Formulas mode.

You inherit a spreadsheet from a former co-worker. You need to figure out how the calculations work. You could visit each cell one at a time and look at the formula in the formula bar. Or, you could quickly toggle between pressing F2 and Esc to see the formula right in the cell.

Sample Data Set
Sample Data Set

But there is a faster way. On most U.S. keyboards, just below the Esc key is a key with two accent characters: the tilde from Spanish and the grave accent from French. It is an odd key. I don’t know how I would ever use this key to actually type Piñata or Café.

The Faster Way
The Faster Way

If you hold down Ctrl and this key, you toggle into something called Show Formulas mode. Each column gets wider, and you see all of the formulas.

Show Formulas Mode
Show Formulas Mode

This gives you a view of all the formulas at once. It is great for spotting “plug” numbers or when someone added the totals with a calculator and typed the number instead of using =SUM(). This is great you can see that the co-worker left RANDBETWEEN functions in this model.

Watch Video

  • F2 puts a formula in edit mode, Esc will exit edit mode
  • Faster way to see all formulas: Ctrl + `
  • Press again to toggle back

Video Transcript

Learn Excel form MrExcel podcast, episode 2020 - See All Formulas at One Time!

Click the “i” on the top-right hand corner, you get to the playlist for all the videos in this series, podcasting this entire book!

Welcome back to the MrExcel netcast, I'm Bill Jelen. Alright, so you've inherited a spreadsheet from someone, you need to see how they're building the formulas. You could always press F2 to see one formula and then Escape to go back, F2 Escape, F2 Escape, F2 Escape, that's a huge hassle. Alright, so on your keyboard, if you're in the US, on the left-hand side you have an Escape key underneath that accent key with a ~ and the ` from French accent égout, also known as a grove accent. Sometimes in Europe, these keys are different keys, it's the grove accent key that matters, press Ctrl+` key, and it will show you all of the formulas in the spreadsheet, see, the columns get a little bit wider.

And we're now in Show Formulas mode and- oh shoot, look at that, someone put a plug number in, it's great, like all these formulas are some other, then that one sticks out like a sore thumb, and we still have RANDBETWEENs. Just a great way to see all of the formula cells in one single go, alright, and to get out, just Ctrl+`, again, it's a toggle, formulas, numbers, formulas, numbers. There's also out here on Formulas, there's a Show Formulas button that you can use if you can't remember the Ctrl+’.

This tip and many more in this book, click that “i” in the top-right hand corner, go out and buy the book, I sincerely would appreciate it. Alright, F2 puts it in Formula Edit mode, Escape will exit the Edit mode, lets you see formulas one of the time. But a faster way to see them all, Ctrl+`, press again to toggle back. You know, this book was crowdsource, people send in their favorite tips, Ron Armstrong, Olga K, and Sven Simon, all sending this as their favorite Excel tip, thanks to them.

And thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!

Download File

Download the sample file here: Podcast2020.xlsx

Title Photo: Kapa65 / Pixabay

Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.