# Mark Linked Cells

October 06, 2017 - by Bill Jelen

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*