Highlight the Largest Value on an Excel Chart

A bit of a follow up from the earlier post on highlighting negative values. This should work with all versions on Excel – though the options may be in slightly different places.

This time, I want to highlight the measure with the single highest value. I’m going to use the same chart as before.

So, I want to highlight the temperature in August as being the highest temperature by giving it a different colour.

To do this, we need to firstly add a second series to our data to pick out the month we want to highlight.

I do this by adding something like this:

=IF(G5=MAX($G$5:$G$16),G5,"")

And copying down the full range

What you will end up with is a column with just one entry (or more, if you have several the same as the max)

Add the second series to the chart (or create your chart from scratch using both series). You will get something like this, where some months have two bars.

Use this opportunity to change the standard colour for the extra series…

(Right click on the series, Format Data Series->Fill->Solid Fill->Fill Color)

Now the clever bit…

Right Click on the Series & select ‘Format Data Series’

Select the ‘Series Options’ menu, and drag the ‘Series Overlap’ slider to 100%

And that’s pretty much it. Hit Close, and your chart should look something like this…

You may want to go back to the previous screen and adjust the Gap Width between the bars, but it’s optional

This same principle can be used not just for the Max, but the Min, select categories – anything – whatever you set your formula to show in that second series.

Have fun!

David

1 comment