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:

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

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.

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.

Now your graph should look like this:

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