Highlighting maximum / minimum value of a series in a chart in Excel

Have you ever had a chart and wondered how you could highlight the maximum or minimum value of the series in that Excel chart.

We will discuss exactly that today.

Let's see how we can do it and create a chart like the one below:

And how do we highlight them?

To start doing it we need some data. Ok, we are going to use a table like this:

Whose visual representation traditionally looks like this:

Well, let's now highlight the maximum and minimum value of that series.

Step 1: We calculate the maximum / minimum value of a series

In the table shown above we need to add 2 more columns which will show the Maximum / Minimum.

To calculate the maximum (or minimum) value the formula to use is:

Imagine Series 1 is located in the range {C7:C18} as you can see in the image above. Then in cell D7 we enter the formula used for calculating the maximum value.

=IF(MAX($C$7:$C$18)=$C7,$C7,NA())

That basically says that if the value in C7 is equal to the maximum number (calculated by MAX($C$7:$C$18)) then we return the value, and if it  is not the maximum we return an error (this is important because Excel does not include errors in charts).

That formula now can be copied the cells D8:D18.

Similarly it would be to calculate the minimum, only that the formula now would be:

=IF(MIN($C$7:$C$18)=$C7,$C7,NA())

Now what we do is to include the Maximum and Minimum series in the chart above, but how it looks does not change much because they are only 2 points that are already showing on the series 1, but as seen in the legend the series Maximum and Minimum have been added.

Related links:

IF function

MAX function

MIN function

NA function

Step 2: We change the marker to something that stands out

By now we have already calculated the maximum and minimum and we have included them in the chart.  Now what we need to do is change the marker of the series Maximum and Minimum to something that helps us identify it easily.

To change the marker we will need to do the following: We will select the Maximum (or Minimum) series. To select it quickly, click on the series 1, and once selected (ensuring that all points of the series have been selected) press the up arrow on your keyboard. We see now that the Maximum series has been selected. Pressing again on the up arrow would take us to the Minimum series.

Once selected, we right click and we will Format Data Series. Once there we will go to Fill & Line> Marker and chose the marker type, colour and size.

In my case I chose a green circle with size 12. We would do similarly with the Minimum series. The result would be something like:

You could also add the Series Name to each marker as to show the name Maximum / Minimum.

To add names, selecting the series Maximum or Minimum and right click and go to Add Data Labels and choose Series Name rather than value.

Step 3: We can also add an image, shape or anything we like

Well to make the highlighted values even more obvious, you could add an image or a shape. Something like this:

To do this create a shape first using Insert> Shapes, once created, copy it and paste it in the maximum / minimum point, and you're done! So simple!

Download example file

To download the example file click on the following link:

Highlight Maximum and Minimum

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+