MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Highlight All Formula Cells


September 07, 2017 - by Bill Jelen

Highlight All Formula Cells

Excel - how to mark or highlight all of the formula cells in a spreadsheet. This isn't Show Formulas. It is about finding all of the formula cells and marking them in red or orange.

If you are going to be auditing the worksheet, it would help to mark all of the formula cells. Here are the steps:

  1. Select all cells using the box just above and to the left of cell A1.
  2. Press Ctrl + G for Go To and then Alt + S for Special. In the Go To Special dialog, choose Formulas and click OK. Alternatively, you can choose Home, Find & Select, Formulas.
  3. All of the formula cells will be selected. Mark them in a different font color, or, heck, use Home, Cell Styles, Calculation.
Cell Style - Calculation
Cell Style - Calculation

To mark all of the input cells, follow the same steps, but choose Constants in the Go To Special dialog. I prefer to then uncheck Text, Logical, and Errors, leaving only the numeric constants.

Go To Special
Go To Special

Why Is the F1 Key Missing from Your Keyboard?

I served as a judge for the ModelOff Financial Modeling Championships in NYC twice. On my first visit, I was watching contestant Martijn Reekers work in Excel. He was constantly pressing F2 and Esc with his left hand. His right hand was on the arrow keys, swiftly moving from cell to cell.

F2 puts the cell in Edit mode so you can see the formula in the cell. Esc exits Edit mode and shows you the number.

Martijn would press F2 and Esc at least three times every second.

But here is the funny part: What dangerous key is between F2 and Esc

F1. 

If you accidentally press F1, you will have a 10-second delay while Excel loads online Help. If you are analyzing three cells a second, a 10-second delay would be a disaster. You might as well go to lunch. So, Martijn had pried the F1 key from his keyboard so he would never accidentally press it.

Photo Credit: MrsExcel
Photo Credit: MrsExcel

Martijn was competing for a $10,000 first prize, but I still thought this was a little over-the-top. However, as I talked to the other 15 finalists, most admitted to having removed F1 from their keyboards, along with Caps Lock and Insert.

Thanks to Ron Armstrong, Olga Kryuchkova, and Sven Simon for suggesting this feature.

