MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Learn Excel Conditional Format Mixed References


June 29, 2017 - by Bill Jelen

Learn Excel Conditional Format Mixed References

Setting up a conditional formatting formula that uses a mixed reference. Most conditional formatting formulas require an absolute reference. But this spreadsheet to track trucks in a yard requires

Watch Video

  • Anderson is looking for a way to be able to copy blocks of data containing mixed conditional formatting
  • Is there a way to remove the dollar signs once conditional formatting is set up?
  • No - not without introducing dozens of new rules
  • My solution: helper cells that use relative references to replace the mixed reference in conditional formatting
  • Other techniques in this episode:
  • If you have four conditional formatting rules, set up the first 3 and then make the fourth rule be the default color
  • Outtake #1: Press F2 to stop Excel from inserting cell references in the conditional formatting dialog
  • Outtake #2: setting up conditional formatting

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2105 copying conditional format with
  • mixed references hey welcome back to the
  • districts on that cast this was going to
  • be a complicated one today I was doing a
  • seminar yesterday and one of the people
  • in the seminar Anderson had a
  • interesting spreadsheet with a problem
  • right and Anderson manages a yard
  • trailers arrive and trailers have to be
  • unloaded within three days all right so
  • this is the this is he starts out you
  • know this was the day these were the
  • trailers that Iraq and then he has
  • conditional formatting set up that once
  • the trailer is unloaded I changed his
  • blue once something is blue everything
  • is great but then he wants to color code
  • things if something arrived today or
  • yesterday
  • it gets color coded as green so today is
  • June 29th 2017 so this was arrived
  • yesterday and everything that's not
  • unloaded is green but when it's more
  • than one day old and one of highlight
  • things is yellow and when it's more than
  • two days old those are the problems we
  • want to highlight things as red and it's
  • not that you know this is one worksheet
  • to manage the whole yard right it's not
  • that there's a sheet for things that
  • arrived in the 26 and another for the
  • 27th and another for the 28th and you
  • know the difficulty is as a new day
  • comes along they either copy the
  • previous day over to here or down to
  • here all right now the point of this
  • video is not about how to set up this
  • conditional formatting so I'm going to
  • speed through this but if you're
  • interested in how to set up this
  • conditional formatting I will put the
  • unspeakable burden as an outtake at the
  • end of the video
  • you
  • okay so there we are sped that up you
  • can watch at the end to see how that
  • works just doing a test here control
  • semicolon will change the blue
  • if this goes back to 626 it will change
  • to red and if it's today it's not
  • working that's right because here's what
  • I'm going to do my fourth rule green
  • arrived today or yesterday I'm just
  • going to use that as the default if none
  • of these other three rules are true then
  • it's going to be green that it will give
  • me one less rule that I have to deal
  • with here all right okay so we're now at
  • the point where we essentially have
  • Anderson's problem I'm going to put in
  • six twenty five seventeen these will all
  • turn red except that once they've been
  • unloaded and now life is moving on is
  • the next day we get some trailers in on
  • six twenty six and so Anderson copies
  • this data pace here for McCollum autofit
  • and this will be trailer 15 don't click
  • to copy that down and increment get rid
  • of the ones that arrived and so this one
  • arrived today so these should all turn
  • green but they're not turning green why
  • aren't they turning green they aren't
  • here in green because these formulas
  • these conditional formatting formulas
  • right here when we look at these they
  • are hard-coded to use dollar sign a
  • dollar sign one oh that's really bad all
  • right so let's try an and and improve
  • things here the first thing I can do I
  • might get rid of all those and come back
  • to this original data set and be a
  • little bit smarter on the second pass
  • and say we don't really need to lock it
  • down to column a I'll get rid of that
  • dollar sign in other words is always
  • going to be the column to the left of us
  • so that's going to be a mixed reference
  • but we do always have to point to the
  • dollar sign one will edit this rule
  • click okay alright now with that one
  • change when we copy to the right and put
  • a new data in like today's date it works
  • okay so this is great life is going to
  • be great on 626 and life is going to be
  • great on 627 all right working great but
  • now we run into the problem where we run
  • out of space on the page and so what
  • Anderson has been doing is goes down
  • essentially starts a new row and pastes
  • and this would be 628 but it's not
  • turning green why is it not turning
  • green it's not turning green because I
  • had still had to use the dollar sign to
  • get back up to the one all right and so
  • now here's the conundrum here's the
  • problem what do you do now and I'm
  • serious
  • what do you do now I want to hear in the
  • youtube comments what you would do now
  • so hey look there's an argument maybe
  • that this is good we could stop right
  • here because by using the a dollar sign
  • one we made it so that way life is easy
  • on day one copy over to day two life is
  • great day three life is great it's only
  • every fourth day when we copy down here
  • that Anderson would have to go in and
  • set up conditional formatting edit this
  • one edit the rule change that one to be
  • 18 click OK
  • edit this rule and change that one to be
  • 18 click OK click OK
  • alright so day for that little
  • adjustment copy over for day 5 copy over
  • for day 6 and then copy over for day 7
  • do those steps again but hey let's face
  • it this worksheet was set up six months
  • ago with these conditional formatting
  • rules and they just need to work we
  • don't need to go in and do conditional
  • formatting again and again and again my
  • first reaction was I'm going to pretend
  • like this is a spreadsheet where I have
  • some formulas here
  • formulism were built with absolute
  • references but I need those formulas to
  • be able to be copied over or down and be
  • relative within the copy both when I
  • copy to here and when I copied it here
  • all right and in order to get that to
  • work I'm going to use absolute
  • references when I set things up but then
  • I'm going to use Find and Replace ctrl H
  • I'm going to say let's get rid of those
  • relative references change every dollar
  • sign a dollar sign 1 to a 1 replace all
  • click close and now this block all of
  • these formulas are different all the way
  • down copy paste and paste and it will
  • work it will be relative so I said all
  • right well that's what we need to do we
  • need to take those dollar signs out of
  • the formula and so I was going to write
  • a macro that would allow me to edit each
  • one of these conditional formatting
  • rules alright and before I wrote the a
  • macro I was going to record the macro of
  • changing one conditional formatting rule
  • but it's not that there are 14
  • conditional formatting rules here it's
  • not even though there are 14 times 342
  • conditional formatting rules here
  • there's only 3 conditional formatting
  • rules here and we're applying those 3
  • conditional formatting rules to a range
  • of cells so if I would change this the
  • first thing I would have to do is take
  • these 3 conditional formatting rules and
  • make them be 42 conditional formatting
  • rules and then I'm starting to cringe
  • because as Anderson copies from here to
  • here he's going to introduce 42 new
  • rules and then 42 new rules and over the
  • course of one sheet of paper with
  • probably 15 days he's going to introduce
  • over 600 rules 600 different formats and
  • that's just going to be horrible you're
  • eventually going to hit the too many
  • formatting rule thing not to mention
  • that it's going to be tough to set up
  • even if we have a macro to set it up
  • it's going to be tough to set up all
  • right so what do we do here's what I
  • came up with and I want to hear if you
  • have something better than
  • I said that I said the Anderson I said
  • you know look it's pretty simple all of
  • these are looking at one calculation and
  • that calculation is equal today - the
  • date that's to the left of me and would
  • it be cool if we could have that answer
  • and a little helper column over here to
  • the right and in fact we don't have to
  • use any dollar signs at all we'll just
  • put all of those cells all the way down
  • with that simple little formula I can
  • see the look at interest in space he
  • doesn't want that extra stuff out there
  • right but that's okay we can hide hide
  • that later so we come back into these
  • cells and go into our conditional
  • formatting that whole today - a1 is
  • simply going to be pointing to c3 and
  • that's going to be a relative reference
  • so in other words whatever so we're in
  • we're always going to look in the cell
  • to the right click ok why did this one
  • click okay we want to hide this data
  • over here so I'll go in and control one
  • I'm going to use the three semicolons
  • semicolon semicolon semicolon click OK I
  • want to do the exact same thing there
  • I'll press f4 to repeat the last action
  • now the weird thing here is I have to
  • convince myself that this little part
  • this blank column is part of the whole
  • thing so I want to just add a light grey
  • there to remind myself that when I copy
  • and paste I'm going to have to include
  • the grey all right so here's our test
  • now I will choose this ctrl C and then
  • I'm actually going to paste it there and
  • paste there and paste here alright big
  • tests go to 626 627 change this to
  • yellow come to 628 should change to
  • green beautiful all right so now it's
  • working we have essentially replaced
  • that conditional formatting mixed
  • reference with a relative reference and
  • we should be relatively relatively good
  • to go all right
  • topics in this episode we're looking for
  • way to be able to copy blocks of data
  • containing conditional formatting that
  • essentially is a mixed reference so
  • there's some way to remove the dollar
  • signs once the conditional formatting is
  • set up well maybe with a macro but you'd
  • be introducing dozens of new rules
  • instead of just one formula playing to a
  • whole block so my solution was to use
  • helper cells they use relative
  • references and then just use regular -
  • regular references in the conditional
  • formatting other topics in this episode
  • if you have for conditional formatting
  • rules just so the first three and make
  • the fourth rule be the default color the
  • outtake coming up next is press f2 to
  • stop Excel from inserting two cell
  • references in the conditional formatting
  • dialog and then setting up the
  • conditional formatting dialog well I
  • want to make Anderson for being in my
  • seminar and hopefully you know this
  • helps him I want a cue for stopping by
  • we'll see you next time for another net
  • cast from MrExcel I'll take number one
  • when you're dealing with conditional
  • formatting and that stupid dialog box
  • and
  • you need to edit something that's
  • already in there you better be darn good
  • at clicking in the right spot like if I
  • wanted to change that one to be 18 and I
  • clicked right there and then press the
  • right arrow key then I have to swear
  • because they're inserting cell
  • references instead right and so many
  • times when I was recording this episode
  • I clicked in the wrong spot and hit the
  • right arrow key or the left arrow key or
  • shift arrow key and how to back out of
  • it all right here is the key if this has
  • been driving you crazy
  • for years well key number one just be
  • perfect about where you clicked or
  • anything you don't have any problem at
  • all life is great but
  • that's not realistic here's the whole
  • trick when you are in this dialogue box
  • down here in the lower left-hand corner
  • it says that we are in enter mode and
  • when you're in enter mode using left or
  • right arrow keys is going to insert cell
  • references for you like that right
  • really really annoying but what you want
  • to do is you want to press the f2 key
  • and that changes us from enter mode to
  • edit mode bingo now we can do whatever
  • we want we can use the left arrow key
  • that right arrow key and we're not
  • inserting cells like that alright next
  • up is I'll take number 2 where I built
  • this original conditional formatting I
  • showed that in fast motion before here
  • just in case you're interested is the
  • slow motion so we're gonna have a date
  • here I'm gonna put in yesterday's date
  • just there are two days ago date so we
  • have something - it's going to work
  • alright and we're going to assume that
  • there's maybe some number of space for
  • trailers to come in in this case I'll go
  • down to trailer 14 and then here we're
  • going to build conditional formatting
  • and there are four rules that we want to
  • do and the first one the easy one is if
  • there's a date here then we turn this
  • thing blue so alt o D to get into
  • conditional formatting I'm going to
  • create a new rule and that new rule is
  • going to be the easy one format only
  • cells that contain a value that is
  • greater than 0 then we're going to
  • format this using a blue color like that
  • okay click okay all right first rule
  • done second rule is the thing the date
  • up in a won more than one days old on
  • this one is going to be the tricky one
  • and this is where we have to look at a
  • specific cell so I don't have to use a
  • formula and we're say equal today today
  • will be today's date - that date up in
  • a1 if that is greater than 2 greater
  • than 1 then we're going to format it in
  • yellow
  • all right and I don't need no if I need
  • parentheses here I'm going to just be
  • safe and put the parentheses in and copy
  • that whole thing so I can create the red
  • color so copy and then we'll add a new
  • rule and that rule is going to be if
  • it's greater than or equal to 2 well
  • format as red
  • all right now we have to be careful here
  • the first thing I want to do is I want
  • to check to see if the thing is filled
  • in if the thing is filled in we get the
  • blue we stop if true then the next thing
  • to do we have to check for the red
  • before we check for the yellow because
  • this formula for yellow is also going to
  • be true on the days when it should be
  • red okay so there we are sped that up
  • you can watch at the end to see how that
  • works just doing a test here control
  • semicolon will change the blue if this
  • goes back to 626 it will change to red
  • and if it's today it's not working
  • that's right because here's what I'm
  • going to do my fourth rule green arrive
  • today or yesterday I'm just going to use
  • that as the default if none of these
  • other three rules are true then it's
  • going to be green that it will give me
  • one less rule that I have to deal with
  • here all right

Download File

Download the sample file here: Podcast2105.xlsx

Title Photo: Pexels / Pixabay


Bill Jelen is the author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.