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 *

Creating your Own Outlook Calendar Files
Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Format or Remove Borders from a Slicer or Timeline in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Using RANK(), and ROW_NUMBER to Rank a field without ‘skipping’ numbers in SQL Server & Teradata
Add Labels to Outliers in Excel Scatter Charts

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 2023 - Theme by ThemeinProgress