Watch Video

  • How to highlight all of the formula cells in a spreadsheet
  • Select all cells using the icon northwest of A1
  • Home, Find & Select, Formulas
  • or, Find & Select, Go To Special, Formulas
  • Once you have the formulas selected, use Cell Styles or a Fill Color
  • Look for things that should be formulas that are not!
  • Once you have the formulas selected you can use Trace Precedents
  • Or, if you turn of Edit Directly in Cells, Double-Click
  • Once you have multiple cells selected, press Enter to move to the next cell

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2021 highlight all formulas
  • I am podcasting this entire book click
  • in the top right hand corner up there to
  • get to the playlist alright
  • episode 26 B this is even one of the 40
  • tips right I you know I say the excel on
  • the front of the cover is for the Roman
  • numeral of 40 and that there's 40 tips
  • in the book but that is such an
  • unexamined 40 tips like this little
  • bonus tip like yesterday I showed you
  • how to use control in the crop accident
  • to see all the formulas at once you know
  • but that's a really temporary thing what
  • if we need to go audit all those
  • formulas first thing we do is we select
  • all of the cells I use this little icon
  • up here I know some people use ctrl a
  • and that works unless you're at a table
  • alright anyway so we select all cells
  • and then oh geez the book the book tells
  • you go to home find and select go to
  • special select formulas and I've been
  • doing that over and over and over and
  • over go to special and finally I was
  • doing a seminar one of my live power
  • Excel seminars recently and I noticed
  • that right underneath go to special is
  • something called formulas and while go
  • to special is amazing it offers all
  • kinds of amazing things like formulas
  • that are errors or constants that are
  • numbers or blank cells or visible cells
  • only I use those over and over and over
  • again I had to admit that home fine and
  • select and formulas is just the much
  • much easier way to go alright so hey
  • first off look at this instantly you can
  • tell that that number which all the
  • other numbers in the column are formulas
  • that number for some reason is
  • hard-coded
  • alright that's a problem this number
  • which should be not a formula is a
  • formula for some reason alright so if we
  • had to go through an audit each one of
  • these we would want to do something to
  • change these formula cells like maybe
  • apply some sort of a color or you know
  • maple heck week even go to cell styles
  • there's a calculation out here right the
  • ugly orange on the gray background
  • something so that way you can go through
  • and find these one at a time all right
  • so now once we found these well what can
  • you do one thing you can do
  • select the cell that has the former then
  • go to the formulas tab and ask for trace
  • precedents all right now this is one of
  • those features that makes it into the
  • afternoon seminar right it's not one of
  • my it's not one of my top 40 tips so
  • it's not in the book this this tips not
  • in the book but what I really really
  • love about this book is after I got done
  • with the book in my 40 tips and Sylvia's
  • 20 tips and then another 10 tips for I
  • know well in 40 Excel shortcut keys I I
  • went through all of the tips that people
  • tweeted to me right and I went through
  • all these tweets hundreds of tweets and
  • I made sure that that I included
  • people's tips and there was a list of 40
  • tips at the end that didn't make it into
  • the book right but they were tips that
  • were self explanatory in a 140 character
  • tweet all right and so on page 223 and
  • 224 the book I put in 40 tips that
  • aren't in the book and now hey they're
  • in the book right so they're those are
  • counted at all they're not in the 40 or
  • the bonus 30 or the extra bonus tips and
  • so as I was I thinking about this I
  • remember there was a great tip there
  • from two just Giants in the Excel world
  • Shawn blessed and David Langstrom both
  • have great Twitter feeds fascinating to
  • watch their Twitter feeds and they said
  • turn off edit directly in a cell and
  • then double click of formulas show cells
  • using that formula even if they're an
  • external workbook alright so how do we
  • do this we get a file and then options
  • go to the huge Advanced tab but it's
  • right here on the first one allow
  • editing directly and cells so I remember
  • back when he had a bill to form this in
  • the formula bar and all of a sudden they
  • allowed us to build the formulas right
  • at the cell that was a big thing that
  • was a selling feature back in like
  • excelled I don't know 95 or somewhere a
  • long time ago now I can do this like
  • that pharmacy formula is referring to 4
  • cells when I double-click those 4 cells
  • become highlighted as a group and the
  • first one d6 is selected and you might
  • know this when you have cells multiple
  • cells selected pressing enter moves to
  • the next cell and the group right so I
  • just selected all for precedent cells
  • and now I can go investigate those one
  • at a time by pressing enter even if
  • they're on different sheets or in
  • different workbooks isn't that a beaut
  • tip right not one of my top 40 but still
  • a beautiful tip those two pages in the
  • book are just a cool cool two pages
  • because they're tips that aren't at my
  • seminar but are still awesome excel tips
  • and I'm happy and honored that that a
  • lot of people contributed tips to this
  • book all right well if you're watching
  • this on the day it came out it's Friday
  • it's payday you can get the whole book
  • the e-book for 10 bucks about five
  • pounds just a great deal go click that
  • high on the top right hand corner and
  • you'll have all of these tips my tips
  • other people's tips keyboard shortcuts
  • great great collection of Excel
  • knowledge all right episode recap we
  • want to show how to highlight all the
  • formulas cells in the spreadsheet select
  • all cells I use this icon up here
  • northwest of a1 some people use ctrl a
  • home find in select form cuz that tips
  • even better than the tip in the book
  • where it's at fine and just select go to
  • special formulas and then once you have
  • the cells all the formulas like to use a
  • cell style font style a fill color
  • whatever you need to do is that we can
  • highlight those look for things that
  • should be forms as they're not I I
  • showed you how to use trace precedents a
  • tip that's not in the book and one of
  • those forty tips that's that I said
  • wasn't in the book but that I put him in
  • the book anyway turn off edit directly
  • in cells double click it selects all the
  • precedent cells and then you can just
  • press ENTER to move from item item to
  • item well hey thanks the David and Shawn
  • for that last tip and thanks to you for
  • stopping by we'll see you next time for
  • another net cast from mister Excel

Download File

Download the sample file here: Podcast2021.xlsx

Title Photo: Meditations / Pixabay