TjiIn this article, I will show how to create a Waterfall chart with multiple series in Excel. The default waterfall chart feature in Excel 2016 and later versions can be used to create a waterfall chart with just one series. However, there are times when we might want to make a waterfall chart that incorporates multiple series.
In this article, I will show how we can create a waterfall chart with multiple series by utilizing the stacked column chart feature across all Excel versions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What Is a Waterfall Chart in Excel?
A Waterfall chart is a type of graph in Excel that helps you see how different positive or negative values add up over time. It shows the overall impact of these values as they are introduced one after the other in a sequence. It essentially represents the running total. This graph shows how the running total changes over time and the effects of each constituent or item on the running total.
Typically, the chart begins with an initial value, and each column that follows shows the result of successively adding or subtracting values. Positive values are represented by columns that rise above the baseline, while negative values are shown as columns that fall below the baseline.
A typical single-series waterfall chart is given below. First, let’s see the dataset where the cash flow of a bank account is shown.
- Based on the cash flow, we created a waterfall chart.
How to Make a Waterfall Chart with Multiple Series in Excel: 3 Easy Steps
In this section, I will show you how to make a waterfall chart with multiple series in Excel in just 3 steps. Before jumping into the steps, let’s look at the dataset with which we will work.
In the dataset, we can see that there are four series for four regions: North, South, East, and West. In the last column, we have the Total of the four series/zones in a particular category. One thing to notice here is that we put all the revenue categories on top, calculated the Total Revenue, and then below it, we put all the Expense categories (Salaries, Rents, and Utilities). It is essential to put all the positive and negative values in groups for multiple series datasets.
Now, using this dataset, we will produce a Waterfall chart. Now, let’s see the steps one by one.
Step-1: Insert a Base Column
As we know, the waterfall chart will have different bases for each column or category. To put the base values of all the categories in the waterfall chart in their corresponding positions, we need to specify the base value for each category. Hence, we insert a column to store the base value.
- Now, we need to add value to the base of each category. The base of Starting Balance is always 0. So, we put 0 in C5.
- Now, the base of Online Sales will be the sum of the Base and Total in the Starting Balance Hence, we put C5+H5 in cell C6.
=C5+H5
- Now, the Store Sales and Advance Order will also have the base value of the sum of Base and Total of the previous row. So, we use the Fill Handle to autofill cells C7 and C8.
- Now, the Total Revenue row will have a base value of 0, as it is the sum/subtotal of all the previous revenue categories.
- In this way, we get all the base values for each revenue category. Now, we will calculate the base value of Expense categories such as Salaries, Rents, and Utilities. For the first expense row (Salaries), the base value will be the difference between the Total Revenue (H9) and Total Salaries (H10).
=H9-H10
- Now, the base value of the next row, Rents, will be the difference between the base value of Salaries (C10) and the Total of Rents (H11). This is because the expenses are subtracted from the running total.
- Similarly, the base value of Utilities will be the difference between the base value of Rents(C11) and the Total of Utilities (H12).
- Finally, the base value of Net Profit will be 0 as it is the final Running Total.
- In this way, we calculate the base value for all the categories.
Step-2: Create a 2-D Stacked Column Chart
In this step, we will insert a 2D Stacked column chart. To do that, first, select the entire table except the Total column. Then, go to the Insert tab, and from the Charts group, select the 2D Stacked Column chart.
- As a result, you will get a stacked chart like this below.
As it doesn’t look like a Waterfall chart, we need to customize it so that we can turn it into one.
Step-3: Formatting the Stacked Column Chart into Waterfall Chart
First of all, we need to remove the Base stacks from the chart. To do that, we will first double-click on any of the Base stacks on the chart.
- As a result, the Format Data Series window will open up on the right side. From the window, go to the Fill Series option and select No fill and No line in the Fill and Border options, respectively.
- As a result, your chart will look like a waterfall chart.
- Now, we can change further formatting such as the chart title, Color of the axis, and delete the Base legend to beautify the waterfall chart. After performing some beautification, our final waterfall chart looks like this:
Things to Remember
- While creating a waterfall chart with multiple series using a default 2D stacked column chart, you must put all the positive values (such as revenues, profits, etc.) together in one group and all the negative values (such as loss, insurance, compensations, etc.) in another group.
- While calculating the base value, you must remember that revenue items will add up to the running total, and expense items will be subtracted from the running total. Hence, the formula for calculating the base value will be different for expense items and revenue items.
Frequently Asked Questions
- What are the other names for waterfall charts?
The waterfall charts are also known as the Flying Bricks Chart, Mario Chart, or Bridge Chart.
- What is the limitation of the waterfall chart?
One of the limitations of a waterfall chart is that it can be challenging to compare the various segments without a common baseline. Even with labels, it can be challenging for a viewer to identify the true differences between the bars.
- Can I use the default Excel waterfall chart for multiple series?
Unfortunately, we can’t. However, we can customize the 2D stack column to display it as a waterfall chart with multiple series.
Conclusion
This concludes the article on Excel waterfall chart with multiple series. Here, I tried to give a general idea on how we can modify the 2D stacked column chart to present it as a waterfall chart. If you find it helpful, please share this article with your friends. Furthermore, do let us know in the comment section if you have any other questions. Finally, please visit Exceldemy for more interesting Excel articles.
