If you’re anything like me, you can’t use waterfall charts enough. They’re the ideal visualization of the addition and subtraction of values from a total over time. In Excel, there’s a really simple way to create waterfall charts.
However, getting/setting total columns? Not so easy.
Total columns are essential to waterfall charts. Yet for some reason, Microsoft decided to make the setting difficult to locate. But don’t worry — once you see where it is, it’s easy. This article shows pictures and examples of how to do so.
There are two easy ways to set totals in Excel waterfall charts: the right-click menu, and the formatting data point settings within the formatting pane. As you’ll see, you should only use the easy right-click option. It’s faster and less cumbersome than the formatting pane.
How to Set the Total: Two Paths
Right-click Option
You simply need to isolate the value or column you want to set as a total by clicking on it. Then, right-click, and navigate down to the section for setting a total as shown in the above picture. We will look at a full example below.
Format Data Point Option
To set a total from the formatting pane, you need to either right-click and navigate to Format Data Point…, or first click on the data point you want to isolate, and navigate to Format>Format Pane>Format Data Point. Either way, it’s much quicker to simply right-click to set as total, as shown on the left.
Use Waterfall Charts, Not Column Charts
Some people are still attached to using column charts for waterfalls because Waterfall charts were only introduced in 2016. You should insist on using the waterfall chart function itself because column charts are less flexible and can eat up time.
Don’t forget, you can access the 67 data skill and concepts checklist for free to ensure you’ve got your bases covered.
Setting Total with Right-Click – Video Example
For the purposes of this example, I’m using the simple dummy dataset shown in the picture below. The first and last rows represent starting and stopping balances, so they should be reflected as totals in a waterfall chart.
To set a total with the right click, you’ll need first to isolate and highlight the column you want to set. You can do this by double clicking on it. Here’s a video that shows how.
Setting Total with Formatting Pane – Video Example
Using the same dataset, here’s a video showing how you can set a total column using the formatting pane.
Happy waterfall charting!
When Should You Use a Total Column in a Waterfall Chart?
In short, totals should always be the starting balance and ending balance of a series of additions and subtractions.
In some cases, you may have a total in between an initial starting and stopping balance. In this case, the total performs both a starting and stopping balance function — stopping for the initial starting balance, and starting for the initial stopping balance.
That’s right, their scope is extremely limited, and so is the scope of waterfall charts in general. That’s why Microsoft has put them on the sideline in favor of more dynamic charts.
What’s the difference between a waterfall chart and a column/bar chart?
Some people get confused when they see waterfall and column charts to represent changes over time, but the difference is very simple. A waterfall chart represents changes (+’s and -‘s) to a total over time, whereas a column chart shows the value of a given metric in each period of time on the x-axis.
They are not the same by any means. An easy way to tell them apart is to look at each column. If all of the columns are touching the x-axis (i.e touching the 0-line), then it’s a column/bar chart. However, if only one column does not touch the x-axis, it’s a waterfall chart.
