Bell Curve Pivot Chart in Excel: Build and Explain It

A PivotChart can summarize your data into bins, but Excel does not provide a one-click bell-curve PivotChart. To show both the observed bin counts and a normal-distribution curve, use a PivotTable for the summary if you like, then build a regular combo chart from a helper table containing the bin counts and calculated curve values.

What a bell curve PivotChart means

People usually mean one of two things when they search for a bell curve PivotChart: a chart of frequency counts that looks roughly bell-shaped, or a frequency chart with a theoretical normal curve drawn over it. Those are different results. A bell-shaped histogram describes the pattern in the observations; a normal curve is a mathematical model that can be compared with that pattern.

A PivotChart is a chart connected to a PivotTable. It is useful for summarizing and interactively filtering data, but it does not automatically calculate a normal distribution. PivotCharts also have restrictions on chart types and data series compared with ordinary charts. In particular, adding a calculated series from a separate worksheet range is not a straightforward PivotChart operation. A helper table and a regular chart are the dependable way to create the overlay.

The method below is for Excel for Microsoft 365 on Windows. The statistical functions used are also available in current Excel for Mac, although ribbon placement can vary. Excel for the web can handle many of the formulas and charts, but some PivotTable and chart-editing options may be more limited.

What you need before you start

Start with one numeric measurement per record, such as a test score, delivery time, or product dimension. Remove or investigate text entries, blanks, and impossible values before interpreting the chart. Decide what each bin should cover: for example, intervals of five points for scores measured in whole numbers. Equal-width bins make the shape easier to compare with a normal curve.

For the worked example, suppose measurements are in A2:A101 and the sample has 100 values, a mean of 50, and a sample standard deviation of 10. These figures describe an illustrative dataset, not a claim about any real set of measurements. Put labels in column B and use the following layout:

B2: Mean; B3: Sample standard deviation; B4: Number of observations; B5: Bin width.
Enter 5 in B5, so bins are five units wide.
In D1:F1, enter Bin center, Observed count, and Expected normal count.
Use D2:D14 for centers 20, 25, 30, and so on through 80.

With the raw measurements in A2:A101, put this formula in B2 to calculate the sample mean:

=AVERAGE(A2:A101)

With the same measurements in A2:A101, put this formula in B3 to calculate the sample standard deviation:

=STDEV.S(A2:A101)

With the same measurements in A2:A101, put this formula in B4 to count numeric observations:

=COUNT(A2:A101)

For the example, B2, B3, and B4 would return 50, 10, and 100 respectively if the illustrative data have those statistics. STDEV.S is appropriate when the data are a sample used to estimate a larger population. If the data contain the entire population of interest, STDEV.P may be the appropriate descriptive measure instead.

Make a PivotTable of observed frequencies

A PivotTable is a convenient way to inspect how often values fall into ranges. Select the source data, choose Insert > PivotTable, confirm the table or range, choose New Worksheet, and select OK. In the PivotTable Fields pane, drag the measurement field to Rows and drag it again to Values. If Excel summarizes the Values field as Sum rather than Count, open its drop-down, choose Value Field Settings, select Count, and confirm.

To group numeric row labels into intervals, right-click one of the numeric labels in the PivotTable and choose Group. Set Starting at, Ending at, and By to suitable values, such as 20, 80, and 5 for a range of five-unit bins. The exact start and end should cover the data; values outside the chosen range need attention rather than silently disappearing from your interpretation. If Group is unavailable, check that the source field contains numeric values without blanks or text entries, refresh the PivotTable, and try again.

With a cell in the PivotTable selected, choose PivotTable Analyze > PivotChart, then select a Column chart. A column chart with narrow gaps can resemble a histogram. This view is useful for filtering categories or comparing groups, but it is only the observed-frequency part of the analysis. It is not yet a normal curve overlay.

Build the helper table and curve values

For the clearest overlay, use the helper table in the ordinary worksheet. Enter 20 in D2 and fill the remaining centers at five-unit intervals through D14. Alternatively, with 20 already entered in D2 and bin width in B5, put this formula in D3 and fill it down to D14:

=D2+$B$5

With raw measurements in A2:A101, bin centers in D2:D14, and bin width in B5, put this formula in E2 and fill it down to E14 to count observations from the lower edge of each bin up to, but not including, its upper edge:

=COUNTIFS($A$2:$A$101,">="&D2-$B$5/2,$A$2:$A$101,"<"&D2+$B$5/2)

The half-open intervals prevent a value on a shared boundary from being counted in two neighboring bins. For example, the bin centered at 50 with width 5 counts values from 47.5 up to, but not including, 52.5. If your measurements are integers, these boundaries still work, though you may choose bin edges that better match how the values are recorded.

To calculate the expected number of observations in each bin under a normal model, use the cumulative normal probability at the upper edge minus the cumulative probability at the lower edge. With mean in B2, standard deviation in B3, sample size in B4, bin width in B5, and the bin center in D2, put this formula in F2 and fill it down:

=$B$4*(NORM.DIST(D2+$B$5/2,$B$2,$B$3,TRUE)-NORM.DIST(D2-$B$5/2,$B$2,$B$3,TRUE))

