Showcase high and low values in Excel charts – Part 1

Introduction

When charting large data sets, we sometimes may want to draw attention to the maximum or the minimum value of a dataset, or both.

Let us assume a data set like this in a sheet called “Base data”:

For ease of use in follow-on formulae let’s create two range names, one for the labels and one for the values:

Label =’Base data’!$A$2:$A$17
Value =’Base data’!$B$2:$B$17

A simple column chart created from that data set will look like this:

Chart with unordered values
Unordered

At a glance, it can be difficult to determine the minimum or maximum values in that chart. How can we make it easy for the reader to identify the minimum and/or maximum values? Grid lines spring to mind, but even with grid lines it may still be difficult.

In the following I’ll show a few different approaches.

Ranking by data value

The most obvious way to showcase minimum and/or maximum values in a chart is to sort the data so that the chart displays the data in ascending or descending order, like in the two following charts:

Chart with values in ascending order
Ascending
Chart with values in descending order
Descending

To sort the data, we can use a helper column to determine the rank of each value and then use another helper column to order the values based on their rank. This sorted data will then be used as the chart source.
Ranking the values can be achieved with the following formula, which includes a tie breaker to assign individual ranks to cells with exactly the same value:

=RANK(B2,Value)+COUNTIF(B$2:B2,B2)-1

Applied to the sample data, we can see the following ranking (I’ve used conditional formatting to highlight the lowest values in red and the highest values in green:

In the attached example file, you will find a sheet called “Ranked descending” with this formula in place. For use in follow-on formulas, I’ve created a named range for the ranking results, called Rank, which refers to

='Ranked descending'!$C$2:$C$17

To sort the labels and the data in ascending order (small to large) we can use these two formulae in helper columns:

Label =INDEX(Label,MATCH(LARGE(Rank,ROW(A1)),Rank,0))
Value =INDEX(Value,MATCH(LARGE(Rank,ROW(A1)),Rank,0))

For descending order (large to small), the formulae for label and value are

Label =INDEX(Label,MATCH(SMALL(Rank,ROW(A1)),Rank,0))
Value =INDEX(Value,MATCH(SMALL(Rank,ROW(A1)),Rank,0))

In a single data table, the results will look like this:

Now it’s very easy to plot a simple column chart based on the data sorted ascending or descending. Note that the solution is dynamic, i.e. when the original data value changes, the sort order will adjust automatically, as will the chart.

But…

There may be situations, though, 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.

Some alternative charting ideas will be discussed in the following parts.

View the file in Excel Web Apps, or download a copy

cheers, teylyn

Part 2:  Conditional colour formatting a chart