When analyzing financial data, simply showing the starting revenue and the final profit often leaves out the most important part of the story: what happened in the middle. If you need to explain how various positive and negative factors (like sales, operating costs, taxes, and unexpected fees) impacted a starting value to reach a final total, a standard bar chart is insufficient.
The best visual tool for this specific scenario is a Waterfall Chart. It uses floating “bridge” columns to visualize the cumulative effect of sequentially introduced positive and negative values. For years, creating a waterfall chart in Excel required complex workarounds with invisible stacked columns. Fortunately, Microsoft eventually introduced it as a native, one-click chart type.
Preparing Your Data
A waterfall chart relies heavily on how your data is structured. It must be organized chronologically or logically, flowing from a starting point, through various changes, to an ending point.
Set up two columns in your spreadsheet. Column A should be your labels, and Column B should be the values.
Crucially, negative impacts must be entered as negative numbers (with a minus sign).
- Gross Revenue: 150000
- Cost of Goods: -45000
- Marketing: -12000
- Q2 Sales Boost: 25000
- Taxes: -18000
- Net Profit: 100000
Inserting the Waterfall Chart
Once your data is cleanly organized, generating the chart takes seconds.
- Use your mouse to highlight all the data in your two columns, including the headers.
- Click the Insert tab on the top ribbon.
- In the “Charts” group, look for the icon that resembles small, cascading vertical rectangles (the Waterfall icon). Click it.
- Select Waterfall from the drop-down menu.
Excel will instantly drop a massive chart onto your spreadsheet. You will see columns going up for positive numbers, and floating columns dropping down for negative numbers.
The Critical Final Step: Setting the Totals
When Excel first generates the chart, it treats every single number as a floating change. If you look at your “Net Profit” bar at the far right, it is likely floating high up in the air, rather than starting from the bottom axis.
You must tell Excel which columns are the “Subtotals” or “Totals” so they anchor to the ground.
- Click on the “Net Profit” column in the chart. (Click it twice slowly to ensure you have selected only that specific column, not all the columns).
- Right-click the isolated column.
- From the context menu, select Set as Total.
The column will immediately drop down, anchoring to the zero line on the X-axis. This visually connects all the floating positive and negative “bridge” columns, perfectly illustrating how the initial Gross Revenue was chipped away and boosted until it settled at the final Net Profit.