Highlighting range Q6:Q106 and pressing Ctrl-D fills in all the y values in column Q. Q6 contains the formula =NORM.DIST(P6,L$5,L$6,FALSE). The corresponding y values are shown in column Q. Highlighting the range P7:P106 and pressing Ctrl-D fills in all the x values in column P. Thus, cell P6 contains the formula =L8 and cell P7 contains the formula =P6+L$9. Since we plan to plot 101 points, the distance between each x value and the next is about. For this data, the normal curve runs from x = -2.459 to 17.559 (as shown in cells L8 and L10). We choose to show the normal curve from -4 standard deviations to +4 standard deviations using 101 data points, as shown in range P6:Q106 of Figure 2 (only the first 10 points are displayed). We then want to plot the normal curve, but first, we need to determine a scaling factor to make the areas match, as shown in range L8:L11 of Figure 2. These values are calculated in cells L5 and L6 of Figure 2. What we want is a normal curve with the same mean and standard deviation as the original data. We now need to add the normal curve to this chart. Next place the formula =COUNTIF($B$4:$B$23,” Charts|Scatter (the Scatter with Straight Lines version), you get the histogram shown on the right side of Figure 1. Here, cell E5 contains the number 3 and cell D11 contains 12 we then place the formula =D9-$E$5 in cell D8, highlight the range D8:D10 and press Ctrl-D. The result is shown in columns D, E and F of Figure 1. We start out by creating a frequency table with bin size of 3 and a maximum bin of 12, as described in Frequency Tables. We now show how to create the histogram with overlay for the data in Example 1 of Using Histograms to Test for Normality.

Second, we need a way to scale the normal curve so that the areas match. First, to place the two graphs on the same chart we can’t use a bar chart for the histogram instead, we need to use a scatter plot. There are two complications with this approach. In this approach, we create a histogram and then add to this chart a normal curve whose area under the curve is the same as the area of the histogram.

How to plot histogram in excel