Highlight All Formula Cells

Learn Excel from MrExcel podcast, episode 2021 - Highlight All Formulas!

I’m podcasting this entire book, click in the top-right hand corner up there to get to the playlist!

Alright, episode 26b, this isn’t even one of the 40 tips, right? You know, I say the XL 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 exaggeration, because there’s far more than 40 tips, like this little bonus tip. Like yesterday I showed you how to use Ctrl+` 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 on a table, alright. So we select all cells, and then, oh geez, the book tells you to go to Home, Find & Select, Go To Special, select Formulas, and I've been doing that 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, Find & 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 hardcoded. 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, heck, we can 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, alright. So now, once we found these well, what can you do?

One thing you can do select the cell that has the formula, then go to the Formulas tab and ask for Trace Precedents, alright. Now this is one of those features that makes it into the afternoon seminar, right, it's not one of my top 40 tips, so it's not in the book, this tips not in the book. But what I really, really love about this book is after I got done with the book, and my 40 tips, and Szilvia's 20 tips, and then another 10 tips for, I don’t know, 40 Excel shortcut keys. 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 alright.

And so on page 223 and 224 of the book I put in 40 tips that aren't in the book, and now, hey, they're in the book right, so those aren’t 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 Blessitt and David Ringstrom. 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 a formula to show cells used in that formula, even if they're in an external workbook.” Alright, so how do we do this?

We go to File and then Options, go to the huge Advanced tab, but it's right here on the first one, Allow Editing Directly in Cells. So, I remember back when we had to build formulas 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 Excel, I don't know, 95 or somewhere, a long time ago. Now I can do this, select the formula, see that 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 multiple cells selected, pressing Enter moves to the next cell in the group, right? So I just selected all 4 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 beautiful 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. Alright 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, about £5, just a great deal. Go click that “i” 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.

Alright, 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 $ Select, Formulas, that tip’s even better than the tip in the book, where it's just Find & Select, Go To Special, Formulas. And then once you have all the formulas selected, use a cell style, font style, a fill color, whatever you need to do, so that we can highlight those. Look for things that should be formulas but they are not, I showed you how to use Trace Precedents, a tip that's not in the book. And one of those 40 tips that I said wasn't in the book, but that I put it 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 to item.

Well hey, thanks to David and Shawn for that last tip, and thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!