How to Create a Histogram with Bell Curve in Excel (2 Suitable Examples)

In statistics, histogram and bell curve is very popular. The histogram is mainly an approximate representation of the numerical data distribution. When we have the combination of histogram and bell curve, it gives a wide-angle to focus on some more things. This article will mainly focus on how to create a histogram with a bell curve in Excel. I hope you find this article very interesting for further use and gather lots of knowledge regarding this matter.

Watch Video – Create a Histogram with Bell Curve in Excel

What Is Bell Curve?

The bell curve can be defined as a curve similar to a bell shape. This curve defines the normal distribution of the dataset. The highest point of the bell curve denotes the most probable condition of the dataset which means the mean values of the dataset. The bell curve will distribute the values evenly.

In every condition, mean conditions have the better number so the bell curve provides the highest in the middle. The bell curve feature indicates that a 68.2% distribution is within one standard deviation of the mean value. Whereas 95.5% of the distribution is within the two standard deviations of the mean. Finally, 99.7% of the distribution is within the three standard deviations of the mean. Basically, the bell curve will represent the dataset in a way where it shows how the mean value is above the rest of the values.

How to Create a Histogram with Bell Curve in Excel: 2 Suitable Examples

As we want to create a histogram with a bell curve in Excel, we will show two different examples to create a histogram with a bell curve in Excel. These two examples will give you a proper overview of this matter. Our two example is based on student marks and project completion days. Both of the methods are applicable for histograms and bell curves.

1. Histogram with Bell Curve for Student Marks

Our first method is based on the student marks. We take a dataset that includes some students and their marks.

At first, we make a histogram with this dataset and then include a bell curve by calculating the normal distribution. To do this, we have to follow the step very carefully, otherwise, you don’t create a histogram with a bell curve in Excel.

Steps

First, you need to enable the Data Analysis Tool.
To do this, go to the File tab in the ribbon.
Next, select the More command.
In the More command, select Options.

An Excel Options dialog box will appear.
Then, click on Add-ins.
After that, click on Go.

From the Add-ins available section, select Analysis Toolpak.
Finally, click on OK.

To use the Data Analysis Tool, you need to have a Bin range.
We set a bin range by studying our dataset’s lowest and highest values.
We take intervals of 5.

Now, go to the Data tab in the ribbon.
Next, select Data Analysis from the Analysis group.

A Data Analysis dialog box will appear.
From the Analysis Tools section, select Histogram.
Finally, click on OK.

In the Histogram dialog box, select the Input Range.
Here, we take the Marks column as the Input Range from cell C5 to cell C20.
Next, select the Bin Range that we created above.
Then, set the Output options in the current worksheet.
Finally, click on OK.

It will give us the following output where it shows the bin we assigned previously and the frequency of distribution of our dataset. Here, Bin 65 has 1 frequency which means from 60 to 65, they have found one mark of a particular student.

Now, to have a better chart, we need to add a new column and name it the midpoint of the bin instead of the endpoint of that bin.
In the new column, write down the following formula.

=I5-2.5

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.

Then, select the range of cells J5 to K11.

Go to the Insert tab in the ribbon.
From the Charts group, select Scatter Chart. See the screenshot.

From the Scatter chart, select the Scatter with Smooth Lines and Markers.

It will give us the following chart using our dataset.

To make the curve bigger and take it to the center, we need to adjust the x-axis.
Then, double-click on the x-axis to open the Format Axis dialog box.
After that, select the bar icon.
From there, change the Minimum and Maximum values. This range is basically by studying the dataset.

As a result, we get a bigger and center shape curve. See the screenshot.

Next, when you select the chart, a Chart Design will appear.
Select the Chart Design.
Then, from the Chart Layouts, select Add Chart Element.

In the Add Chart Element option, select Error Bars.
From the Error Bars, select More Error Bars Options.

A Format Error Bars dialog box will appear.
Then, in the Vertical Error Bar section, select direction Minus.
After that, set End Style as No Cap.
In the Error Amount section, set the Percentage to 100%.

It will represent the curve in the following way, see the screenshot.

As you can see the line in every bin, we need to change the line into a bar.
To do this, again go to the Format Error Bars.
Then, change the Here, we take the width as 40.

It will shape the curve in the following way. See the screenshot.

