In a spreadsheet that keeps track of expenses, we want to mark the cell in one color if the expenses are within 25% of the budget and in another color if they are within 10% of the budget. In Episode #1445, Bill shows us how to do this.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1445 – Within 25% of Budget?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
A call came in late last.
And hey, if you're in trouble late at night, and you need a quick answer, don't write into a podcast.
That's not a fast way to get things done.
Go to MrExcel.com over to the left hand side, click Message Board.
It is free to register.
And then you go into Excel questions and say, new thread and post your question there.
There's folks there all the time.
You might get an answer from Australia or New Zealand, if you're calling in the middle of the night, but that will be a fast way to get your answer.
But here we go.
So the question is, we have a list of expenses here.
Here's the total expenses.
We want to highlight this cell in one color if it's within 25% of the budget, another color if it is within 10% of the budget.
So what we're going to do, on the Home tab.
It might have this conditional formatting.
I have to go under Styles because my screen is smaller.
And rather than highlight cells rules where none of these are actually going to work, I'm going to go down to new rule, and say, I want to use a formula to determine which cells to format.
I want to move this aside so I can take a look.
I want to say, =B4>.75*B2, equal b4, that's where my total is, is greater than 0.75 times the budget in B2.
What format do we want to use?
Within 25%, let's turn it yellow.
Click OK, click OK.
All right, that's working.
And we're going to do that again.
So come back up here to Styles, conditional formatting, new rule, use a formula, and again =B4>.9*B2, format in red.
Click OK, click OK.
Now let's do a little test here.
So we'll just add $2,000 and that should get us up into the red zone, right.
So it's working.
It's important that you kind of set those up backwards when we go in and look at this.
The first rule is, evaluate the second.
The second rule is, evaluated the first.
So it's hitting this 0.9 first.
It was hitting the 0.75 first while even things that were 0.9, two of the total would be caught in and would be turning yellow.
So it just works out.
If it was in the wrong order, you could click this and say, move up or move down.
Well thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1445 – Within 25% of Budget?
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
A call came in late last.
And hey, if you're in trouble late at night, and you need a quick answer, don't write into a podcast.
That's not a fast way to get things done.
Go to MrExcel.com over to the left hand side, click Message Board.
It is free to register.
And then you go into Excel questions and say, new thread and post your question there.
There's folks there all the time.
You might get an answer from Australia or New Zealand, if you're calling in the middle of the night, but that will be a fast way to get your answer.
But here we go.
So the question is, we have a list of expenses here.
Here's the total expenses.
We want to highlight this cell in one color if it's within 25% of the budget, another color if it is within 10% of the budget.
So what we're going to do, on the Home tab.
It might have this conditional formatting.
I have to go under Styles because my screen is smaller.
And rather than highlight cells rules where none of these are actually going to work, I'm going to go down to new rule, and say, I want to use a formula to determine which cells to format.
I want to move this aside so I can take a look.
I want to say, =B4>.75*B2, equal b4, that's where my total is, is greater than 0.75 times the budget in B2.
What format do we want to use?
Within 25%, let's turn it yellow.
Click OK, click OK.
All right, that's working.
And we're going to do that again.
So come back up here to Styles, conditional formatting, new rule, use a formula, and again =B4>.9*B2, format in red.
Click OK, click OK.
Now let's do a little test here.
So we'll just add $2,000 and that should get us up into the red zone, right.
So it's working.
It's important that you kind of set those up backwards when we go in and look at this.
The first rule is, evaluate the second.
The second rule is, evaluated the first.
So it's hitting this 0.9 first.
It was hitting the 0.75 first while even things that were 0.9, two of the total would be caught in and would be turning yellow.
So it just works out.
If it was in the wrong order, you could click this and say, move up or move down.
Well thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
