Excel Chart with highest value in different colour – Multi Color Bar Charts [How To]

Reports and charts goes together and the biggest reason to have charts in reports is to add visual treat that makes it super easy for everyone to decide instantly. To toss it even further we add, subtract, blend and mix colours to make it even more easier. Today we have the similar kind of assignment to work on.

Desire is to have an excel chart that shows the highest value in different colour so that the users don’t have to ogle the whole chart again and again to find the highest value.

This assignment is in short requires us to use multi-colour bar charts OR sometimes referred as conditional charts in Excel. Following is the image that shows what we are after in this tutorial:

Understanding the approach

Originally we will be given some sort of numbers most probably sales data for weeks or months or years with sales value for each. In that data one figure will be the highest.

The idea is to split the series in two distinct series where one series will house the highest value and the other will house the other values and then using these two separate series we will draw the chart which will automatically have our demand fulfilled. So lets get it done.

Multi-colored Bar Charts – Getting Highest Value in different color

Following is the example data that we are going to use:

Step 1: Copy the above data and paste it into excel worksheet. I assume that you have pasted the data in the first two columns of Excel starting from cell A1.

Step 2: Go to cell C1 and put the heading Max value and in cell D1 put the heading Other value

Step 3: In cell C2 put the following formula:

=IF(B2=MAX($B$2:$B$8),B2,””)

This formula checks if the value in cell B2 is equal to the max value out of the values in the range B2:B8 then fetch the value from cell B2 otherwise leave the cell empty which is mentioned using quotation marks.

Step 4: Double click the fill handle or drag the fill handle to cell C8. Now go to cell D2 and put the following formula:

=IF(C2=””,B2,””)

This formula is checking if cell C2 is empty which it will be if the value in cell B2 is not the highest value out of the lot then get the value from cell B2 otherwise leave the cell empty. Double click the fill handle to propagate the range with the same formula down to cell D8.

Step 5: Select the values in column C and D then press and hold CTRL key to select the month column too. Then go to Insert tab > Charts group > Column chart > More column charts > go for the option that shows both series plotted. Click OK

DONE!

Why Stacked column chart?

Point to remember is that you should use stacked column charts instead of clustered column charts as it is more easier and requires less steps to run through to get the desired outcome. But definitely you can get the same result if you plot one series on a different axis AND to have the same scales for both the axis.

Don’t forget to check out the following articles too:

1. Creating and Working with Dynamic Charts in Excel
2. Making Interactive Charts in Excel [How To]
3. Creating Timelines in Excel [How To]
4. Check out all of Excel charting articles/tutorials/resources