Now we need to remove the curve because we have to draw the bell curve here.
To delete the curve, click on the curve.
A Format Data Series dialog box will appear.
In the Line section, select No line.

Then, go to the Marker section.
In the Marker options, select None.

After that, all the lines and markers are gone. But there are some endpoints also in there.
To remove them, click on them.
Then, right-click to open the Context Menu.
From there, select Delete to remove all the endpoints.

As a result, we get the desired histogram from our dataset.

After that, we turn our focus to the bell curve.
Before plotting the bell curve, we need to calculate the Mean, Standard Deviation, and more importantly the Normal Distribution.
At first, we need to find the Mean value of the student marks using the AVERAGE function.
Select, cell F14.

Then, write the following formula in the formula box.

=AVERAGE(C5:C20)

Press Enter to apply the formula.

Next, we need to calculate the standard deviation using the STDEV.P function
To do this, first, select cell F15.

Write down the following formula in the formula box.

=STDEV.P(C5:C20)

Press Enter to apply the formula.

After that, to establish the bell curve, we need to calculate the normal distribution.
We take some values from 60 to 85. This value is taken by studying the histogram properly.
Then, we want to find the normal distribution for the corresponding values.
To determine the normal distribution by using the NORM.DIST function.
Then, select cell C26.

Then, write down the following formula in the formula box. Here, we need to scale the normal distribution in terms of the histogram graph. That’s why we use 97.

=NORM.DIST(B26,$F$14,$F$15,FALSE)*97

Press Enter to apply the formula.

Then, drag the Fill Handle icon down the column.

Now, we can add the bell curve to the histogram curve.
To do this, select the histogram chart which was made previously. It will open up the Chart Design option.
Then, from the Data group, click on Select Data.

A Select Data Source dialog box will appear.
Then, select Add to insert new series.

In the Edit Series dialog box, select X and Y values range of cells.
In the Y series, we set the normal distribution whereas, in the X series, we set the values.
Finally, click on OK.

It will add as Series 2 in the Select Data Source dialog box.
Then, click on OK.

After that, go to the Chart Design and Select Change Chart Type from the Type group.

Then, select the Scatter type chart. See the screenshot.
After that, click on OK.

It will give the bell curve along with the histogram. But here, the curve line is in dotted format.
We need to make it a solid line.

Now, double-click on the dotted curve, and the Format Data Series dialog box will appear.
In the Line section, select Solid line.
Then, change the Color.

Here, we have our final result of a histogram with a bell curve for student marks.

2. Histogram with Bell Curve for Project Completion

Our next example is based on project completion. We take a dataset that includes the name, project ID, and days to complete the task.

To create a histogram with a bell curve in Excel, we need to find the mean, standard deviation, and normal distribution. You need to follow the processes carefully to do this.

Steps

At first, to create a histogram, you need to use Data Analysis Tool.
To use the Data Analysis Tool, you need to have a Bin range.
We set a bin range by studying our dataset’s lowest and highest values.
We take interval 5.

Now, go to the Data tab in the ribbon.
Next, select Data Analysis from the Analysis group.

A Data Analysis dialog box will appear.
From the Analysis Tools section, select Histogram.
Finally, click on OK.

In the Histogram dialog box, select the Input Range.
Here, we take the Marks column as the Input Range from cell D5 to cell D24.
Next, select the Bin Range that we created above.
Then, set the Output options in the current worksheet.
Finally, Click on OK.

It will give us the following output where it shows the bin we assigned previously and the frequency of distribution of our dataset. Here, bin 15 has 1 frequency which means from 10 to 15, they have found one mark of a particular student.

Now, to have a better chart, we need to add a new column and name it the midpoint of the bin instead of the endpoint of that bin.
In the new column, write down the following formula.

=I5-2.5

Then, press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.

Then, select the range of cells J5 to K12.

Go to the Insert tab in the ribbon.
From the Charts group, select Scatter Chart. See the screenshot.

From the Scatter chart, select the Scatter with Smooth Lines and Markers.

It will give us the following chart using our dataset.

Next, when you select the chart, a Chart Design will appear.
Select the Chart Design.
Then, from the Chart Layouts, select Add Chart Element.

In the Add Chart Element option, select Error Bars.
From the Error Bars, select More Error Bars Options.

A Format Error Bars dialog box will appear.
Then, in the Vertical Error Bar section, select direction Minus.
After that, set End Style as No Cap.
In the Error Amount section, set the Percentage to 100%.

