How to Create Min Max and Average Chart in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to create a min max and average chart in Excel. We’ll also learn how to add or draw max min or average lines in an Excel graph.

Data visualization is a crucial tool for understanding and analyzing information effectively. Among the many chart types available, the Min-Max-Average chart in Excel stands out as a powerful tool for displaying the range and central tendency of data. By creating Min-Max-Average charts, explaining their significance, and providing practical tips for maximizing their potential.

Here is an overview of the Min Max Average chart in Excel to help users better understand it.

min max average chart Excel


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


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.

This type of chart helps visualize the dispersion of data points and provides insights into the central tendency of a dataset. Min Max Average charts are particularly useful when working with datasets that exhibit significant variation.

1.1-Min Max Average chart


How to Create Min Max and Average Chart in Excel: with Easy Steps

We’ll make and format the Min Max Average chart in Excel. Let’s follow the instructions below to learn!


Step 1: Create Dataset with Proper Parameters

Here we will create a dataset with proper parameters to make a min max average line chart in Excel. You will format the dataset as you want. We are formatting our dataset like the following.

2-Creation of the dataset with proper parameters in Excel


Step 2: Insert a Line Chart

Now, we’ll insert a line chart using our dataset.

  • Select data range 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

  • As a result, you will be able to create a Line chart.

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

After creating the Line chart, we will format that chart.

  • Give the title of the chart as “Min Max and Average Chart”.
  • After that, right-click on any portion of the Max line chart >> Choose Format Data Series option.

5-Giving chart name as Min Max and Average Chart

  • As a result, the Format Data Series menu pops up >> Choose Fill & Line option >> Select No line from the Line option.

6-Selection of No line option

  • Insert marker size 8 from the Built-in Marker Options.

7-Selection of the Marker Options feature

  • Similarly, remove lines and change the marker size for the min and average line charts.

8-Removing line and change marker size


Step 4: Inserting Min Max and Average Vertical Line on Chart

In this section, we will insert the min max and average vertical line from the Chart Design tab.

  • Select any marker point of the min max and average chart >> Go to the Chart Design tab.
  • Choose Add Chart Element feature >> Select High-Low Lines feature from the Line drop-down list.

9-Using the High-Low Lines feature

  • Finally, you will be able to create min max and average charts in Excel.

10-Creating min max and average chart in Excel


How to Make a Horizontal Max Min Average Chart in Excel

Now, we will make a horizontal Max Min Average 2-D Bar Chart in Excel.

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

11-Selection of the 2-D Bar Chart

  • Finally, you will be able to make a horizontal Max Min Average 2-D Bar chart in Excel.

12-Min Max and Average Horizontal Line Charts


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

In this section, we’ll create a 2-D Column chart. Hence, add the max or Min line to the Column chart in Excel. Let’s follow the instructions below to learn!

  • Select the data range from B4 to C14 >> Go to the Insert tab >> Charts >> Insert Column or Bar Chart >> 2-D Column.
  • As a result, you will make a 2-D Column chart.

16-Creating 2-D Column chart

  • Right-click on the chart area and choose “Select Data” from the list options.

17-Selection of the Select Data feature

  • The “Select Data Source” dialog box pops up. Under the Legend Entries (Series) menu, press the Add option.
  • As a result, the Edit Series dialog box will appear >> insert Series name and Series values >> OK.

18-Insertion of Series Name and series value

  • Hence, the Max 2-D column chart will be inserted >> Select any Column chart.
  • Go to the Chart Design tab >> Choose the Change Chart Type feature from the Type group.

19-Selection of the Change Chart Type feature

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

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

  • Finally, you will be able to add the Max line to the 2-D Column chart.

21-Adding Max Line to 2-D Column chart

  • Similarly, you can add Min Line to a 2-D column chart in Excel.

22-Adding Min Line to 2-D Column chart


How to Draw an Average Line in Excel Graph

Likewise, you can draw an Average line in an Excel graph.

23-Insertion of Average Line chart


How to Make Min Max and Average Column Charts in Excel

In this portion, we’ll make a min max and average column chart in Excel. Follow the instructions below.

  • Select data range B5:G5 and B11:G13. From your Insert tab, go to,

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

24-Making a Min Max Avg Column Chart

  • After that, you can make a min max and average clustered column chart in Excel.
  • Further, you will be able to format the chart as you want.

25-Min MAx and Average Column Chart


Things to Remember

  • Ensure that your data is accurate and complete before creating the chart.
  • Use descriptive labels and data points to make the chart easily understandable to the users. Avoid cluttering the chart with unnecessary information.
  • Choose axis scales that best represent your data without distorting the visualization.

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 to the data will automatically reflect in the chart. You can update the chart by modifying the values in the data range, adding or removing rows, or changing the source data series.

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 the chart’s title, axis labels, data labels, colors, line styles, and marker styles. Right-click on various 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 on any data series in the chart and select “Add Trendline.” Choose the desired trendline type (linear, exponential, etc.) and customize its options if needed.


Conclusion

Min Max Average chart in Excel is a valuable tool for visualizing and understanding data dispersion and central tendencies. By following the steps provided in this article, you can create meaningful Min-Max-Average charts and gain deeper insights into your datasets.

Remember to interpret the chart thoughtfully and provide context when sharing it with others, as accurate data visualization is critical to making informed decisions based on the data at hand.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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