Concept
1. Scatter — relationship between two measures
Each dot = one item (store, product, salesperson); x = one measure, y = another. Insert → Scatter.
Example: stores by Orders (x) vs Avg Order Value (y). Questions it answers: do busy stores also sell bigger baskets? Which store is an outlier?
Tips: label interesting dots (data label → Value From Cells → store names); add a trendline (right-click series → Add Trendline) and show R² only if your audience understands it; don't connect the dots.
Scatter needs two numeric columns; the first selected column becomes x.
2. Histogram — distribution of one measure
How are order values spread? Insert → Statistic charts → Histogram (Excel 2016+). Format Axis → Bin width (e.g. 50,000) or number of bins.
Practice data (16 orders): 0–50k: 7, 50k–100k: 5, 100k–150k: 3, 150k–200k: 1. Most orders are small; a few big ones drive the total.
Older Excel or a pivot: group the values into bins (Module 2 Lesson 3) and draw a column chart with gap width 0–10%.
Bin width changes the story — try two or three widths before choosing.
3. Waterfall — how we got from A to B
Insert → Waterfall (Excel 2016+). Shows a starting total, increases and decreases, and an end total.
Capstone revenue bridge FY2024-25 → FY2025-26 (₹ crore, rounded — steps may not add exactly):
| Step | Value |
|---|---|
| FY2024-25 revenue | 26.57 |
| East | +0.56 |
| North | +1.00 |
| South | +0.94 |
| West | +0.44 |
| FY2025-26 revenue | 29.50 |
After inserting: click the first and last bars → Format Data Point → Set as total, otherwise they float. Use for profit bridges (revenue − costs − tax), budget vs actual, or cash flow.
4. Funnel — stage-by-stage drop-off
Insert → Funnel (Microsoft 365 / 2019+). Each bar is a stage; widths shrink as people drop out.
Online store example: Visitors 20,000 → Product views 8,000 (40%) → Add to cart 1,600 (20% of views) → Checkout 640 (40%) → Orders 480 (75%). Overall conversion 480 ÷ 20,000 = 2.4%. The biggest leak is views → cart.
Show stage-to-stage % in a helper column; the funnel itself shows only counts.
5. Availability
Histogram, Pareto, Box & Whisker, Waterfall, Treemap, Sunburst: Excel 2016+ (Windows and Mac). Funnel and Map: Microsoft 365 / 2019+. None of these can be pivot charts — build them from cells, or from formulas pointing to a pivot or dynamic array.
Common mistakes
Scatter with category text on the x-axis (Excel numbers them 1, 2, 3…). Histogram with one huge bin width that hides everything. Waterfall totals floating because "Set as total" wasn't applied. Funnels for data that isn't a sequence of stages.