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 / 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.