Waterfall charts in Excel - Dashboard Guide

Dashboard Mode - Waterfall charts

Goal

Understand how different factors increase or decrease a starting value to reach a final total using a waterfall chart.

Sample Data

Step	Value
Starting Balance	1000
Sales	300
Returns	-100
Expenses	-200
Other Income	150
Ending Balance	1150

Dashboard Components

• KPI Card: Starting Balance shows =B2 which is 1000.
• KPI Card: Ending Balance shows =B7 which is 1150.
• Helper Table: Calculates intermediate totals for waterfall bars:
  =SUBTOTAL(109,$B$2:B2) copied down from row 2 to 6 to get running totals.
• Waterfall Chart: Built using the Step names and the helper table running totals to show increases and decreases visually.

Dashboard Layout

+----------------------+----------------------+
| Starting Balance KPI  | Ending Balance KPI   |
+----------------------+----------------------+
|                      Waterfall Chart             |
|                                                  |
|                                                  |
+--------------------------------------------------+

Interactivity

Filters can be added to select specific steps to display in the waterfall chart. When a step is filtered out, the helper table and chart update to reflect the new running totals and bars.

Self Check

If you filter out the Returns step, what happens to the Ending Balance KPI and the waterfall chart?

Answer: The Ending Balance KPI remains the same (1150) because it is a fixed value, but the waterfall chart and helper table update to show the running totals without the Returns decrease, so the visual bars change accordingly.

Key Result

A waterfall chart dashboard showing how starting balance changes step-by-step to ending balance with increases and decreases.