How to Build a Waterfall Chart in Excel
A waterfall chart is useful when you need to show how a starting figure changes through a series of gains and losses. Instead of forcing readers to add and subtract a long list, the chart connects each step visually and ends with the total. This guide explains the method with a monthly-income example, including the formulas, chart conversion, formatting choices and a practical way to read the result.
What a waterfall chart shows
A waterfall chart — also described as a waterfall graph or bridge graph — displays positive and negative values across a period while keeping the opening and closing figures visible. It works well for company profit, inventory, overall sales and other measures where the path between two totals matters as much as the totals themselves.
Choose this format when a simple list would hide the cumulative effect. The floating columns show each movement in context, so a reader can see whether growth came from several small increases, one large gain, or a sequence of losses that later recovered.
When to use one
The clearest uses are:
- Tracking sales or inventory through successive periods
- Breaking profit into positive and negative contributors
- Comparing earnings across products in sequence
- Illustrating employee growth over time
- Showing how a budget changes from an opening amount to a final amount
- Highlighting how product value moves during a period
Before building one, decide the question the chart must answer. If the question is only which period was highest, a standard column chart may be simpler. If the question is how the starting value became the ending value, a waterfall chart is the better fit.
Step 1: Set up the data table
The same steps apply on PC and Mac. The example uses monthly income, with positive and negative months. Adapt the labels and values to your own measure, but keep the same four-part structure: a value column, a Base column, an Up column and a Down column.
Checklist:
- Enter the period names and income values in a table.
- Add Base, Up and Down columns beside the value column.
- Put positive values in Up and negative values in Down.
- Add Start and End rows so the opening and closing totals are explicit.
- Leave the Income Flow column out of the chart selection later, because it is a helper column rather than a plotted series.
The Base column is the invisible support for each floating bar. It records where the next movement begins, which is why the bars appear to climb from or descend from the previous total instead of all starting at zero.
Step 2: Add the Base, Up and Down formulas
Enter each formula in the first relevant cell, then drag the fill handle through the rest of that column. The cell references below match the monthly-income layout in the example, so adjust the row numbers if your table starts elsewhere.
- Down column: select C7 and enter =IF(E7<=0, -E7, 0), then fill down.
- Up column: select D4 and enter =IF(E4>0, E4,0), then fill down.
- Base column: select B4 and enter =B3+D3-C4, then fill down.
These formulas separate the two directions of movement. Down converts a negative value into the height needed for a falling segment, Up keeps only positive values, and Base carries forward the running position after the previous Up and Down amounts. If a column shows values in the wrong direction, check that the source values and the formula references still point to the same rows.
Step 3: Insert a stacked column chart
Select the table data you want to plot, including the headers but excluding the Income Flow helper column. From the Insert tab, choose Chart and then Stacked Chart. Excel will first display a stacked column chart rather than a finished waterfall chart.
That intermediate result is expected. The stacked chart contains all three series, including Base. The next step hides Base visually while retaining its positioning role in the data.
Step 4: Hide the Base series
Select the Base series, right-click it and choose Format Data Series. In the formatting pane, set Fill to No Fill and set Line to No Fill as well. The Base segments then become invisible, leaving only the Up and Down segments floating at the correct heights.
Remove Base from the legend by right-clicking Base in the legend and choosing Delete. At this point the chart has the waterfall structure, even if it still needs clearer colour and labels.
Customise colour, spacing, titles and labels
Colour is the fastest way to distinguish direction. Format the Down series with one fill and matching line colour, then format Up with a different colour. In the example, Down uses blue and Up uses green. Give the Start and End columns a separate colour so the totals do not get confused with ordinary movements.
To reduce the gaps between columns, double-click a column, open Format Data Series, choose Options, and set Series Overlap to 100% and Gap Width to 15%. Those settings are presentation choices, not requirements, but they make the sequence easier to follow.
For titles and labels, select the chart and use Chart Options in the formatting palette to name the chart. To show values, right-click a column and choose Add Data Labels. Use Format Data Labels to adjust label position, font colour and font size. You can also remove zero values and delete the legend if the colours and labels already explain the chart.
How to read the result
Read the finished chart in sequence: opening value, each rise and fall, then closing value. In the monthly-income example, the negative months are April and May, followed by July and August. That pattern prompts useful questions rather than an automatic conclusion.
Ask whether late-spring and late-summer dips recur, whether holidays or seasonal demand explain them, whether the budget already allows for them, and whether a one-off decline could become a trend. Walk the relevant team through the visual sequence instead of relying only on a written summary; seeing where the total drops can make the follow-up discussion more concrete.
Template and related resources
If you prefer not to build the table from scratch, {{OWNER_TEMPLATE_AVAILABILITY}} — file: {{OWNER_TEMPLATE_FILE_NAME}}. Replace the example values with your own figures, check that Start and End still reconcile, and then apply the colour and label choices above.
For broader project planning, the Gantt chart template hub and the Gantt chart Excel template provide related starting points for schedules and project plans.
Frequently asked questions
What is a waterfall chart?
A waterfall chart, also called a waterfall graph or bridge graph, places positive and negative changes in sequence between a starting value and an ending value. That structure makes cumulative movement easier to scan than a list of isolated numbers.
When is a waterfall chart useful?
Use it when the story is cumulative: sales or inventory moving over time, profit being built from gains and losses, product earnings compared step by step, employee growth, budget changes, or product value changing across periods.
How do I create a waterfall chart in Excel?
Prepare Base, Up and Down columns, add the three formulas described below, insert a stacked column chart, hide the Base series, then colour the Up and Down series differently. The detailed checklist below gives the exact cell references used in the monthly-income example.
How should I analyse a waterfall report?
Read the chart from left to right: start value, each rise or fall, then the end value. Look for clusters of declines, ask whether they repeat seasonally or are new, and check whether budgets already allow for them. In the monthly-income example, April and May and then July and August are the negative periods to investigate.