Microsoft Excel · Analysis and charts · Intermediate
Use statistical charts for distributions and change
Build a histogram and a waterfall from complete synthetic data, checking bin counts and total endpoints.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: A histogram groups individual observations into bins; a waterfall explains how increases and decreases bridge an opening and closing total. Choose the chart for the question rather than using one visual for both distributions and change.
Scope and sample data
Use English Excel for Microsoft 365 desktop on Windows. These modern chart procedures are not instructions for Excel 2013. Start a new XLSX workbook. The examples use six observations and a separate four-row bridge; they do not represent a statistical survey or financial advice.
On a sheet named Distribution, put Minutes in A1 and the values 1, 2, 2, 3, 4, and 8 in A2:A7. There are six numeric records, their sum is 20, and their mean is 20/6, approximately 3.3333. Preserve the raw values beside the chart so a reader can inspect the small sample.
Build and check a histogram
- Select A1:A7 and choose Insert > Insert Statistic Chart > Histogram.
- Right-click the horizontal axis and choose Format Axis.
- Set a Bin width of 2. Set the underflow bin to 0 and overflow bin to 6, making the boundaries explicit rather than relying on automatic choices.
- Give the chart a title such as Six observed processing times. Make clear that the vertical measure is frequency, not the total number of minutes.
With these boundaries, no observations are at or below 0; the interval above 0 through 2 contains three observations; above 2 through 4 contains two; above 4 through 6 contains none; and above 6 contains one. The counts must sum to six. A value on the upper boundary belongs in that bin: the two occurrences of 2 belong together, not in different intervals.
Change A7 from 8 to 4. The overflow count becomes zero and the above-2-through-4 count becomes three. The count remains six; the sum becomes 16. This detects a chart that failed to update or accidentally omitted the last record. Chart labels may abbreviate boundaries, so retain the stated bin settings in the accompanying explanation.
Explain change with a waterfall
On a new sheet named Bridge, enter:
Stage Amount
Opening 100
Added 20
Removed -10
Closing 110
Select A1:B5 and choose Insert > Insert Waterfall or Stock chart > Waterfall. The opening and closing rows are totals, not additional movements. Select the Opening data point, right-click, and choose Set as Total. Repeat for Closing. Select an individual point, not the entire series.
The bridge should start at 100, rise to 120, fall to 110, and finish with a total anchored at zero at height 110. Title it Opening amount plus two changes. Its arithmetic is 100+20−10=110; summing all four source values would double-count the closing total.
Change Added to 30 and Closing to 120. The intermediate high becomes 130 and the closing total becomes 120. A waterfall does not calculate your closing source cell automatically when that cell contains a constant; use =SUM(B2:B4) in B5 if you want the closing amount to follow the inputs.
Troubleshooting and storage
If the last bar floats above the baseline, check Set as Total. If frequencies do not reconcile, check the selected source and numeric data types. A box-and-whisker chart answers a different question about quartiles and spread; it is not needed to complete these two checks.
Save as XLSX and reopen to check chart types, bin settings, and total markers. Use SUM for reconciliation rather than trusting a visually convincing picture.