To make an Excel histogram with a normal distribution curve, calculate the count in each bin and the normal curve’s expected count, then plot them together in a combo chart. Excel’s built-in Histogram chart does not add a fitted normal curve automatically, so a small helper table gives you more control over the bins and scaling.
What a histogram with a normal curve shows
A histogram groups numeric observations into intervals, or bins, and displays how many values fall in each interval. The column heights reveal the data’s shape: it might be roughly symmetric, skewed, clustered, or marked by unusually distant values. A normal distribution curve is a theoretical bell-shaped reference based on a mean and a standard deviation.
Putting the curve over the histogram lets you compare the observed pattern with that reference. It can help show whether a normal model is a reasonable approximation, but it does not prove that the data are normally distributed. A curve can look convincing with a particular bin width and misleading with another, and a single curve can conceal subgroups or outliers.
The scales must match for the comparison to be meaningful. A normal density has units of probability per unit of measurement, while histogram columns usually show counts. To overlay the curve on count columns, multiply the density at each bin center by the number of observations and the bin width. The result is an approximate expected count for that interval.
Choose a method: built-in chart or helper table
Excel for Microsoft 365 on Windows offers a built-in histogram chart at Insert > Insert Statistic Chart > Histogram. It is a quick way to inspect a distribution: Excel groups the values into bins and draws the columns. However, this chart type does not provide a simple built-in option to add a normal curve as another series.
For an overlaid curve, build a helper table and use a combo chart. This takes a few extra steps, but you can set explicit bin boundaries, verify the counts, and keep the curve on the same count scale as the columns. The method also makes it easier to update the display if you change the bin width or the source data.
The Analysis ToolPak offers another way to calculate histogram frequencies. On Windows, enable it through File > Options > Add-ins; choose Excel Add-ins in the Manage box, select Go, and check Analysis ToolPak. Then use Data > Data Analysis > Histogram. The tool can produce frequency output and a histogram, but you still need to calculate and add the normal series yourself. Menu availability and add-in setup can differ on Mac, so check Excel’s Add-ins options there.
Prepare the data and select useful bins
Put the numeric observations in one column, with a heading in A1 and values in A2:A101 for the example below. Use actual measurements rather than precomputed percentages unless your chart is intended to compare proportions. Remove or investigate text, blanks, and errors; Excel’s count and statistical functions ignore some nonnumeric cells, which can make the sample size differ from what you expect.
Choose bins that cover the full data range. For a simple example, suppose measurements run from 40 to 100 and you want intervals of width 10: 40–50, 50–60, and so on through 90–100. Put each lower boundary in column D and its upper boundary in column E, with the first bin on row 2. Use consistent widths where possible. Very wide bins can hide meaningful patterns; very narrow bins can make random variation look like structure.
For adjacent bins, count values from the lower boundary up to, but not including, the upper boundary. This convention avoids counting a value that sits exactly on a boundary twice. Make the final bin include its upper boundary so the maximum observation is not left out. If your data extend beyond the listed bins, add more bins rather than silently excluding those values.
Calculate observed and expected bin counts
Set up the helper table with lower bounds in D, upper bounds in E, observed counts in F, expected normal counts in G, and bin centers in H. Put the mean, sample standard deviation, number of observations, and bin width in J2:J5. Each formula below assumes the raw data are in A2:A101, with the first bin’s lower and upper limits in D2 and E2.
- With the observations in A2:A101, enter
=AVERAGE($A$2:$A$101)in J2 for the mean. - With the observations in A2:A101, enter
=STDEV.S($A$2:$A$101)in J3 for the sample standard deviation. - With the observations in A2:A101, enter
=COUNT($A$2:$A$101)in J4 for the number of numeric observations. - With the bin boundaries in D2 and E2, enter
=E2-D2in J5 for the bin width. - With raw observations in A2:A101 and the first bin limits in D2 and E2, enter
=COUNTIFS($A$2:$A$101,">="&D2,$A$2:$A$101,"<"&E2)in F2. Fill it down for ordinary bins. In the last bin only, use=COUNTIFS($A$2:$A$101,">="&D7,$A$2:$A$101,"<="&E7)if that final bin is on row 7. - With boundaries in D2 and E2, enter
=AVERAGE(D2,E2)in H2 for the bin center, then fill down. - With the bin center in H2 and the mean, sample standard deviation, count, and width in J2:J5, enter
=NORM.DIST(H2,$J$2,$J$3,FALSE)*$J$4*$J$5in G2, then fill down.
The COUNTIFS formula counts each ordinary bin from its lower boundary up to, but not including, its upper boundary. Adjust D7 and E7 in the last-bin example if your final row differs. The NORM.DIST formula uses the normal probability density at the bin center, then scales it by sample size and bin width. This midpoint calculation is an approximation to the expected count across the whole bin; it is generally suitable for a visual overlay when bins are reasonably narrow.
For example, if the sample contains 100 observations, a bin is 10 units wide, and the normal density at its center is 0.03, the expected count is 0.03 × 100 × 10 = 30. The formula returns that count on the same general scale as the histogram’s frequency column. The example is illustrative; actual expected counts depend on the data and chosen bin.
Build the combo chart in Excel
Select the bin labels or centers along with the observed and expected counts. For clear labels, you can make a separate label column such as “40–50” and select that column with F and G; the labels are for display, while the calculations use the numeric boundaries and centers. Then choose Insert > Insert Combo Chart > Create Custom Combo Chart.
Set the observed-count series to Clustered Column and the expected-count series to Line. Keep both series on the primary axis: each now represents a count, so a secondary axis would introduce a scale mismatch. Confirm the chart and add a descriptive title, such as “Observed and normal expected counts.” Use the chart’s axis titles or labels to identify the horizontal measurement intervals and vertical frequency.
Format the columns so they remain visible behind the curve. For example, reduce the column gap width or use a lighter fill, and remove markers from the line if they make the curve crowded. The line connects expected counts at bin centers, so it may appear as a series of straight segments rather than a perfectly smooth bell. That is normal for this bin-based comparison. If the line is jagged, review the bin widths and sample size before trying to smooth the chart visually.
If Excel assigns the wrong categories or series, use Chart Design > Select Data to check the chart data range and series names. A combo chart can use a category axis, so the bins are treated as ordered labels rather than a continuous numeric x-axis. That is appropriate for standard histogram columns, but it means the displayed line is a visual guide across those categories.
Choose bin widths and interpret the fit
There is no single bin width that is best for every dataset. A useful starting point is to compare a few reasonable widths and check whether the overall shape remains similar. If changing the width dramatically changes the apparent story, describe the chart cautiously. Keep the bin boundaries fixed when comparing two groups; otherwise, differences in bin placement can look like differences in the data.
Look beyond whether the bars resemble a bell. A normal model is symmetric, so a persistent long tail on one side, multiple peaks, or a gap between clusters suggests that the distribution may not be well described by one normal curve. Also check the original values and a scatter or time plot when order matters. Measurements collected over time can drift or form batches even if the combined histogram looks approximately bell-shaped.
The curve here uses the sample mean and sample standard deviation, so it is fitted to the data rather than an independent prediction. The sample standard deviation, calculated with STDEV.S, is commonly appropriate when the data are a sample from a larger process. If the dataset is the complete population of interest, a population standard deviation may be more appropriate; that choice changes the curve. Neither choice repairs biased, dependent, or otherwise unsuitable data.
Common problems and how to fix them
- The curve is almost flat. Check that you multiplied the density by both the observation count and bin width. Plotting NORM.DIST with FALSE by itself produces a density, not a frequency count.
- The curve is far above or below the bars. Confirm that the histogram shows counts rather than percentages, and that the expected series uses the same sample size and bin width. Do not put one series on a secondary axis just to make the shapes look similar.
- The observed counts do not add up to the sample size. Check that the bins span the complete data range, the final bin includes its upper edge, and the data contain no unexpected text or errors. The sum of the observed counts should equal COUNT for the values being analyzed.
- The first or last bar is unexpectedly empty. Verify the boundary values and whether your bin convention includes the lower or upper endpoint. A value exactly on a boundary belongs to the next bin under the half-open rule used above.
- The chart looks unlike Excel’s built-in histogram. Compare bin settings. The built-in chart may choose its own bin width or boundaries, while the helper table uses the limits you specified. Matching the boundaries is necessary for a fair visual comparison.
When this chart is useful—and when it is not
A histogram with a normal reference curve is useful for an initial distribution check, a quality-control discussion, or explaining how observed frequencies compare with a simple model. It is especially readable when the measurement is continuous, the sample is large enough to create informative bins, and the bins are consistent with the question being asked.
It is not a formal normality test, and visual overlap alone should not be treated as evidence that a statistical assumption is satisfied. For a small sample, a histogram can change substantially with bin choices. For a large sample, small departures from normality may be visible but not practically important. If a formal decision matters, use a method suited to the analysis and consider subject-matter context rather than relying on the chart alone.
For an alternative view, a normal probability plot (often called a Q–Q plot) compares ordered observations with theoretical normal quantiles. It can make tail departures easier to spot than a histogram, although it answers a related but different visual question. Excel does not offer a one-click normal Q–Q chart in the standard chart gallery, so it generally requires additional calculations or a statistical tool.
Frequently Asked Questions
Can Excel add a normal curve directly to a Histogram chart?
Not as a simple built-in option for the standard Histogram chart in Excel for Microsoft 365 on Windows. Calculate expected counts in a helper table and plot the histogram counts as columns with the expected counts as a line in a combo chart.
Should I use NORM.DIST with TRUE or FALSE?
Use FALSE for the density value at a bin center. TRUE returns a cumulative probability up to that value, which is not the height needed for this curve. The density still needs scaling by sample size and bin width to compare it with count columns.
Why does the normal curve not match the bars exactly?
The curve is a model, while the bars show the particular sample. Random variation, skew, multiple groups, outliers, bin choices, and the midpoint approximation can all create differences. The curve is a reference, not a prediction that every bin must match.
Can I use percentages instead of counts?
Yes, but both series must use a percentage scale. Divide observed bin counts by the total number of observations, and scale the normal density by bin width to get an expected proportion per bin. Do not compare percentage bars directly with expected counts.
Does this work in Excel for Mac or the web?
The formulas and general helper-table method work in current Excel versions that support NORM.DIST and COUNTIFS, though ribbon locations and add-in availability can vary. Excel for the web may present chart controls differently; if a combo-chart option is unavailable in your interface, open the workbook in desktop Excel to set the series chart types.
Related
- Excel Group Rows Into a Dropdown: What Actually Works
- Excel Group Duplicate Rows Without Losing Data
- Excel Group Rows: How to Expand and Collapse Rows Easily
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: How to Exclude Blank Rows and ItemsTo exclude blanks from an Excel PivotTable in Microsoft 365 for Windows, open the dropdown for the row,…
- Excel Line Chart Not Plotting Correctly? Fix the DataIf an Excel line chart is missing points, plotting the wrong values, or showing an unexpected axis, first…
- Excel Pivot Chart Colors Keep Changing? Fix the CauseExcel PivotChart colors usually change because filtering, refreshing, or rearranging fields changes which series are displayed or their…
- What Does a Trendline Tell You in Excel? A Clear GuideA trendline in Excel summarizes the general direction or pattern of data in a chart: it can show…
- Combo Chart Icon in Excel: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the combo chart icon on the Insert tab, in the…
- How to Edit a Chart in Google Sheets: A Step-by-Step GuideTo edit a chart in Google Sheets, select it and double-click, or click its three-dot menu and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.