Excel waterfall chart for profit and loss, step by step

You wrote the P&L, the totals tie out, and the leadership team wants the visual. A flat bar chart hides what matters most: how revenue gets eaten on the way down to net income. A waterfall chart shows that journey directly — each bar is one line of the income statement, the running total rises and falls, and the subtotals anchor back to the baseline so the story reads in two seconds.

Excel 2016 and Microsoft 365 ship the chart type built-in. Get the sign convention right and the chart is two clicks away. Get it wrong and the Net Income bar floats in mid-air instead of landing on the baseline — the single most common mistake. Here is the working setup, end to end.

What the chart actually shows in a P&L

A waterfall chart draws a running total as connected columns. Each column is one P&L line; its height is the absolute change, its color signals whether the value added to or subtracted from the cumulative total. Connector lines stitch the top of one column to the start of the next, so the eye follows the path from revenue down to net income without retracing.

The columns map directly to the income statement. Revenue lands first as an increase bar rooted at zero. COGS hangs below it as a decrease bar. Gross Profit is a subtotal that drops back to the zero baseline — so do Operating Income and Net Income. Each operating expense floats in between, taking another bite out of the running total.

That anchoring is what separates a waterfall from an ordinary column chart. A plain column treats every bar as independent; a waterfall makes the relationships visible. Microsoft documents the built-in chart type as available in Excel 2016 and later, in Microsoft 365, and on Mac. Excel 2013 and below need a stacked-column workaround, covered near the end.

For the broader rules on when a chart helps a report at all, our walkthrough on adding charts to Excel reports applies here too.

Lay out the data the way the chart expects

The built-in chart reads a two-column range: line item label, signed amount. Increases go in as positive numbers; decreases go in as negative numbers. Excel uses the sign to color each bar. Get the sign wrong on a single expense and that bar shows up as an increase instead of a decrease — same height, opposite meaning.

Subtotal rows go in as the cumulative value, not as a SUM that the chart re-applies. Excel does not compute Gross Profit from the rows above; you enter the number, and the next step (Set as total) tells the chart to anchor that bar to zero rather than treat it as another delta. The same rule applies to Operating Income and Net Income.

Build the range on a fresh sheet so the chart has nothing else to wrestle with. Headers in row 1, line items running down column A, amounts in column B. A quarterly P&L for a software company would look like this:

That gives Excel the eleven rows it needs to draw eleven bars. If the source numbers come from a transaction ledger, our guide to Excel financial analysis formulas shows the SUMIFS pattern that rolls thousands of journal entries up into these eleven totals automatically.

Insert the chart and anchor the three subtotals

Select A1:B11. Include the header row so Excel knows which column is the label. On the Insert ribbon, the Charts group has a dedicated Waterfall icon under Insert Waterfall, Funnel, Stock, Surface, or Radar Chart. The Mac menu reads the same. Pick Waterfall, and Excel drops a first draft on the sheet in under a second.

That first draft is almost right. Revenue, Gross Profit, Operating Income, and Net Income all float in mid-air at the running-total height instead of anchoring to zero. Only the per-line increases and decreases sit in the correct positions. Four bars to fix, three clicks each.

1. Click any bar once to select the whole data series; click the subtotal bar a second time to select that single data point.
2. Right-click the selected bar and choose Format Data Point.
3. Tick Set as total. The bar drops to the zero baseline and recolors to the neutral total style (gray by default).
4. Repeat for Revenue, Gross Profit, Operating Income, and Net Income.

Revenue often anchors itself because it sits at the start of the running total, but anchor it explicitly anyway — Excel sometimes treats the first row as a delta when the source range includes blank rows or merged cells. Order matters too: the bars are drawn left to right in the order of the data rows, so swap rows in the source range, not in the chart, if the sequence needs to change.

When the Net Income bar still floats

The most common failure mode: every subtotal looks right except Net Income, which hangs in mid-chart at the running-total height instead of touching the baseline. The cause is almost always one of two things, and they fix differently.

Cause one is a sign mistake on an expense row. If COGS got typed as 400,000 instead of -400,000, Excel treated it as an increase, the running total went the wrong way, and the Net Income figure you typed no longer matches the implied cumulative. Audit column B: every expense, including Taxes, must be negative. Other Income stays positive because it adds to operating income.

Cause two is a forgotten Set-as-total tick. The tick is per data point, not per chart. Skip it on the last bar and the chart treats Net Income as one more delta from Operating Income, which leaves it floating instead of grounding it.

COGS         400,000   (positive!)
Net Income   190,000   (not set as total)

COGS        -400,000
Net Income   190,000   (Set as total)

A quick sanity check before sharing the chart: read the cumulative top off the bars from left to right. If the last visible height does not equal the Net Income you typed, the data has a sign error — not the chart. Fix the cell, and the chart updates on its own.

Colors, labels, and connector lines

The default palette uses green for increases, red for decreases, and gray for totals. That reads well on its own; a corporate brand often demands different colors. To repaint a whole series in one click, select the legend entry for Increase (not a single bar) and pick a new fill color from the Home ribbon. The same trick works for Decrease and Total.

Data labels show on every bar by default but at full precision, which clutters the chart with eight-digit numbers. Open Format Data Labels → Number → Custom and use an accounting format that displays values in thousands:

$#,##0,;($#,##0,);"–"

The trailing comma divides the displayed value by 1,000 (so 400,000 renders as $400); the second format group puts negative values in parentheses, the accounting convention; the dash renders zeros as an em-dash so they do not visually compete with real numbers.

Connector lines stitch the top of each bar to the start of the next. They are on by default and worth keeping — they are the visual rail that makes the chart read as a running total at all. For a minimal version in a slide deck, open Format Plot Area → Series Options and uncheck Show connector lines. For a P&L chart that lives inside a larger workbook, our guide to dynamic Excel dashboards shows how to wire the source range to a slicer so changing the quarter redraws the bars and labels in one motion.

What to do on Excel 2013 or earlier

The built-in waterfall arrived in Excel 2016. On older builds, you fake it with a stacked column chart and three helper columns: Base (invisible spacer), Up (increases), Down (decreases). Each row populates exactly one of Up or Down; Base raises the floating bar to the right starting height. The amounts column stays in B; the helpers go in C, D, and E.

=IF(B2>=0, MAX(0, SUM($B$2:B2)-B2), MAX(0, SUM($B$2:B2)))   ' C (Base)
=IF(B2>=0, B2, 0)                                            ' D (Up)
=IF(B2<0, -B2, 0)                                            ' E (Down)

Each Base formula assumes B2:B11 holds your signed amounts and drags down with the rows. Insert a stacked column chart on the A, C, D, E ranges; set the Base series fill to No Fill so the spacer disappears; color Up green and Down red. Subtotal rows still need a manual touch: set their Base to zero and put the cumulative value in Up so the bar climbs back from the baseline.

The stacked workaround does redraw automatically when the source numbers change — the helper formulas recompute and the chart picks up the new heights. The brittle part is the subtotal rows: every time you insert or delete an expense line, the manual Base-of-zero rows shift and need a re-check before sharing.

Build the data with a header row, signed amounts, and explicit subtotal values typed (never SUM-ed into) column B. Insert → Waterfall. Click each subtotal bar twice — once for the series, once for the single point — Format Data Point, tick Set as total. If a bar still floats after that, check the signs in column B before touching the chart. Eight times out of ten the data is wrong, not the chart. The whole sequence takes under five minutes once the data is clean, and it replaces a slide of bullet points with one image that explains itself.