Make an Avg-Max-Min Chart in Excel

Make a Chart of Average, Maximum and Minimum Values in Microsoft Excel

By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies
in collaboration with
SUNY at Buffalo Professor Robert Wetherhold, AMSE fellow

These instructions explain how to make an Average-Max-Min chart like this one:

These instructions are for Microsoft Excel 2011.

Instructions for Excel 2013 can be found here.

An Average-Max-Min chart displays the average, maximum, and minimum values where the average is plotted with greater importance. This chart type is used by engineers and scientists. An avg-max-min chart type can be used when an uneven number of data records exist, when gaps exist in the data, or when insufficient samples exist for a more complete analysis.

You're going to need some data to work with. Notice there gaps in the data set (the number of records in each column is not the same).

Using this tutorial you will make an Avg-max-min chart. The tutorial also explains how to save your chart as a template.

Follow these steps to make a really great looking Avg-Max-Min chart.

1. Download the example workbook by clicking the green Excel icon in the dark band above. Your web browser will save AvgMaxMinChart.xlsb to your web browser's default download location. You can find and set this location in your web browser's preferences.

2. Open the file AvgMaxMinChart.xlsb in Microsoft Excel

Notice that our example research data is in the range B2:E10. We will not attempt to directly graph the research data. Instead we will calculate the maximum, average, and minimum for each column of the dataset and make our chart from the calculations. A range below the dataset has been started for this purpose. The heading information was copied and pasted from the research data-set. When working on your own data, be sure to leave at least two completely blank rows between the end of your dataset and the calculated data's heading information.

In our example, our values must be divided by 1,000,000 to be converted from Pa to MPa, so our formulas include a division by 1,000,000. When you work with your own data you would not include division or correction factors unless you need to change units.

3. Enter the following formulas to calculate the maximum value for each column in the data-set:
In Cell B14 enter =MAX(B3:B10)/1000000
Copy this formula to columns C, D and E in row 14

When choosing the cell range with your own data, use the column that covers the largest complete data range and then copy that formula to the adjacent columns. MAX, MIN and AVERAGE worksheet functions ignore blanks, but zero values are included.

4. Enter the following formulas to calculate the average value for each column in the data-set:
In Cell B15: =AVERAGE(B3:B10)/1000000
Copy this formula to columns C, D and E in row 15

5. Enter the following formulas to calculate the minimum value for each column in the data-set:
In Cell B15: =MIN(B3:B10)/1000000
Copy this formula to columns C, D and E in row 16

Your calculated data should be:

6. Select the cell range B14:E16 to select the calculations

7. On the Home tab of the Ribbon, in the Number group, set the format to Number and then click the Decrease Number of Decimal Places button or Increase Number of Decimal Places button to set the desired number of decimal places. You may have to widen the worksheet to see all the number controls.

In our example we chose zero decimal places, like this:

You're now ready to make your chart.

8. While the selection cursor is in any cell in our calculation range (as shown above), on the Charts tab of the Ribbon, click Line, and from the palette, in the 2-D Line section click Line to make a Line chart.

Your calculated data and chart should look like this:

Initial chart

The chart now needs to be formatted. You format each line separately. We will start by making the Max, Min and Average lines invisible. The trick here is that the lines will still exist in the chart. Even though you can't see the lines, we can still connect their data points with new max-min lines.

9. Double-click the Max line. This action selects the Max line, which you can tell because the Max line's data points become highlighted. At the same time, the Format Data Series dialog opens.

10. In the left pane of the Format Data Series dialog, choose Line. On the Solid tab of the Format Data Series dialog, in the Color pop-up menu choose No Line, as shown here, then click OK to close the Format Data Series Dialog.

Choose No Line

11. Double-click the Min line. This action selects the Min line, which you can tell because the Min line's data points become highlighted. At the same time, the Format Data Series dialog opens.

12. In the left pane of the Format Data Series dialog, choose Line. On the Solid tab of the Format Data Series dialog, in the Color pop-up menu choose No Line, and then click OK to close the Format Data Series Dialog. The only line that is visible now is the Average line.

Next, you will format the Average line. In the next step you will open the Format Data Series dialog again. Keep the Format Data Series dialog open for steps #13 through #19 as you format the Average line.

13. Double-click the Average line. This action selects the Average line, which you can tell because the data points become highlighted. At the same time, the Format Data Series dialog opens.

14. In the left pane of the Format Data Series dialog, choose Line. On the Solid tab of the Format Data Series dialog, in the Color pop-up menu choose No Line.

15. In the left pane of the Format Data Series dialog, choose Options. Click the check box to turn on High-Low lines. Your chart now has vertical lines connecting the Average, Maximum and Minimum data points with data points of the Average selected, as shown:

Add Hi-Low chart lines

16. In the left pane of the Format Data Series dialog, choose Marker Style. In the Style pop-up choose the circle (second from the bottom). Set the Size to 8.

17. In the left pane of the Format Data Series dialog, choose Marker Fill. Click the Color pop-up and choose black.

18. In the left pane of the Format Data Series dialog, choose Marker Line. Click the Color pop-up and choose No Line.

19. (Optional) In the left pane of the Format Data Series dialog, choose Shadow. Click the check box for Shadow.

20. Click OK to close the Format Data Series dialog.

21. Double-click one of the vertical bars to display the Format High-Low Lines dialog. Keep the Format High-Low Lines dialog open for steps #21 through #23c.

22. On the Solid tab of the Format High-Low Lines dialog, in the left hand panel choose Line. Click the Color pop-up button and choose black.

