logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Highlight the Largest Value on an Excel Chart

June 27, 2013 1 comment Article Excel, Intermediate

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.

062713_1437_HighlightNe5.png

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

Related

Tags: Charts, Conditional, Excel, Formatting

1 comment

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Format or Remove Borders from a Slicer or Timeline in Excel
Populate a table by looping through items in a list / another table
Showing Data Bars as a 'Proportion to Total' in Excel
Creating Virtual Tables in Power BI Using DAX
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Showing a Picture Based on the Value of another Cell in Excel

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2022 - Theme by ThemeinProgress