MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Mark Linked Cells


October 06, 2017 - by Bill Jelen

Mark Linked Cells

How to use conditional formatting to mark cells that link to another worksheet or workbook.

Watch Video

  • Use conditional formatting to mark formula cells
  • New Rule, Use a Formula, =ISFORMULA(A1)
  • Thanks to Craig Brody: mark formula cells that are links to other sheets
  • New Rule, Use a Formula, =ISNUMBER(FIND("!"),FormulaText(A1)))
  • Extending this: To mark cells pointing to external workbooks, use
  • this formula: =ISNUMBER(FIND("]",FORMULATEXT(A1)))
  • You can use AutoSum in Several Cells at Once
  • When you are editing a formula in conditional formatting box and press the arrow to move,
  • you will get cell references. To prevent this, use F2 to change Enter mode to Edit mode

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2154 mark linked formulas hey
  • welcome back to the MrExcel NetCast
  • I'm Bill Jelen and today a way to see
  • which cells are formula cells and what
  • should cells are linked formula cells
  • alright so here's what we're gonna do we
  • have some data here and I just put a lot
  • of one two three fours in here q1 and q2
  • those are some 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 all right so here's
  • we're going to do we're going up to a 1
  • select all of our data and note that a 1
  • 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 gonna say equal is
  • formula of a1 now is from this brand new
  • in excel 2013 if you're still using
  • Excel 2010 or Excel 2007 this is not
  • gonna work for you time to upgrade to
  • office 365 we'll choose a blue font for
  • anything that has a formula like that
  • all right see so all the form of the
  • cells become highlighted ha 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 equal well actually let's
  • start from the inside out
  • we're saying whether we're going to find
  • an exclamation point in the formula
  • formula text is another new and Excel
  • 2013 of a1 and what this is going to do
  • is going to look through the formula
  • look for the exclamation point 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 it was not
  • there it returns an error so now we need
  • to detect the error and so we could say
  • equal is error and then put it that
  • whole thing a naught or right here
  • what Craig's suggestion was is is number
  • open parentheses and then click out here
  • and write parentheses 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 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 exclamation point
  • what about links ticks aren't workbooks
  • well they always have a right square
  • bracket all right so let's select our
  • data control asterisk and we will do
  • conditional formatting new rule use a
  • formula and I'll paste in the last
  • formula all right see now this is where
  • I need to change that exclamation point
  • to a right square bracket so right now
  • I'm in enter mode and if I press the
  • left arrow key C 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
  • right square bracket 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 alright
  • well hey my new book power excel with
  • MrExcel - 2017 edition 600 C 17 Excel
  • mystery 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
  • equal is formula a1 but thanks to Craig
  • Brody mark formula cells there are links
  • to other sheets we use is number look
  • for that exclamation point formula text
  • of a1 and then I extended that to point
  • to external
  • workbooks is number look for the right
  • square bracket alright a few other tips
  • you can use autos autosum and several
  • cells at once or when you're editing a
  • formula in the conditional formatting
  • dialog box or in the the name manager
  • and you press the arrow keys to move is
  • going to insert cell references to
  • prevent this use f2 to change to enter
  • change from enter mode to edit mode or
  • hey I wanna think we're stopping by I'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2154.xlsm

Title Photo: Free-Photos / Pixabay