How can you highlight the lowest value in each row? Episode 1206 shows you the answer! Check out 376 other tips - Learn Excel 97-2007 from MrExcel.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Boy, this good question sent in- Actually it was at a seminar, someone had a series of items and spot prices for 4 different weeks, or 5 weeks.
They wanted to highlight the price in each row that was the lowest in that row.
OK now, when you do this, you need to pay attention to which cell is the active cell, so currently the cell I2 is the active cell.
We're going to go into Conditional Formatting and create a New Rule, and that New Rule is going to be based on a formula.
So we're going to write a formula here that works for I2, but use the relative and absolute references, so that way it will work for all the cells in the selection.
So we're going to say- let's just take a look here, we want to look at the prices from I to M, so we say =I2, and that's going to be left as relative, =MIN(I2:M2).
Alright, now we want to be careful here, I have to add some $ in, if I would use the left and right arrow keys here, it's going to start to enter cell addresses, so I want to press the F2 key.
The F2 key will allow me to actually edit that without getting in the cell addresses in there, and I want to lock down that, we're looking from I, so a $ before the I, and then a $ before the M. Alright, let's just format that.
We’ll choose a fill color here, and I'll just go with yellow, click OK, and click OK, and that will apply a format to all of the cells, now the lowest value and each row gets formatted.
Let’s just try a little, put a number 1 in here, and that now becomes formatted and the other ones don’t.
The whole trick to this, the formula version again is, you have to pay attention to what the active cell was.
So when I set this up, I2 was the active cell, and I write a formula that works for I2, but realize that that formula is going to be applied to all of the values in the range.
And so that's why I put the $I and $M, that way we're looking at other items in other columns of that selected range, it'll still always look for the minimum from I-M.
So a very cool trick there, next time you have to highlight the lowest value in each row or each column, you can use that formula version of conditional formatting.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Boy, this good question sent in- Actually it was at a seminar, someone had a series of items and spot prices for 4 different weeks, or 5 weeks.
They wanted to highlight the price in each row that was the lowest in that row.
OK now, when you do this, you need to pay attention to which cell is the active cell, so currently the cell I2 is the active cell.
We're going to go into Conditional Formatting and create a New Rule, and that New Rule is going to be based on a formula.
So we're going to write a formula here that works for I2, but use the relative and absolute references, so that way it will work for all the cells in the selection.
So we're going to say- let's just take a look here, we want to look at the prices from I to M, so we say =I2, and that's going to be left as relative, =MIN(I2:M2).
Alright, now we want to be careful here, I have to add some $ in, if I would use the left and right arrow keys here, it's going to start to enter cell addresses, so I want to press the F2 key.
The F2 key will allow me to actually edit that without getting in the cell addresses in there, and I want to lock down that, we're looking from I, so a $ before the I, and then a $ before the M. Alright, let's just format that.
We’ll choose a fill color here, and I'll just go with yellow, click OK, and click OK, and that will apply a format to all of the cells, now the lowest value and each row gets formatted.
Let’s just try a little, put a number 1 in here, and that now becomes formatted and the other ones don’t.
The whole trick to this, the formula version again is, you have to pay attention to what the active cell was.
So when I set this up, I2 was the active cell, and I write a formula that works for I2, but realize that that formula is going to be applied to all of the values in the range.
And so that's why I put the $I and $M, that way we're looking at other items in other columns of that selected range, it'll still always look for the minimum from I-M.
So a very cool trick there, next time you have to highlight the lowest value in each row or each column, you can use that formula version of conditional formatting.
Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