NORM.DIST with TRUE returns cumulative probability. At a center of 50 in the stated example, the expected count is about 19.74 for the interval from 47.5 to 52.5. The value need not be a whole number: it is a model-based expected frequency, not an observed count. Using cumulative probabilities accounts for the full width of the bin and is generally more accurate than multiplying the curve height at the center by the bin width.

Create the chart that shows both series

Select D1:F14 and choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set Observed count to Clustered Column and Expected normal count to Line, then select OK. Keep both series on the primary axis because both are measured as counts. If the chart dialog offers a secondary-axis option, leave it cleared for this setup. Add a useful chart title and axis labels, and reduce the column gap width if you want the bars to look more like a histogram.

The line connects the expected counts at the bin centers. It is a practical visual comparison, not a perfectly continuous curve. For a smoother-looking theoretical line, create a second set of x-values at smaller increments and calculate the normal density at those points. To compare density with count bars, scale the density by the sample size and bin width; do not plot unscaled density against counts because the units and magnitudes differ. The bin-based expected-count line is usually easier to read and explain.

This chart is a regular chart based on worksheet cells, not a PivotChart. If you want PivotTable filtering, you can use a slicer or filter to select a subset, then make sure the helper formulas and chart use the same subset. A fixed A2:A101 formula range will not automatically include newly appended records. Convert the source to an Excel Table with Insert > Table, or extend the ranges when adding data, and refresh the PivotTable when needed.

How to interpret the result

Compare the bars with the expected line, but do not treat a visual match as proof that the data are normally distributed. A roughly symmetric, mound-shaped pattern may be consistent with a normal model. Skew, multiple peaks, gaps, or unusually long tails can suggest that a normal curve is a poor description, that the data combine different groups, or that the bin choices conceal important details.

Check the sample size: small samples can look irregular even when drawn from a normal population.
Try another reasonable bin width: changing bin boundaries can change the apparent shape.
Inspect the original values for outliers, data-entry errors, or separate subgroups.
Use a normal probability plot or an appropriate statistical test if formal assessment matters; a PivotChart alone is not a normality test.

The expected counts in the tails may be very small. That is normal for the model, but it also means visual comparisons there can be noisy. The illustrated range from 20 to 80 covers most, but not all, of a normal distribution with mean 50 and standard deviation 10. If actual observations fall outside the displayed bins, add bins or choose a wider range so the chart does not imply that those observations do not exist.

PivotChart versus histogram and regular combo chart

A PivotChart is best when interactive summarization is the main goal: you can filter fields, compare categories, and refresh the report from its PivotTable. A native Histogram chart is designed to bin raw numeric observations, but it does not inherently provide the PivotTable workflow or a calculated normal overlay. A regular combo chart gives the most control over combining observed counts with a helper-series curve, but it requires maintaining the helper calculations.

For a small, stable dataset, formulas and a regular combo chart are usually the simplest route. For a report that needs frequent filtering, make the PivotTable the source for observed counts and build a separate chart from a linked summary table, or use a carefully designed worksheet report. Do not assume that converting a PivotChart to a different chart type will preserve the PivotTable connection or allow every series combination; chart options can depend on the PivotChart structure and Excel version.

Common problems and fixes

The curve is flat or far above the columns: check that expected counts, not raw probability densities, are plotted. The formula above multiplies each bin probability by the sample size.
Counts do not add up to the sample size: verify that the bins cover the observed minimum and maximum, that the intervals do not overlap, and that the source range includes all records.
The mean or standard deviation is wrong: confirm that the source cells are numeric and that the range excludes headers. Consider whether STDEV.S or STDEV.P fits the purpose.
The PivotTable will not group: inspect the source column for blanks, text, errors, or mixed data types, correct them, and refresh the PivotTable.
The bars look unlike a histogram: use equal-width bins, order the centers numerically, and reduce the column gap width. A category chart does not show the continuous spacing of an XY chart.

Rounding the expected counts for display is optional, but keep the underlying values unrounded for charting. Also remember that NORM.DIST describes a normal model using the mean and standard deviation you supply; it does not estimate whether that model is suitable. A chart can make a mismatch visible, but decisions about data quality or statistical conclusions need context.

Frequently Asked Questions

Can Excel make a bell curve PivotChart automatically?
No. Excel can create a PivotChart from a PivotTable, but it does not automatically add a normal-distribution series. Summarize the observed bins and use a helper table with a regular combo chart for the overlay.

Is a bell curve the same as a histogram?
No. A histogram uses bars to show observed frequencies in numeric intervals. A bell curve is a smooth mathematical distribution, commonly a normal distribution. A histogram may look bell-shaped, but that does not make it a normal curve.

Should I use STDEV.S or STDEV.P?
Use STDEV.S when your observations are a sample used to describe or estimate a broader population. Use STDEV.P when the data represent the whole population you intend to describe. The choice affects the calculated curve, so state the assumption when reporting results.

Why does the normal line not touch the tops of the bars?
The line gives expected counts under the model, while the bars show actual counts. Random variation, sample size, bin width, and bin placement all affect their alignment. A mismatch is not by itself proof of an error or a failed model.

Can I keep slicers and still show a normal curve?
Yes, but ensure the curve uses the same filtered observations as the bars. A normal series calculated from the full dataset will not be a fair comparison with counts filtered to a subset. Update the helper calculations to respond to the same selection or use a separate report design that keeps both summaries synchronized.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.