How to Set a Data Point as a Total in Excel

Ever tried to show how a starting value is affected by positive and negative changes in Excel? It’s a common business need - visualizing how starting revenue for the quarter is impacted by sales, refunds, and operating costs. While a simple bar chart falls short, Excel has the perfect, though sometimes overlooked, tool for this: the waterfall chart. This guide will walk you through creating a waterfall chart and, most importantly, show you how to set any data point as a definitive "total" or "subtotal" so your story is clear, compelling, and accurate.

What Even Is a Waterfall Chart?

A waterfall chart (sometimes called a bridge chart) is a type of data visualization that shows a running total as values are added or subtracted. It’s perfect for understanding the cumulative effect of a series of positive (gains) and negative (losses) values. Think of your monthly bank account statement: you start with an opening balance, add your paycheck (a gain), and then subtract rent, groceries, and bills (losses) to arrive at your ending balance.

In the chart, positive and negative values are shown as “floating” columns that start where the last one left off. This makes it incredibly easy to see exactly how you got from a starting number to a final one. The problem is, without a little tweaking, Excel doesn't automatically know which of your data points are supposed to be solid columns anchored to the baseline - in other words, your totals.

Step 1: Prep Your Data for Success

Before you even think about clicking the "Insert Chart" button, structuring your data correctly is the most critical step. Excel needs a simple list of labels and a single corresponding column of numerical values. Increases should be positive numbers, and decreases should be negative numbers.

Let's imagine you're analyzing a small business's quarterly performance. Your data in Excel should look something like this. Notice how expenses are entered as negative numbers.

Here's our sample dataset:

A few important things to note here:

• Structure: It's just two columns. Column A has the text labels, and Column B has the values. Don't add extra columns or complex layouts.

• Totals are Pre-Calculated: You need to calculate your subtotals and final totals yourself. In our example, "Net January Income" would be the sum of everything above it (50000 + 25000 - 7000 - 12000 = 56000). The "Final Balance" is the sum of all preceding values. You can use Excel's SUM formula right there in the cell to do this easily. This feels weird at first, but it is necessary for the chart setup.

Step 2: Create Your Basic Waterfall Chart

Now that your data is perfectly structured, creating the initial chart is the easy part.

1. Click and drag to highlight your entire data range, including the headers (in our example, that would be cells A1 through B8).

2. Go to the Insert tab on Excel's ribbon.

3. In the Charts group, click on the "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" icon (it looks like a mini waterfall chart).

4. Select Waterfall from the dropdown menu.

Excel will instantly generate a chart. It will look almost right, but you'll immediately notice a problem. The columns for "Starting Balance," "Net January Income," and "Final Balance" are probably floating in mid-air just like the others. This is because Excel defaults to treating every data point as a change (an increase or a decrease), not as a static total. Don't worry, this is expected!

Step 3: The Main Event - Setting a Data Point as a Total

This is where we turn that confusing floating chart into a crystal-clear financial story. We’re going to tell Excel which of those columns are totals, which will anchor them to the zero axis.

Setting the First Total (Starting Balance)

Your first value is almost always a total.

1. Click once anywhere on the chart's columns to select the whole data series. You'll see all columns get highlighted.

2. Now, click once more on only the "Starting Balance" column. It's important to do two separate single clicks. If you double-click too fast, Excel might open the wrong menu. When you do it right, only the first column will have selection handles on it.

3. Right-click on that selected column and choose Format Data Point from the context menu.

4. A pane will open on the right side of your screen. Simply check the box that says "Set as total."

Instantly, you'll see that "Starting Balance" column drop down to the horizontal axis. Success!

Setting a Subtotal and the Final Total

The process is exactly the same for any other totals or subtotals you have.

1. Following the same method, click twice (slowly) on the "Net January Income" bar to select only that column.

2. Right-click it, select Format Data Point, and check the "Set as total" box. You'll see it drop down to the axis, representing the subtotal at that point in time.

3. Finally, repeat the same steps for your "Final Balance" column. Click twice to select it, right-click, choose Format Data Point, and check "Set as total."

Now your chart makes perfect visual sense. Blue columns (by default) show increases, orange shows decreases, and the gray totals show you where you stood at the beginning, middle, and end of the period.

Step 4: Making Your Chart Look Professional

With the totals set correctly, the final step is to clean up your chart for presentation.

• Change Colors: The default colors are okay, but you can adjust them. Right-click any of the "Increase" columns, select "Fill," and choose a different color (like green). Do the same for the "Decrease" (red is common) and "Total" (a neutral gray or blue works well) columns.

• Add Data Labels: Click the "+" icon at the top right of the chart and check the box for Data Labels. This adds the exact monetary value to each column, making it easier to read at a glance.

• Refine the Title: Give your chart a clear, descriptive title, like "Q1 Financial Performance" instead of the generic "Amount."

• Connector Lines: By default, thin gray "connector lines" show the flow between columns. If you find them distracting, you can click on any column, go to Format Data Series, and uncheck "Show connector lines."

More Practical Use Cases

While we used a simple P&L statement, you can use this technique for countless scenarios:

• Sales Pipeline Analysis: Start with 'Deals at Start of Quarter', then add 'New Deals', subtract 'Deals Lost', and end with a 'Deals at End of Quarter' total. This is great for showing sales team performance.

• Inventory Tracking: Show 'Starting Inventory', add 'New Stock Received', subtract 'Items Sold', and finish with an 'Ending Inventory' total.

• Website Traffic Breakdown: Start with 'Total Users Last Month', add 'Gains from Organic Search', 'Gains from Paid Ads', subtract 'Loss of Repeat Visitors', and arrive at 'Total Users This Month'.

Anytime you need to tell a story about how individual positive and negative factors contribute to a final result, the waterfall chart with properly set totals is your best friend in Excel.

Final Thoughts

You've now seen how to build a waterfall chart and specifically designate data points as totals, giving you a clear way to visualize a sequence of positive and negative values in Excel. It bridges the gap between raw data and a compelling financial narrative, helping stakeholders see exactly how you got from A to Z.

Manually building these kinds of reports in Excel week after week can become a major time drain, especially when you're pulling data from different places like Google Analytics, Shopify, and your ad platforms. At Graphed, we built a tool to eliminate that manual work. You just connect your data sources once and ask questions in plain English, like "Show me a waterfall chart of our revenue vs. ad spend this month," and get a live, interactive dashboard instantly. It keeps your data up-to-date automatically and puts the focus back on insights, not on wrangling spreadsheets.