Spreadsheets

Waterfall chart: income to savings in Excel

Build a waterfall chart from monthly income through category spending to savings in Excel, set the last bar as a total, and use a stacked-column fallback.

Editorial illustration for Waterfall chart: income to savings in Excel

List your income as a positive number, each spending category as a negative number, and a final row for what is left. Select the table, choose Insert, Waterfall, then right-click the final bar and pick Set as Total. Excel draws income as the starting bar, each category as a step down, and savings as the closing bar.

What data does the chart need?

Here is a hypothetical month. Expenses are typed as negatives so that the running total falls.

ItemAmount (₹)
Income85,000
Rent-22,000
Groceries-9,500
Transport-4,000
Utilities and phone-4,500
Eating out and shopping-8,000
Loan EMI-12,000
Left over=B2+SUM(B3:B8)

The last row works out to 85,000 - 60,000 = ₹25,000. Check the arithmetic: 22,000 + 9,500 + 4,000 + 4,500 + 8,000 + 12,000 = 60,000. Note that Left over is what remains after listed spending; it becomes savings only when you actually move it.

How do I make the native waterfall chart?

  1. Select both columns, from Income to Left over.
  2. On the Insert tab, open the Waterfall or Stock menu and choose Waterfall. (It needs Excel 2016 or a later version.)
  3. Click the Left over bar once to select the series, click it again to select just that bar, right-click and choose Set as Total.
  4. Do the same for Income if you want it drawn from zero as a total column.
  5. Remove gridlines, add data labels, and colour increases and decreases differently in the legend.

Why does my last bar float instead of reaching the baseline?

Because it was treated as a normal step and not a total. Excel adds the value to the running balance rather than drawing it from zero. Set it as a total and the bar stands on the axis. If the final value does not equal the running total, the chart will still draw the number you typed, so verify the subtraction before presenting it.

What if my Excel has no waterfall chart?

Older versions can fake it with a stacked column chart and an invisible base series.

StepBase (no fill)Visible bar
Income085,000
Rent63,00022,000
Groceries53,5009,500
Transport49,5004,000
Utilities and phone45,0004,500
Eating out and shopping37,0008,000
Loan EMI25,00012,000
Left over025,000

Each Base figure is the running total after that expense: 85,000 - 22,000 = 63,000, then 63,000 - 9,500 = 53,500, and so on. Insert a Stacked Column chart from both series, right-click the Base series, choose Format Data Series and set Fill to No fill. Reduce gap width to about 50% so the bars sit close together.

What should I put in each step?

  • Five to eight steps read well. Combine small items into one category such as Other.
  • Keep fixed costs (rent, EMI) in a consistent order from month to month.
  • If you have irregular income, use the same chart for each month and compare shapes, not just numbers.
  • Give the chart a title with the month and unit, like April, ₹.

Where do the category totals come from?

Use the same totals you would put on a monthly summary sheet, taken from your transaction table with SUMIFS. If your records are built from uploaded statements, SpendixAI reports can be downloaded as CSV and pasted into the table above.

Checklist before sharing

  1. Confirm income minus all steps equals the final bar.
  2. Check that expenses are negative and income is positive.
  3. Mark the total bar clearly, and label the period.
  4. Colour the income bar and the left-over bar differently from the spending steps.
  5. Keep the table beside the chart so exact amounts are one glance away.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports