Label Excel Chart Min and Max

Highlighting the minimum and maximum in an Excel chart can help focus your readers’ attention.

We can manually select points or columns and change the color, and that’s fine for static charts, but if your chart gets updated then you’ll want to automate the process.

The approach is slightly different for line charts and column charts, so I’ll cover both here.

Download the workbook

Watch the Video

Label Excel Chart Min and Max - Line Charts

It all begins with the chart source data (see image below). Column C contains the data that plots the line, column D contains the maximum marker and column E contains the minimum marker:

We use formulas in columns D and E to automatically find the maximum and minimum values, plus using formulas for this allows us to update the data and not have to manually alter the chart.

Max formula: =IF([@Sales]=MAX([Sales]),[@Sales],NA())

In English the Max formula reads; if the sales amount = the maximum sales amount in the Sales column then return the sales amount, otherwise return the #N/A error.

Min formula: =IF([@Sales]=MIN([Sales]),[@Sales],NA())

In English the Min formula reads; if the sales amount = the minimum sales amount in the Sales column then return the sales amount, otherwise return the #N/A error.

Note: The chart source data is formatted in an Excel table and as a result the formulas above use Structured References instead of regular cell references that you might be familiar with.

Tip: In line charts we use #N/A to hide values we don’t require labels for, but in the column chart we use a different technique. More on that in a moment.

Excel Line Chart with Min & Max Markers

Step 1: Insert the chart; select the data in cells B5:E29 > insert a line chart with markers

Step 2: Fix the horizontal axis; right-click the chart > Select Data > Edit the Horizontal (Category) Axis Labels and change the range to reference cells A6:B29.

Step 3: Format the markers; click on the max marker in the chart > right-click > format data point > Marker Options > apply settings as per image below:

Tip: It’s a good idea to set the line for the marker series to ‘no line’ just in case you have ties in your max/min values.

Rinse and repeat for the Min marker choosing a different color.

Step 4: Add data labels to markers; right-click the marker > add data label > format the label above the line for the max marker and below the line for the min marker.

Bonus points; match the label font color to the marker color.

Bonus tip: If you have multiple min or max values you’ll also want to set those series’ lines to ‘no fill’, otherwise you’ll have a line joining the markers.

Thanks to fellow MVP Jon Peltier for the hollow circle marker trick. Normally I just use a solid dot marker, but the circle is nice for a change.

Label Excel Chart Min and Max - Column Charts

Highlighting minimum and maximum values in a column chart is slightly different, but it also begins with columns for max and min the source data, as shown below:

Again, we use formulas to find the minimum and maximum values in columns D and E:

Max formula: =IF([@Sales]=MAX([Sales]),[@Sales],0)

In English the Max formula reads; if the sales amount = the maximum sales amount in the Sales column then return the sales amount, otherwise return zero.

Min formula: =IF([@Sales]=MIN([Sales]),[@Sales],0)

In English the Min formula reads; if the sales amount = the minimum sales amount in the Sales column then return the sales amount, otherwise return zero.

For column charts we use zero to hide values we don’t want plotted in the chart. A column with a zero height simply doesn’t display.

However, to avoid a load of zeros displaying in our labels we need a custom number format that hides the zero values.

I like to apply the format to my source data as this will be automatically picked up by my chart.

The format is 0;-0; and you can see it below in the Format Cells dialog box (tip, don’t forget the last semi-colon in the format, as that’s what hides the zero):

Note: My numbers are small so my custom number format doesn’t have a comma separator for thousands, However if your values are large, or you want $ or ₤ symbols included in your format then refer here for more on Custom Number Formats.

Ok, now that my source data is ready I can insert my chart. BTW, this also applies to bar charts.

Excel Column Chart with Min & Max Markers

Step 1: Insert the chart; select the data in cells B40:E64 > insert a 2-D column chart

Step 2: Fix the horizontal axis; right-click the chart > Select Data > Edit the Horizontal (Category) Axis Labels and change the range to reference cells A41:B64.

Step 3: Overlap columns; right-click any column > format data series > Series overlap 100%, Gap width 60% (or whatever you prefer).

Step 4: Colour code min/max; left click the max column > format the fill colour. Repeat for the min column.

Step 5: Add labels; right-click the max column > add data label. Repeat for min column.

Bonus points; match the label font color to the column colors.

Bonus tip: Make the label font color one shade darker than the column color to help it stand out.

Label Excel Chart Min and Max - Summary

I want to recap the key differences in hiding points in line charts vs column/bar charts because this is something I regularly get asked about.

Line charts require the #N/A error to hide points. This prevents the line dipping down to zero and #N/A errors are simply not displayed in line chart labels.

Column or Bar charts require zero or blanks to hide the column/bar. This results in no bar being drawn in the chart, but if you add labels you’ll see zero values along the horizontal axis. To hide the zeros in labels we use a custom number format that tells Excel not to display the zero; 0;-0;

If you try to use #N/A in column/bar charts you won’t be able to hide them in the labels.

Related Lessons

Excel Tables & Structured References

Custom Number formats