Mark Linked Cells

Learn Excel From MrExcel, Episode 12154: Mark Linked Formulas.

Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Today, a way to see which cells are formula cells and [unintelligible – 00:12] cells are linked formula cells.

Alright. So, here's what we're going to do. We have some data here and I just put a lot of 1234s in here. Q1 and Q2, those are SUM cells that go across, and then I also have just a bunch of random formulas that I put in here, and actually let's, down at the bottom, let's put a TOTAL row in. So, here we'll choose all those cells and we can put the AUTOSUM in at one fell swoop.

Alright. So, here’s what we're going to do. We're going to go up to A1, select all of our data, and note that A1 is the active cell. That's really important as we set this up. CONDITIONAL FORMATTING, create a NEW RULE, USE A FORMULA, and we're going say =ISFORMULA of A1. Now, ISFORMULA’s brand new in Excel 2013. If you're still using Excel 2010 or Excel 2007, this is not going to work for you. Time to upgrade to Office 365. We'll choose a blue FONT for anything that has a formula like that. Alright. See, so, all the formula cells become highlighted. [=ISFORMULA(A1)]

But this tip from Craig Brody: Craig is a frequent contributor and he says, hey, there's a way that you can mark only the cells that are linked cells; in other words, are we getting the data from another worksheet or another workbook. So, we'll do CONDITIONAL FORMATTING, NEW RULE, USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and here's the formula we're going to use. =, well, actually, let's start from the inside out. We're going to say that we’re going to find an ! in the  formula -- FORMULATEXT is another new one in Excel 2013 -- of A1, and what this is going to do is it’s going to look through the formula, look for the !, and tell us where it is. If it's there, it's going to be a number, a number position, like, so, the exclamation points in the 7th or 14th or 15th position, but if it’s not there, it returns an error. [=FIND(“!”,FORMULATEXT(A1))]

So, now, we need to detect the error, and so we could say = is error and then put that whole thing in a NOT or, right here, what Craig's suggestion was, is ISNUMBER ( and then click out here and ) like that, and then we'll make those have a FONT COLOR of orange. Click OK and click OK. By the way, hey, I was about to press the RIGHT ARROW key to move through here and, when you do that, it starts inserting the cell references in. Just press F2 before we do that and then that READY changes to EDIT and you're allowed to move left and right. Click OK, alright, and so, now, all of the cells that have external links to another worksheet or another workbook are marked in orange. All the regular formula cells are marked in blue. Cool, cool trick there. [=ISNUMBER(FIND(“!”,FORMULATEXT(A1)))]

Hey, let's take Craig's trick and extend it a little bit. So, Craig's trick is counting on the fact that every link to an external worksheet has the !. What about links to external workbooks? Well, they always have a ]. Alright, so, let's select our data, CONTROL*, and we will do CONDITIONAL FORMATTING, NEW RULE, USE A FORMULA, and I'll paste in the last formula, alright? See, now, this is where I need to change that ! to a ]. So, right now, I'm in ENTER mode, and if I press the LEFT ARROW key, see, it starts giving me cell references, which is really, really annoying. So, instead, press the F2 key and, down here, ENTER will change to EDIT. Now, I'm able to use the left and right arrow keys as much as I want. Look for the ]. We'll change these to a white FONT with a red FILL, like that. Click OK, click OK, and there are external links to external workbooks will show up in red like that. [=ISNUMBER(FIND(“]”,FORMULATEXT(A1)))]

Alright. Well, hey, my new book Power Excel With MrExcel, the 2017 Edition, 617 Excel Mysteries Solved; click that i on the top right-hand corner to check out how you can buy this book.

I'll wrap up today. So, we're using CONDITIONAL FORMATTING to mark formula cells, just to mark any cell that has a formula. We use NEW RULE, USE A FORMULA, =ISFORMULA(A1), but, thanks to Craig Brody, mark formula cells that are links to other sheets, we use ISNUMBER, look for that !, FORMULATEXT of A1, and then I extended that to point to external workbooks, ISNUMBER, look for the ]. Alright, a few other tips. You can use AUTOSUM in several cells at once, or when you're editing a formula in the CONDITIONAL FORMATTING dialog box or in the name manager and you press the arrow keys to move, it is going to insert cell references. To prevent this, use F2 to change from ENTER mode to EDIT mode.

Alright, hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.