00:00If you want to highlight the highest and the lowest on your bar chart that looks something
00:03like this, these are the steps that you have to take. The first thing you have to do is to
00:08introduce a new column called min and max. The formula for min, you're going to basically run
00:13an if statement, open parenthesis, if C3 is equal to min, open parenthesis, and you're going to
00:21highlight the complete column on your total sales here, and press F4 once to make your column and
00:27your row fix. Close parenthesis. If this condition is true, you're basically going to display
00:34the cell C3 here like this. If not, you're going to display zero. Close parenthesis and
00:40hit enter. The next thing you're going to do is to write the formula for max. Same thing,
00:44if, open parenthesis, C3, this cell here, equal, if, open parenthesis, C3, equal to max, this
00:58time around, open parenthesis, the whole column here, and you're going to press F4 once to make
01:05the column and the row fix. Close parenthesis. If this condition is true, you're basically
01:10going to show the value of C3 on this cell here. If it's not true, then you're going to
01:16show zero, close parenthesis, and hit enter. Now you're going to select this two formula
01:20that you just created. Click on the fill handler and drag all the way down to the very last
01:25row so that the formula can be applied to every row like this. Now you can see the min. This
01:30is the minimum value among all of them, and this is the max for all of your data set. And
01:36now you're going to introduce the bar chart here. You're going to put your cursor in your
01:41data set table here and press control A to highlight your complete data set table here
01:46like this. Now you're going to go insert chart. Under columns, you're going to select this kind
01:53of chart. Now reposition it nicely somewhere here like this. Once that's done, you're going
01:59to right click on the blue bar chart, which is predominantly the total sales itself. And from
02:05the context menu, you're going to select format data series. A pop up will appear on the right
02:10hand side. Make sure you're selected to this series options here. And then on the series
02:15overlap, you're going to drag this cursor or the slider right across all the way to 100%.
02:21And what you'll notice is that the all the bar chart will overlap on top of each other,
02:25producing this kind of effect. Now to change the color of each bar chart. Let's start off
02:30with coloring the highest. I'm going to color my highest bar chart as red. Right click on
02:37the gray bar chart, which is going to highlight your max here like this. And then go data format
02:42data series. If this is not open, that's how you open it. Then you go fill and line here.
02:48And then on this field, if it's closed, expand it. Under the color, you're going to select red,
02:54or whichever color you want for your highest value. Next, you're going to select the lowest
02:59value. And then go back to your data series menu on the right hand side here. Make sure
03:04you clicked on this one here. And this time around, on the lowest, I'm going to color it
03:08as say green. And for the remaining of the blue chart or the blue bar chart, you're going
03:14to select that. And I'm going to make it a gray color. Next, we'll work on highlighting the
03:20cell on the total sales for highest and lowest value. You're going to select all the cells
03:25on that total sale column here. Going to go home, style, conditional formatting, manage
03:30rule. In here, you're going to select new rule. And then after that, you're going to select
03:34use a formula to determine which cell to format. And the formula you'll be using is equal C3,
03:40which is the very first cell on that particular column, equal to min, open parenthesis and highlight
03:48the data on your total sales column like this, and close parenthesis, which means if C3 is
03:54equal to minimum, going to go format, make sure you're on the fill tab, we're going to
03:58highlight it as green because you're looking for minimum. This is the same color as that.
04:03Now you're going to click OK once and OK one more time. And you should see one rule appearing
04:08here on your rule manager here. Next, going to add next rule for the red, going to go new rule,
04:15use the formula to determine which cell to format. And the formula you'll be using is
04:19equal C3, again the same cell, the very first cell on that row, equal to instead of min, we're
04:26going to use max now because you're looking for the maximum value. And in that function,
04:31you're going to select your whole total sales column like this and close parenthesis. If C3
04:38is the maximum, going to go format, fill, we're going to color that as red, same color as this
04:43guy here. And click OK, OK, and OK one more time. And you can see that your highest value
04:49is colored red, like this guy here, and the lowest value is colored green, like this guy
04:55right here. And you're done.
