How to Add Total in Waterfall Chart Excel

A waterfall chart is one of the best ways to tell a financial story, showing exactly how an initial value is affected by a series of positive and negative changes. But while Excel’s built-in waterfall chart can get you started, making it show a final total or even subtotals isn't always straightforward. This guide will walk you through exactly how to set up your data and create a polished waterfall chart in Excel, complete with the total and subtotal bars you need.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Waterfall Chart Anyway?

Think of a waterfall chart as a visual "before and after" story. It starts with a total on the left, then shows a series of floating bars that either go up (positive changes) or down (negative changes), and often ends with a final total on the right. This makes them incredibly useful for things like:

• Visualizing a profit and loss statement (Starting Profit → Revenue → Expenses → Final Profit).
• Explaining changes in your sales pipeline (Last Month's Pipeline → New Deals → Lost Deals → Won Deals → This Month's Pipeline).
• Tracking project budget changes (Initial Budget → Added Scope → Savings → Final Cost).

The "floating" bars are the key. They make it easy to see the individual impact of each item, rather than just showing the running total. The main challenge in Excel is that creating these floating bars - and especially the solid "total" bars that start from the bottom axis - requires a specific data setup.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Structure Your Data Correctly (The Most Important Part)

The secret to a great waterfall chart in Excel isn't about chart formatting, it's all in the data setup. We are going to use a stacked column chart to build our waterfall manually because it offers more flexibility for totals and subtotals than the standard waterfall chart type.

Let's use a simple monthly business income example. Imagine we start with an opening cash balance, have some sales, pay some expenses, and want to see our closing balance.

Instead of a simple two-column table, you'll need five columns for this method:

1. Category: The labels for your chart's x-axis (e.g., Opening Balance, Sales Revenue, Marketing Costs).
2. Amount: The actual positive or negative values. This column helps with calculations but won’t be plotted directly.
3. Base: This is an invisible "spacer" column that will push our colored bars up, making them float.
4. Increase: For all your positive values (like revenue).
5. Decrease: For all your negative values (like costs).

Here’s how to set up your spreadsheet:

Set Up Your Columns and First Data Row

Type your headers as shown above. Your first row should be your starting value. For this row, the setup is unique:

• Category: "Opening Balance"
• Amount: 25000 (your starting number)
• Base: 0 (The first bar must start from the ground)
• Increase: 25000 (Same as the Amount)
• Decrease: 0

Enter Formulas for Your 'Flowing' Data

Now, let’s add the subsequent transactions. Here is where the formulas do the heavy lifting. Let's add "Sales Revenue" of $15,000.

In the second row (A3:E3), first enter your new category and amount:

• Category: "Sales Revenue"
• Amount: 15000

Now, use formulas for the remaining columns. These formulas can be dragged down for all your P&L items.

For the Increase column (D3): =IF(B3>0, B3, 0)

This formula checks if the value in the Amount column is positive. If it is, it places the value here, otherwise, it shows 0.

For the Decrease column (E3): =IF(B3<0, -B3, 0)

This formula does the opposite. If the Amount is negative, it converts it to a positive number (charts don't handle negative lengths) and places it here.

For the Base column (C3) - The Secret Sauce: =C2+D2-E3

This is the most critical formula. It tells Excel where to start the bottom of the "floating" bar. It works by taking the previous bar's starting point (C2), adding the previous bar's increase (D2), and then subtracting the current bar's decrease (E3). This clever trick stacks the invisible base perfectly underneath the visible bar.

Continue this for all your positive and negative items. For example, add "Marketing Costs" of -$5,000 and "Operating Costs" of -$8,000. Just enter the Category and Amount, then drag down your formulas from C3, D3, and E3. Your data should look something like this:

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Add Your Total (or Subtotal) Bar

Now for the main event. Adding a total requires another special row entry. A total bar, like the starting bar, must start from the axis, not float.

• Add a new row for your total. Let's call it "Closing Balance."
• Calculate the final amount. The amount is simply the SUM of the Amount column (from opening balance to the last transaction). For our example, in cell B6, the formula would be: =SUM(B2:B5)
• Configure the Base, Increase, and Decrease. This is what makes it a total bar:

Your finished table with the total is now ready:

What About Subtotals?

Adding subtotals (e.g., "Gross Profit") works exactly the same way as adding a final total. You just insert it in the middle of your data. Calculate the subtotal amount (SUM of all transactions up to that point), and set its Base to 0. This grounds the subtotal bar to the axis, providing a visual anchor point before continuing with the rest of your items.

Step 3: Create and Format the Chart

With your data perfectly structured, creating the chart is easy.

1. Select your data. Highlight the range from your Category column to your Decrease column, including the headers. Don't include the Amount column.
2. Insert a Stacked Column Chart. Go to the Insert tab, click Chart, and choose Stacked Column.

You'll immediately get a chart that looks something like a waterfall, but we need to format it to create the floating effect.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Making the 'Base' Bars Invisible

This is the essential trick. The blue bars at the bottom represent your Base column. We need to make them disappear.

1. Right-click on any of the Base bars (they are usually the bottom series, colored blue by default).
2. Click on Format Data Series.
3. In the panel that appears, go to the Fill & Line (paint bucket) icon.
4. Under Fill, select No Fill.

Just like that, the Base bars vanish, leaving your Increase and Decrease bars floating in place. Your waterfall is now functional!

Step 4: Polish Your Waterfall Chart for Presentation

A functional chart is good, but a beautiful and clear chart is better. Here are a few final tweaks:

• Adjust an individual column: Your starting and ending total should reflect that they are totals instead of sequential data. You can click once on that Ending column to select the entire Series. A second time to select that individual bar on your selected series. Then, right-click to Format Data Point. Here you can adjust colors so totals or subtotals stand out.
• Add Connector Lines: Connector lines automatically draw the visual path from the top of one bar to the next.
• Cleanup Your Legend: Since the Base series is invisible, you can delete it from the legend. Simply click on the word "Base" in the legend box and press Delete.
• Add Data Labels: Right-click on your Increase and Decrease bars and select "Add Data Labels" to show the values of each change, making your chart easier to read at a glance.

Final Thoughts

Setting up your data correctly with separate columns for the Base, Increases, and Decreases is the real secret to mastering waterfall charts in Excel. Once you understand the logic - especially setting the Base to zero for your starting, total, and subtotal bars - you can create compelling financial visuals that clearly explain the story behind the numbers.

This manual process in Excel gives you total control, but it can become tedious, especially if you're constantly updating data from different marketing, sales, or finance tools. When you're ready to get out of the spreadsheet trenches, we built Graphed to automate this entirely. You can connect your data sources like Shopify or your CRM and simply ask a question like, "Create a waterfall chart showing last month's revenue changes ending with a final total," and our AI handles all the painful data structuring and chart-building in seconds.