Learn Excel Conditional Format Mixed References

Learn Excel from MrExcel Podcast Episode 2105: Copying Conditional Format with Mixed References

Hey, welcome back to the MrExcel netcast.  This is 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.  Alright, and Anderson manages a yard - trailers arrive and trailers have to be unloaded within three days.  Alright, so this is - he starts out, you know, this was the day, these were the trailers that arrived and then he has conditional formatting set up that once the trailer is unloaded, it changes to 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, we want to highlight things as 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 on the 26th 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. 

Alright 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 unspeeded up version as an outtake at the end of the video.

Okay, so there we are.  Sped that up, you can watch at the end to see how that works.  Just doing a test here, CTRL;  will change to blue.  If this goes back to 6/26, 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, alright? 

Okay, so we're now at the point where we essentially have Anderson's problem.  I'm going to put in 6/25/2017, these will all turn red except for the ones that have been unloaded.  And now life is moving on, it’s the next day.  We got some trailers in on 6/26 and so Anderson copies this data, paste here, format Column AutoFit, and this will be Trailer 15.  Go 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 turning green because these formulas, these conditional formatting formulas right here, we’ll look at these.  They are hard-coded to use $A$1.  Oh, that's really bad. 

Alright, so let's try and improve things here.  The first thing I can do, I’m going to 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 $ sign.  In other words, it’s 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 $1.  We’ll edit this rule, click OK.  Alright now, with that one change when we copied 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 6/26 and life is going to be great on 6/27.  Alright, 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 6/28 but it's not turning green. 

Why is it not turning green?  It's not turning green because I still had to use the $ to get back up to the 1.  Alright, 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.

You know, so hey look, there's an argument made that this is good, we could stop right here because by using the A$1, we made it that way, life is easy on Day 1, copy over to Day 2, life is great.  Day 3 life is great.  It's only every 4th 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 1 to be 18.  Click OK, edit this rule and change that 1 to be 18.  Click OK, click OK.  Alright, so Day 4, 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 and those formulas 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 copy to here.  Alright, 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.  And let’s say let's get rid of those relative references, change every $A$1 to A1, 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, alright, well that's what we need to do.  We need to take those $ 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 that 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 to the 14*3, 42 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.

Al right, so what do we do?  Here's what I came up with and I want to hear if you have something better than that.  I said to Anderson, I said, “You know, look it's pretty simple.  All of these are looking at one calculation and that calculation is =TODAY- the date that’s to the left of me.”  And wouldn’t it be cool, if we could have that answer in a little helper column over here to the right.  And in fact, we don't have to use any $ 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 Anderson’s face, he doesn't want that extra stuff out there erased 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 cell we're in we're always going to look in the cell to the right, click OK, write at this one, click OK.  We want to hide this data over here so I'll go in and CTRL 1.  I'm going to use the three semicolons - ;;;,click OK.  I’m going to do the exact same thing there.  I’ll press F4, 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.  Alright, so here's our test now.  I will choose this CTRL C and then I'm actually going to paste there and paste there and paste here.  Alright, big tests go to 6/26, go to 6/27, change this to yellow, come to 6/28, it should change to green.  Beautiful! 

Alright, 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. 

Alright, topics in this episode.  We're looking for a way to be able to copy blocks of data containing conditional formatting that essentially is a mixed reference.  So, is there some way to remove the $ 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 applying to a whole block.  So my solution was to use helper cells that use relative references and then just use regular references in the conditional formatting. 

Other topics in this episode, if you have 4 conditional formatting rules just set 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 cell references in the conditional formatting dialogue and then setting up the conditional formatting dialogue. 

Well, I want to thank Anderson for being in my seminar and hopefully, you know, this helps him.  I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel. 

I'll take number 1.  When you're dealing with conditional formatting in that stupid dialogue 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 1 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.  Alright, 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. 

Alright, here is the key if this has been driving you crazy for years.  Well key number 1, just be perfect about where you click.  Right then 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 or the 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 going to have a date here.  I'm going to put in yesterday's date just or two days ago date so we   have something – this is going to work.  Alright, and we're going to assume that there's going to be 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 format.  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’re going to 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, click OK, click OK.  Alright, first rule done. 

Second rule is the thing- the date up in A1, more than one days old.  This one is going to be the tricky one and this is where we have to look at a specific cell.  So I’m going to have to use a formula and we'll say =TODAY, today will be today’s date minus that date up in A1.  If that is >2, >1 then we're going to format it in yellow. 

Alright, and I don't know if I need parentheses here, I'm going to just be safe and put the ( ) in and copy that whole thing so I can create the red color.  So copy and then we'll add a new rule and rule is going to be if it’s > or =2, we’ll format as red.

Alright, 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.  CTRL; will change to blue.  If this goes back to 6/26, 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.  Alright.