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.
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.
- 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.
- 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.
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.
- 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.
- 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.
- 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)
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.
- How to Create Gaussian Distribution Chart in Excel
- Plot Weibull Distribution in Excel (with Easy Steps)
- How to Plot Poisson Distribution in Excel (with Easy Steps)
- Make a Cumulative Distribution Graph in Excel
- Create a Probability Distribution Graph in Excel (With 2 Examples)
- How to Plot Frequency Distribution in Excel (4 Easy Ways)
- Plot Normal Distribution in Excel with Mean and Standard Deviation