Formatting Max and Min Values on a Line Graph

I discourage labeling every value on a set of time-series data that will be displayed in a line graph – it just crowds the graph and distracts viewers from the simple message about how something has changed over time. But, there are occasions when displaying the maximum and the minimum values in time-series data may be helpful to the viewer of your report.

Data plotted over time to show how it is trending:

Max and Min Values 01

Data plotted over time to show trend with addition of maximum and minimum values detail:

Max and Min Values 02

Here is a tip for how to set up your spreadsheet with a couple of formulas so that it will find and plot just the maximum and minimum values for you (thanks to Pelitiertech.com for these formulas).

I created two additional columns on my spreadsheet, C is for the calculation of the maximum value in my data and D is for the minimum.

Next I added the following formulas into cells C2 and D2 respectively:

=IF($B2=MAX($B$2:$B$25),$B2, NA())

=IF($B2=MIN($B$2:$B$25),$B2, NA())

And then I copied them down the columns.

Max and Min Values 03

Here is what is cool about these formulas – when you copy them down the columns the #N/A fills in all of the cells that don’t contain the maximum or minimum numbers and then (here is the cool part) when you includes these two columns in your new graph it only charts the cells with values.

So, now the values are there but you need to get them to show as in the example above.

Here is what you do:

  1. Double click on the red line in the legend labeled Max.
  2. Select Format Data Series.
  3. Select Marker Style and choose the style you want.
  4. You will now see the value marked on the line with the marker style you selected. I chose a circle.
  5. Click on the marker and add a data label.
  6. Repeat for the label in the legend for the Min.
  7. Delete the legend – you don’t need it and it is taking up space.

Max and Min Values 04

Now your graph should look like this:

Max and Min Values 05

AND, once you have this set up whenever you add new data the formula will calculate the new minimum and maximum values for you.

This Post Has 2 Comments

  1. Amory

    The voice of rationality! Good to hear from you.

  2. Bob Zoltok

    Good start for me, but not what I want.

    I will have 24 numbers in each column, fromcolumn B thru column N; each column is a year. Cells B2 thru B25 are numbers that I want to plot.
    I want to plot the trend of 2 columns: the minimum value in each column per year and the maximum value in each column per year.

    The trend I want to see is what is the minimum value over 13 years, and what is the maximum value over 13 years. I am only interested (for now) in seeing 2 trendlines, one for the minimum and one for the maximum.

    No formula seems to work, so I am willing to add work columns if necessary.

Leave a Reply