23. On the Weights and Arrows tab of the Format High-Low Lines dialog make the following changes:
23a. In the Arrows group, change the Begin Style and End Style to Oval Arrow as shown:

Choose Begin and End style

23b. In the Arrows group, change Begin size to Arrow L, size 1 as shown:

23c. In the Arrows group, change End size to Arrow R, size 1, then click the OK button to close the Format High-Low Lines dialog.

Your chart looks excellent right now, but there are some finishing touches that can make it look truly professional. Next, format the chart's legend so that only Average displays.

24. Click once on the Legend to select it. The corners of Legend should be highlighted, as shown:

25. Click into the Legend to select Max. The Legend highlight turns off, and Max becomes highlighted, as shown:

26. While Max is selected, on your keyboard, press the Delete key to delete the Max label from the Legend.
27. Once again, Click once on the Legend to select it. The corners of Legend should be highlighted.
28. Click into the Legend to select Min. The Legend highlight turns off, and Min corners will be highlighted.
29. While Min is selected, on your keyboard, press the Delete key to delete the Min label from the Legend.

Here's what your finished chart should look like:

You should save your work at this point.

There are several optional steps you may wish to take with your chart: add a chart title, save your customizations as a Chart Template, save your chart as a picture file, add a label to the Y-axis, add a trendline or regression, or add standard deviation markers.

Add a title to your chart:

1. Click once on the border of the chart to select the entire chart. A solid blue border displays around your entire chart.
2. On the purple Chart Layout tab of the Ribbon, choose Chart Title and then a position for the title. The Chart Title text box displays in your chart.
3. Double-click into the Chart Title Text box and then edit the Text. Click away from the chart to end text editing mode.
4. (Optional) Double-click the Chart Tile text box to display the Format Title dialog should you desire to change the appearance of the Chart Title.

Save your chart as a Chart Template.

1. Right-click on the border of the chart. From the resulting menu choose Save Template. The Save Chart Template dialog displays.
2. In Save As, change the file name to AvgMaxMin4Col.crtx
3. Click the Save button.
4. Quit Excel (press Command-Q) (You have to re-open Excel to refresh the Templates list in the Ribbon)
Now you can format a chart in a single click. Go back to Step #1 at the very beginning of this web page a download another copy of
AvgMaxMinChart.xlsb. Set up the calculations in steps #1 through #7. While the selection cursor is in the calculation range, on the Chart tab of the Ribbon click the Other button. Drag the scroll bar down to expose the Templates section of the palette which is at the bottom of the palette. Choose AvgMaxMin4col. Your chart will appear formatted with the most of the formatting as when you saved your chart template, as shown:

Chart template files (.crtx) can be shared with other Excel Mac and PC users. Put chart template files into this (usually hidden Library) directory on your Mac:
UserName/Library/Application Support/Microsoft/Office/Chart Templates/

Here's a suggested location for PC users:
C:\Users\UserName\AppData\Roaming\Microsoft\Templates\Charts Save your chart as a Picture file

To save your chart as a picture:

1. Right-click on the border of the chart. From the resulting pop-up menu choose Save As Picture.
2. In the Save dialog, click the Format button to choose a picture type.
3. In Save As, type a name for your picture file.
4. (Optional) Choose a folder in which to save your picture (the default folder is Pictures)
5. Click the Save button.

Add a Y-axis label

Excel doesn't have a built-in way to display a label for the Y-axis in your Avg-Max-Min chart. The work-around is to add a shape to the chart.
1. Click in a blank area on your chart. A blue selection indicator will display around your chart.
2. On the Insert menu choose Insert > Picture > Shape to display the Shapes tab of the Media browser. Alternatively, click the Media Browser button on the Standard Toolbar, and then click the Shapes tab of the Media Browser.
3. Click the Square shape and then drag across your chart to make a shape.
4. Right-click on your shape. Ignore the pop-up menu and click into the shape and type the label to be used for the Y-axis.
5. While the shape is selected, on the Format tab of the Ribbon, in the Shape Styles group, click the small triangle at the right side of the Fill button. Choose No Fill from the pop-up palette.
6. While the shape is selected, on the Format tab of the Ribbon, in the Shape Styles group, click the small triangle at the right side of the Line button. Choose No Line from the pop-up palette.
7. While the shape is selected, on the Format tab of the Ribbon, in the Text Styles group, click the small triangle at the right side of the Line button. Choose black color from the pop-up palette.
8. You can right-click on the shape and choose Format Shape and Format Text to control font, shadows, and other aspects of the text.
9. Position the shape as desired.

Add a Trendline or Regression

If you wish to add a Trendline or regression, do the following:
1. Right-click one of the Average data points. From the pop-up menu choose Add Trendline to display the Format Trendline dialog.
2. In the left pane choose Type, then choose a Trend/Regression type and an appropriate order or period for your data.
3. In the left pane choose Options, then choose or type a label for your line, a forecast period, and whether you want to display the equation of the line and/or the R² value to your chart.
4. Click OK to close the Format Trendline dialog.
To re-display the Format Trendline dialog, double-click the trendline.

This example shows our chart with a polynomial trendline with a forecast period:

Add Standard Deviation to your chart

If you wish to add standard deviation values to your chart, the secret is to add two rows to your chart's data source (see example below) - one row for the positive values of the standard deviation, and second row for the negative values. You'll need to use cell formulas to calculate the standard deviation of your data, and add or subtract that value from the Average. Format the chart similarly, but choose a different marker style for the plus and minus standard deviation values.

AvgMaxMin
chart with
standard
deviation

Jim Gordon's MVP Home