It will represent the curve in the following way, see the screenshot.

As you can see the line in every bin, we need to change the line into a bar.
To do this, again go to the Format Error Bars.
Then, change the Here, we take the width as 30.

It will shape the curve in the following way. See the screenshot.

Now we need to remove the curve because we have to draw the bell curve here.
To delete the curve, click on the curve.
A Format Data Series dialog box will appear.
In the Line section, select No line.

Then, go to the Marker option.
In the Marker options, select None.

After that, all the lines and markers are gone. But there are some endpoints also in there.
To remove them, click on them.
Then, right-click to open the Context Menu.
From there, select Delete to remove all the endpoints.

As a result, we get the desired histogram from our dataset.

After that, we turn our focus to the bell curve.
Before plotting the bell curve, we need to calculate the Mean, Standard Deviation, and more importantly the Normal Distribution.
At first, we need to find the Mean value of the student marks using the AVERAGE function.
Select, cell G16.

Then, write the following formula in the formula box.

=AVERAGE(D5:D24)

Press Enter to apply the formula.

Next, we need to calculate the standard deviation using the STDEV.P function.
To do this, first, select cell G17.

Write down the following formula in the formula box.

=STDEV.P(D5:D24)

Press Enter to apply the formula.

After that, to establish the bell curve, we need to calculate the normal distribution.
We take some values from 11 to 40. This value is taken by studying the histogram properly.
Then, we want to find the normal distribution for the corresponding values.
To determine the normal distribution by using the NORM.DIST function.
Then, select cell C28.

Then, write down the following formula in the formula box. Here, we need to scale the normal distribution in terms of the histogram graph. That’s why we use 122.

=NORM.DIST(B28,$G$16,$G$17,FALSE)*122

Press Enter to apply the formula.

Then, drag the Fill Handle icon down the column.

Now, we can add the bell curve to the histogram curve.
To do this, select the histogram chart which was made previously. It will open up the Chart Design
Then, from the Data group, click on Select Data.

A Select Data Source dialog box will appear.
Then, select Add to insert new series.

In the Edit Series dialog box, select X and Y values range of cells.
In the Y series, we set the normal distribution whereas, in the X series, we set the values.
Finally, click on OK.

It will add as Series 2 in the Select Data Source dialog box.
Then, click on OK.

After that, go to the Chart Design and Select Change Chart Type from the Type group.

Then, select the Scatter type chart. See the screenshot
After that, click on OK.

It will give the bell curve along with the histogram. But here, the curve line is in dotted format.
We need to make it as a solid line.

Now, double-click on the dotted curve, and the Format Data Series dialog box will appear.
In the Line section, select Solid line.
Then, change the Color.

Here, we have our final result of a histogram with a bell curve for student marks.

Download Practice Workbook

Download the Practice workbook.

Histogram with Bell Curve.xlsx

Conclusion

To create a histogram with a bell curve in Excel, we have shown two efficient examples through which you can have a clear idea of this topic. Both of these topics are highly applicable for further uses. All of them utilize mean, standard deviation, and normal distribution to establish a bell curve whereas both of them utilize data analysis tools to create a histogram. I hope you find it very informative for your further use. If you have any questions, feel free to ask in the comment box.

<< Go Back to Excel Histogram | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Roxanne Jan 21, 2024 at 7:13 AM

Thank you for the excellent tutorial. It was very helpful. However, I would like to better understand where you got the number 122 in order to scale the curve to the best normal distribution. Do you have any information on how to choose the number for scaling?
- Reply
  Rafiul Hasan Jan 21, 2024 at 2:31 PM
  
  Hi ROXANNE,
  Thanks for your complement. The number 122 used for scaling is chosen based on empirical or trial-and-error methods to visually match the bell curve with the histogram in the specific context of this tutorial. It is not a standard or universally defined scaling factor; rather, it appears to be chosen to align the bell curve with the histogram in a way that the author finds visually pleasing or appropriate for his dataset. If you are reproducing this analysis for a different dataset, you may need to experiment with the scaling factor to achieve the best visual fit for your specific data. Adjusting this factor allows you to customize the appearance of the bell curve to better match the characteristics of your dataset.
  
  Regards,
  Rafiul Hasan
  ExcelDemy