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

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.

Table of Contents hide

Download Practice Workbook

2 Suitable Examples to Create Grade Distribution Chart in Excel

Example 1: Insert a Pie Chart for Grade Distribution

Example 2: Plot a Histogram for Grade Distribution

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Grade Distribution Chart.xlsx

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.

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"))))

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

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

Hence, the pie chart will be created.

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

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

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.

Then AutoFill formula to the rest of column H.

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

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.

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.

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.