Often you may want to create a chart in Excel that displays the minimum, maximum and average values for various groups, similar to the chart below:
The following step-by-step example shows exactly how to do so.
Step 1: Enter the Data
First, let’s enter the following dataset that contains the minimum, maximum and average points values for players on various basketball teams:
Step 2: Insert Chart
Next, highlight the cell range A1:F4, then click the Insert tab along the top ribbon, then click the icon called Waterfall within the Charts group, then click the icon called High-Low-Close:
The following chart will be created:
Typically this type of chart is used to show the high, low and close prices for stocks on a daily basis but we can use it to display the minimum, maximum and average values for various groups.
Step 3: Customize the Chart
Next, we can customize the chart to make it easier to read.
First, click on any individual line in the chart. In the Format panel that appears on the right side of the screen, click the dropdown next to Begin Arrow type and click Oval Arrow. Repeat for End Arrow type.
The dots for the minimum and maximum values will be larger:
Next, click on any of the tiny circles in the middle of one of the lines.
In the Format Data Series panel that appears on the right side of the screen, click the icon called Marker, then under Marker Options choose a square for the Type and change the Size to 6:
The tiny circle in the center of each line will now be a larger square:
Lastly, customize the title name, add axis labels and delete the legend at the bottom of the chart:
The chart is now much easier to read and interpret.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Plot Multiple Lines in Excel
How to Create a Clustered Stacked Bar Chart in Excel
How to Create a Scatterplot with Multiple Series in Excel
