Andy "Cyber" Walker once asked me what seemed like a simple question - he wanted to use conditional formatting to change records to green if the total of all records for that day exceeded $1000. While this sounds simple, it actually is incredibly complex to set up. Episode 347 walks you through the amazing conditional formatting option to allow this to happen.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel podcast, I'm Bill Jelen.
I have an insanely hard problem today, and I have to blame it on Andy Walker.
When I was writing the book “Learn Excel from MrExcel”, I would often be taking trips up to Toronto, and be writing the book in the green room, and call for help.
And Andy walked up one day with what seemed like a really simple question, but as I try to explain it, you'll see that it's not as simple as you might think.
Andy had a list of sales with date here and a sales amount, and he wanted to highlight any day where the sales for the day were greater than 1000.
Well, that's kind of hard to do, because we have to add up multiple records to figure out whether those records add up to more than 1000.
The function that we use is a relatively new function called SUMIF, and SUMIF says “Take a look at all of the dates in column A.” I'm going to hit F4 to make that an absolute reference, and see if it's equal to today's date in column A, I'm going to freeze just A there, and if it is, then add up the corresponding number in column C. When I copy that down, you'll see that for each day, we'll see the total sales for the day, so on this particular day there were $611, on the next day $1189.
Now, Andy wanted to see any time that it was greater than 1000, so I'll put a little test here that says >1000, now instead of the actual number, I'm going to get either FALSEs or TRUEs.
Now, this whole formula was really just a temporary step to make sure that it was going to work.
I'm going to go to the formula bar, copy that formula using Ctrl+C, and then select my dataset, and go to Format, Conditional Formatting.
Now, in Conditional Formatting, most people don't know that you can change the Cell Value Is drop-down to be Formula Is, and then in the formula bar use Ctrl+V to paste the formula that you used, will click OK here.
And all of the days where the sales were >1000 turn green, absolutely amazing use for Conditional Formatting.
Don't try and build that formula in the Conditional Formatting dialog though, build a temporary column, make sure it's working, and then you can come back and delete the column.
There you have it, how to build a rather complex conditional formatting formula, that looks at several rows within your dataset, to decide whether or not to color this row.
Hey, thanks for stopping by, we'll see you tomorrow for hopefully a tip that is much simpler than this one!
I have an insanely hard problem today, and I have to blame it on Andy Walker.
When I was writing the book “Learn Excel from MrExcel”, I would often be taking trips up to Toronto, and be writing the book in the green room, and call for help.
And Andy walked up one day with what seemed like a really simple question, but as I try to explain it, you'll see that it's not as simple as you might think.
Andy had a list of sales with date here and a sales amount, and he wanted to highlight any day where the sales for the day were greater than 1000.
Well, that's kind of hard to do, because we have to add up multiple records to figure out whether those records add up to more than 1000.
The function that we use is a relatively new function called SUMIF, and SUMIF says “Take a look at all of the dates in column A.” I'm going to hit F4 to make that an absolute reference, and see if it's equal to today's date in column A, I'm going to freeze just A there, and if it is, then add up the corresponding number in column C. When I copy that down, you'll see that for each day, we'll see the total sales for the day, so on this particular day there were $611, on the next day $1189.
Now, Andy wanted to see any time that it was greater than 1000, so I'll put a little test here that says >1000, now instead of the actual number, I'm going to get either FALSEs or TRUEs.
Now, this whole formula was really just a temporary step to make sure that it was going to work.
I'm going to go to the formula bar, copy that formula using Ctrl+C, and then select my dataset, and go to Format, Conditional Formatting.
Now, in Conditional Formatting, most people don't know that you can change the Cell Value Is drop-down to be Formula Is, and then in the formula bar use Ctrl+V to paste the formula that you used, will click OK here.
And all of the days where the sales were >1000 turn green, absolutely amazing use for Conditional Formatting.
Don't try and build that formula in the Conditional Formatting dialog though, build a temporary column, make sure it's working, and then you can come back and delete the column.
There you have it, how to build a rather complex conditional formatting formula, that looks at several rows within your dataset, to decide whether or not to color this row.
Hey, thanks for stopping by, we'll see you tomorrow for hopefully a tip that is much simpler than this one!
