Financial analysts, project managers, and operational planners frequently need to communicate how an initial starting balance transforms into a final net figure through a sequence of positive gains and negative deductions. While standard clustered column or bar charts display discrete totals, they fail to demonstrate the cumulative, step-by-step impact of intermediate variables. Google Sheets provides built-in support for waterfall charts (often referred to as bridge charts). A waterfall chart features floating column blocks that begin where the previous column ended, offering an intuitive visual narrative for budget variance, cash flow analysis, and corporate earnings breakdowns.
Structuring Data Correctly for a Waterfall Chart
A successful waterfall chart requires clean, chronologically ordered data. The chart engine relies on alternating positive and negative values to determine whether floating blocks climb upward or drop downward.
Recommended Data Table Layout
Arrange your figures in a two-column structure where Column A contains the category or milestone labels and Column B contains the numerical values:
| Category Label | Amount (£ / $) |
|---|---|
| Starting Gross Revenue | 150,000 |
| Product Sales Growth | 35,000 |
| Cost of Goods Sold (COGS) | -45,000 |
| Operational Overheads | -20,000 |
| Marketing & Ad Spend | -15,000 |
| Tax Liabilities | -12,000 |
| Net Retained Profit | 93,000 |
Note: Deductions and expenses must be entered as explicit negative numbers (e.g. -45000). Do not enter positive numbers with the expectation that the chart will deduce expense status automatically.
Inserting the Waterfall Chart in Google Sheets
Once your tabular data is organised, generate the baseline chart:
- Highlight your data range (e.g.,
A1:B8) including header titles. - Click Insert in the top menu and select Chart.
- The Chart editor sidebar opens on the right side of the screen.
- Under the Setup tab, click the Chart type dropdown menu.
- Scroll down to the Other section and select Waterfall chart.
Configuring Subtotals and Grounded Summary Columns
By default, Google Sheets may treat the final row (e.g., Net Retained Profit) as another incremental addition rather than a grounded summary column that anchors to the zero baseline. You must configure subtotal flags manually:
Step 1: Adding a Subtotal Column Anchor
- In the Chart editor sidebar, switch to the Customise tab.
- Click to expand the Waterfall settings section.
- Locate the Subtotal option. Click Add subtotal.
- Under Subtotal index, select the row index corresponding to your concluding summary row (or intermediate milestones, such as Operating Profit).
- Check the box labelled Show connector lines to ensure subtle visual connector lines link each step together across the bridge.
Customising Visual Formatting and Palette Colours
Standard corporate financial reporting follows universal colour conventions: positive gains appear in green or corporate blue, negative reductions in red or amber, and anchored totals in neutral navy or slate grey.
Step 1: Setting Custom Bar Colours
- Under Customise > Waterfall in the Chart editor:
- Select your preferred colour for Positive columns (e.g., Forest Green
#2e7d32). - Select your preferred colour for Negative columns (e.g., Crimson Red
#c62828). - Select your preferred colour for Subtotal columns (e.g., Deep Navy
#1565c0).
Step 2: Adding Data Labels for Direct Readability
- In the Customise tab, expand the Series section.
- Check the Data labels box. Numerical values will appear directly above or below each floating block.
- Adjust Position to Outside end or Center.
- Customise the label font size and set the text format to bold for executive presentation clarity.
Publishing and Embedding the Chart
To incorporate your completed waterfall chart into executive reporting packages:
- Click the three vertical dots (kebab menu) in the top-right corner of the chart canvas.
- Select Copy chart to paste it as a linked dynamic graphic inside Google Docs or Google Slides presentations. When figures in your sheet update, the linked presentation chart updates with a single click.
- Alternatively, select Download > PNG image or PDF document for static distribution.
Waterfall charts transform dense financial spreadsheets into compelling visual narratives, enabling decision-makers to comprehend variance trajectories instantaneously.