Showcase high and low values in Excel charts – Part 2


In the first part of this mini-series, minimum and maximum values of a chart were showcased by simply sorting the data values in ascending or descending order


There may be situations, where the order of the data points may not be changed, for example because they are in a chronological order, or because the data points are sorted alphabetically, so they can be seen in relation to other charts with the same alphabetical order.

With the data values in alphabetical or chronological order, it can be hard to identify the highest and/or lowest data point, so a few visual pointers may be called for.

So, here’s an alternative to sorting the values:

Conditional colouring of the columns

To highlight the minimum and maximum values of the data set, we can colour the minimum values red and the maximum values green. This solution is also dynamic, of course, so that any changes in the base data set will be reflected in the chart without user intervention.

We need two additional data series in helper columns, one for the minimum and one for the maximum data value. These will also show if a max or min value is present more than once.

The minimum value of the data set can be obtained with a formula like


The maximum data value can be calculated with


These two columns are added to the base data table to provide a scenario like this (I’ve applied conditional formatting to highlight the minimum and maximum values in the source table):

Data table for conditional highlights

Plot all three data columns as a clustered column chart, with the town names as the X axis labels.  That should produce a chart like this:

Clustered column chart with default settings

Double-click any of the columns to format the data series. Set the series to 100% overlap and decrease the gap width to 50%.

Format the data series

Delete the legend and the grid lines to arrive at this:

Chart with conditionally formatted collumns

The maximum data points are green, the minimum data points are red — and that even applies to duplicate values.

View the Excel file in Excel Web Apps or download a sample file.

In the next post I will show how to use an image to highlight the minimum and maximum value.

cheers, teylyn

Part 1: Sort the data

Part 3: Stacked series with an image fill