Quick, clean, and to the point

This video comes from our video training for Excel Charts.

Dynamic min and max with data labels

The data labels in Excel charts are flexible and powerful. While you can easily use them to display values from source data, you can also display values from other cells. The video below shows how to leverage this feature to dynamically display the minimum and maximum value in the data, using formulas.
Video Transcript 

In this video, we'll look at how to highlight high and low values in an Excel chart using data labels.

This worksheet contains daily sales numbers for a small online store.

I'll plot the data in a basic column chart.

Let's say we want to highlight the highest and lowest values by showing these values directly over the bars.

This may seem tricky, but we can build a very straightforward solution using only data labels with a simple formula.

I'll work through the solution step-by step.

First, I'll enable data labels.

Now we can clearly see the sales values for each day.

However, we only want to show the highest and lowest values.

An easy way to handle this is to use the "value from cells" option for data labels.

You can find this setting under Label options in the format task pane.

To show you how this works, I'll first add a column next the data, and manually flag the minimum and maximum values.

Now, back in the label options area, I'll uncheck Value, and check "Value from cells". Then I need to select the new column.

When I click OK, the existing data labels are replaced by the labels I typed by hand.

So that's the concept. Now we need to make the solution dynamic, and pull in the actual values.

I'll start by adding the max value.

To make the formula easy to read and enter, I'll name the sales numbers "amounts".

The formula I need is:

=IF(C5=MAX(amounts), C5,"")

When I copy this formula down the column, only the maximum value is returned.

And back in the chart, we now have a data label that shows maximum value.

Now I need to extend the formula to handle the minimum value.

I could add another nested IF, but using the OR function is a bit more compact.

=IF(OR(C5=MAX(amounts),C5=MIN(amounts)), C5,"")

The formula reads if C5 is the max of amounts, or the min of amounts, return C5.

Otherwise, return an empty string.

Now the formula returns both min and max values, and the chart shows these values as data labels.

And id I replace the sales numbers with a formula to generate random values, the chart correctly highlights high and low values whenever data changes.

Dave Bruns

Related shortcuts

This site is perfect for me as a moderate Excel user who sometimes needs a formula that I cannot figure out on my own. It's clean, easy to use, comprehensive and doesn't have distracting material like many other Excel help sites. - Susan
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course