Todays question deals with conditional formatting. How can you have four rules? How can you have the color of column A be based on values in Column D? Episode 635 answers all.
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:
Well come back to the MrExcel netcast.
I'm Bill Jelen.
Conditional formatting, this is just a subject that I hardly ever bring up I don't do it my power Excel seminars up until excel 2007.
It was just so hard to use, But a question came in and so, let's address the question.
They said hey, we have a data set here. I want to color Column A, based on the values over in Column D, and to make life even more difficult.
I have four different values that can show up in D and Conditional Formatting can only handle three of them.
Well, if you have exactly 4 values here's what we're going to do.
Let's take the value for status number 4, they said status number 4 should be colored orange.
So, the first thing I'm gonna do is select all of column A and choose an orange color for that.
That's going to be my default, if none of the other conditions are true, then it's going to fall to the fourth one.
Then we're going to select all of those cells and go to Format, Conditional Formatting and normally, we say hey if the cell value is equal to one, but we don't want to do that.
We want to say if the formula is, and now I have to write a formula that basically works for cell A2.
So, I always wanna look over to D2.
So I say, =$D2 that freezes to D, but it allows it to move as I copy this down to all the rows equals one, then I want to use the color for one.
So, I choose format and choose red, will click OK, click Add, Formula Is, use the exact same formula =$D2=2, then they wanted yellow for two's click OK.
Finally, we'll click Add, Formula Is, =$D2=3, then we use green for that, click OK.
Now, this is interesting couple things.
First of all most people don't realize that Conditional Formatting can be based on a value in another cell that's because you have to change the cell value is drop down to Formula Is.
Here's stuck with only three conditions in Excel 2003 and before, but by basically turning all of those cells to the color of the fourth condition.
We can eke out a fourth condition, will click OK and you'll see that now, our cells are Conditionally Formatted.
If I would change one of these values, so here I'll change the status from 2 to 3 cell changes to green.
If I would change it to a 4, none of the conditions are true and the underlying color shows through which in this case is orange.
So, a way to basically trick Excel and to giving you 4 Conditional Formats and more importantly using the Formula Is, to point to another column instead of the column that we're formatting.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Conditional formatting, this is just a subject that I hardly ever bring up I don't do it my power Excel seminars up until excel 2007.
It was just so hard to use, But a question came in and so, let's address the question.
They said hey, we have a data set here. I want to color Column A, based on the values over in Column D, and to make life even more difficult.
I have four different values that can show up in D and Conditional Formatting can only handle three of them.
Well, if you have exactly 4 values here's what we're going to do.
Let's take the value for status number 4, they said status number 4 should be colored orange.
So, the first thing I'm gonna do is select all of column A and choose an orange color for that.
That's going to be my default, if none of the other conditions are true, then it's going to fall to the fourth one.
Then we're going to select all of those cells and go to Format, Conditional Formatting and normally, we say hey if the cell value is equal to one, but we don't want to do that.
We want to say if the formula is, and now I have to write a formula that basically works for cell A2.
So, I always wanna look over to D2.
So I say, =$D2 that freezes to D, but it allows it to move as I copy this down to all the rows equals one, then I want to use the color for one.
So, I choose format and choose red, will click OK, click Add, Formula Is, use the exact same formula =$D2=2, then they wanted yellow for two's click OK.
Finally, we'll click Add, Formula Is, =$D2=3, then we use green for that, click OK.
Now, this is interesting couple things.
First of all most people don't realize that Conditional Formatting can be based on a value in another cell that's because you have to change the cell value is drop down to Formula Is.
Here's stuck with only three conditions in Excel 2003 and before, but by basically turning all of those cells to the color of the fourth condition.
We can eke out a fourth condition, will click OK and you'll see that now, our cells are Conditionally Formatted.
If I would change one of these values, so here I'll change the status from 2 to 3 cell changes to green.
If I would change it to a 4, none of the conditions are true and the underlying color shows through which in this case is orange.
So, a way to basically trick Excel and to giving you 4 Conditional Formats and more importantly using the Formula Is, to point to another column instead of the column that we're formatting.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
