8.8 Waterfall and Funnel
- Waterfall (Excel 2016+) – how a starting value becomes an ending value through ups and downs.
- Funnel (Excel 2019 / Microsoft 365) – stages that shrink (app visits → cart → checkout → delivered).
Waterfall example (fictional, Pune, ₹ lakh):
| Step | Value |
|---|---|
| Oct Sales | 5.0 |
| New customers | +0.8 |
| Diwali basket growth | +0.9 |
| Cancellations | -0.4 |
| Returns | -0.2 |
| Nov Sales | 6.1 |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Vikram Rao) are made up for this lesson on waterfall and funnel.
Steps in Excel
- Select the table › Insert › Charts › Insert Waterfall, Funnel, Stock, Surface or Radar Chart › Waterfall.
- Click the Nov Sales bar twice (select that one point) › right-click › Set as Total. Do the same for Oct Sales if needed.
- Funnel: stages and counts (Visits 50,000 → Cart 12,000 → Checkout 8,500 → Delivered 7,900) › Funnel.
Ravindra Bagale's Tip
In a waterfall, if the last bar isn't "Set as Total", it also floats up as just another change – many students' charts look wrong. Set the start and end bars as totals. Check that the waterfall's total adds up (5.0 + 0.8 + 0.9 − 0.4 − 0.2 = 6.1).
Ravindra Bagale's Tip – मराठी
Waterfall मध्ये शेवटचा bar "Set as Total" न केल्यास तो पण एक बदल म्हणून वरती तरंगतो – बऱ्याच students चा chart चुकीचा दिसतो. Start आणि end bars ला Set as Total करा. Waterfall मध्ये total बरोबर येतोय का (5.0 + 0.8 + 0.9 − 0.4 − 0.2 = 6.1) ते check करा.
Ravindra Bagale's Tip – हिंदी
Waterfall में आख़िरी bar को "Set as Total" न किया तो वह भी एक बदलाव की तरह ऊपर तैरता है – बहुत से students का chart गलत दिखता है. Start और end bars को Set as Total करो. Waterfall में total सही आ रहा है या नहीं (5.0 + 0.8 + 0.9 − 0.4 − 0.2 = 6.1), check करो.
Worked example — waterfall and funnel
Why
Priya Deshmukh in Pune and Anna Kowalski abroad both need a clear, repeatable way to use waterfall and funnel on a Maple Bakery Co sheet without breaking totals.
What
Waterfall And Funnel is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for waterfall and funnel.
- Enter a small made-up value for Priya Deshmukh using € where money appears.
- Add a second value for Anna Kowalski using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once waterfall and funnel is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of waterfall and funnel on a blank sheet for Pearl Textiles.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Priya Deshmukh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Sophie Martin.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply waterfall and funnel to a 5-row made-up Pearl Textiles table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with waterfall and funnel.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as PearlTextil_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total billable hours (Easy): Add up Billable hours in D2:D11.
- Average rate (Easy): Average of the Rate per hour (₹) column, rounded to 2 decimals.
- Rows in Cloud (Easy): How many rows have Category/group "Cloud"? (column B)
- Largest billable hours (Easy): The largest value in D2:D11.
- Billable hours for Cloud (Medium): Total of the Billable hours column where Practice is "Cloud".
- Cloud in Chennai (Medium): Total of the Billable hours column where Practice is "Cloud" and City is "Chennai".
- Best in Cloud (Medium): Largest E2:E11 value where B is "Cloud".
- Value of row 2 (Medium): Multiply the first-row Billable hours by the first-row Rate per hour (₹).
- Look up Noah Wilson (Medium): The Billable hours for "Noah Wilson" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Cloud (Hard): Share of D2:D11 that is "Cloud", rounded to 3 decimals.
- Total "Billable hours" for Testing (Hard): Add up the "Billable hours" column for the rows where "Practice" is "Testing".
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
