How to Create a Min-Max-Average Chart in Excel – 4 Steps

This is an overview:

min max average chart Excel


Download Practice Workbook

Download the practice workbook.


What Is a Min Max Average Chart?

A Min-Max-Average chart, also known as a range chart or error bar chart, is a graphical representation of data that displays three key values: the minimum, maximum, and average.

1.1-Min Max Average chart


Step 1 – Create a Dataset

This is the sample dataset.

2-Creation of the dataset with proper parameters in Excel


Step 2 – Insert a Line Chart

  • Select  B5:G5 and B11:G13.
  • Go to the Insert tab >> Charts >> Insert Line or Area Charts >> 2-D Line with markers.

3-Inserting a Line Chart

A line chart will be displayed.

4-Creating a Line Chart to make Min Max Avg Chart in Excel

Notes: Leaving the first cell blank or using it for labels helps Excel correctly interpret the data and create an accurate 2-D column chart.

Step 3 – Formatting the Line Chart

  • Name the chart: “Min Max and Average Chart”.
  • Right-click any portion of the Max line chart >> Choose Format Data Series.

5-Giving chart name as Min Max and Average Chart

  • Choose Fill & Line >> Select No line in Line.

6-Selection of No line option

  • Choose marker size 8 in Built-in Marker Options.

7-Selection of the Marker Options feature

  • Remove the lines and change the marker size for min and average.

8-Removing line and change marker size


Step 4 – Inserting Min Max and Average Vertical Line

  • Select any marker point of the min max and average chart >> Go to Chart Design.
  • Choose Add Chart Element >> Select High-Low Lines in Line.

9-Using the High-Low Lines feature

This is the output.

10-Creating min max and average chart in Excel


How to Make a Horizontal Max Min Average Chart in Excel

  • Select the data range >> Go to the Insert tab >> Charts >> Insert Column or Bar Chart >> 2-D Bar.

11-Selection of the 2-D Bar Chart

A horizontal Max Min Average 2-D Bar chart is displayed.

12-Min Max and Average Horizontal Line Charts


How to Add a Max or Min Line to a Chart in Excel

  • Select B4:C14 >> Go to the Insert tab >> Charts >> Insert Column or Bar Chart >> 2-D Column.

A 2-D Column chart will be displayed.

16-Creating 2-D Column chart

  • Right-click the chart area and choose “Select Data”.

17-Selection of the Select Data feature

  • In the “Select Data Source” dialog box, select Legend Entries (Series) and click Add.
  • In Edit Series, enter Series name and Series values.
  • Click OK.

18-Insertion of Series Name and series value

The Max 2-D column chart will be displayed.

  • Select any Column in the chart.
  • In Chart Design >> Choose Change Chart Type in Type.

19-Selection of the Change Chart Type feature

  • In the Change Chart Type dialog box >> All Charts >> Combo >> Stacked Line >> OK.

20-Select the Stacked Line chart to add a Max line

The Max line is added to the 2-D Column chart.

21-Adding Max Line to 2-D Column chart

  • Follow the same procedure to add a Min Line.

22-Adding Min Line to 2-D Column chart


How to create an Average Line in Excel Graph

23-Insertion of Average Line chart


How to Make Min Max and Average Column Charts in Excel

  • Select B5:G5 and B11:G13.
  • Go to:

Insert >> Charts >> Insert Column or Bar Charts >> Clustered Column

24-Making a Min Max Avg Column Chart

The min max and average clustered column chart is displayed.

  • Format the chart.

25-Min MAx and Average Column Chart

 


Frequently Asked Questions

1. Can I update the data in the chart automatically?

Answer: Yes, if your chart is linked to a data range, any changes you make will automatically be displayed in the chart.

2. Can I customize the appearance of the Min-Max-Average chart?

Answer: Yes, you can customize the appearance of the Min-Max-Average chart. You can change: title, axis labels, data labels, colors, line styles, and marker styles. Right-click chart elements to access the formatting options.

3. How can I add a trendline to the Min-Max-Average chart?

Answer: To add a trendline, right-click any data series in the chart and select “Add Trendline.” Choose a trendline type (linear, exponential, etc.).

 

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

2 Comments
  1. Just awesome . I’ve never read this type of article

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo