How to Create Grade Distribution Chart in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

While working with data in Excel, sometimes it is convenient to insert pie charts or histograms to analyze data easily. If you want to analyze the test results of students you can insert charts to find out the percentage of students getting different grades. In this article, we will walk you through two practical examples to create a grade distribution chart in Excel.


Download Practice Workbook

Download this practice workbook to exercise while reading this article.


2 Suitable Examples to Create Grade Distribution Chart in Excel

In this article, we will demonstrate two suitable examples to create a grade distribution chart in Excel. We will use the following dataset for this purpose.

grade distribution chart excel


Example 1: Insert a Pie Chart for Grade Distribution

In this example, we will determine the grades of all the students using the nested IF function and then insert a pie chart for grade distribution in Excel. The steps to do so are discussed in the following section.

Steps:

  • First of all, insert a column named “Grades”.
  • Then to find out the grade, select cell D5 and write down the following formula, and press Enter.
=IF(C5>=80, "A", IF(C5>=70, "B", IF(C5>=60, "C",IF(C5>=40, "D", "F"))))

Insert a Pie Chart for Grade Distribution in Excel

  • After that, AutoFill formula to the rest of column D.
  • As a result, you will have the grades of all the students.

Insert a Pie Chart for Grade Distribution in Excel

  • Next, add two more columns named “Grades” and “No. of Students”.
  • Then select cell G5 and type the following formula using the COUNTIF function.
=COUNTIF(D$5:D$20,F5)
  • Hit the Enter button to get the students who got an A.

  • Then AutoFill formula to the rest of the cells in column G.

  • Now it’s time to insert a pie chart. To do so, select all the data from column F to column G, and then from the Insert tab, go to,

Insert → Charts → Insert Pie or Doughnut Chart → Pie

Insert a Pie Chart for Grade Distribution in Excel

  • Hence, the pie chart will be created.

Insert a Pie Chart for Grade Distribution in Excel

  • Double-click on the chart title and edit it as per your need.
  • Then click on the chart and from the Chart Design tab, go to,

Chart Design → Add Chart Element → Data Labels → Data Callout

Insert a Pie Chart for Grade Distribution in Excel

  • Consequently, you will have your grade distribution chart with proper formatting.

grade distribution chart excel

Read More: How to Create a Distribution Chart in Excel (2 Handy Methods)


Example 2: Plot a Histogram for Grade Distribution

In this example, we will create a histogram for grade distribution in Excel. Read the following steps to learn how to do it.

Steps:

  • First, determine the No. of students who got an A, B, and other grades just like in the previous example.
  • Then insert a column named “Percentage” and write the following formula in cell H5.
=G5/SUM(G$5:G$9)
  • After that, hit Enter.

Plot a Histogram for Grade Distribution in Excel

  • Then AutoFill formula to the rest of column H.

Plot a Histogram for Grade Distribution in Excel

  • After that, to convert the column H data into a percentage, select cells H5 to H9, and then from the Home tab, go to,

Home → Number → Percentage

  • Once the values are converted into percentages, select the Grades column and then select the Percentage column while holding the Ctrl button.
  • Then click on the Insert tab and go to,

Insert → Charts → Insert Column or Bar Chart → Clustered Column

Plot a Histogram for Grade Distribution in Excel

  • As a result, the histogram will be created. Double-click on any of the bars to open the Format Data Point panel.
  • Then click on Fill & Line and check the box of Vary colors by point.

  • Hence, the bars will have different colors and the histogram will look more appealing.

Plot a Histogram for Grade Distribution in Excel

Notes
  • Don’t forget to give proper cell references or you won’t get the desired results.
  • You can format the charts in various ways using the Format Chart option to enhance its appearance.

Read More: How to Plot Normal Distribution in Excel (With Easy Steps)


Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know two practical examples of how to create a grade distribution chart in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.


Related Articles

Alif Bin Hussain
Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo