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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.