What Is Waterfall Chart In Excel?
The Waterfall Chart in Excel is a Column graph that plots the increasing result of data points as a graphical running total when we add or remove data values. Using the Waterfall Chart in Excel, users can analyze how the initial value of a data series gets impacted by the ongoing positive and negative changes. Thus, we use this chart in quantitative and financial analyses.
For example, the following table shows the starting monthly revenue in January, the variations in income from February to May, and the final revenue in June.
Using Waterfall Chart in Excel feature, we can visually review how the monthly revenue fluctuates through a series of changes from January to June.
The above Waterfall Chart in Excel example shows the increasing and decreasing values and the starting and ending revenues in different color columns. If the subsequent value is positive, we can observe that it starts from the level where the previous data point ends. On the other hand, the Waterfall Chart in Excel shows the negative data points as a column moving downwards from the level where the previous data point ends. And as the graph also exhibits connector lines between each column, it gets referred to as the Bridge chart.
Table of contents
Key Takeaways
- The Waterfall Chart in Excel shows how the data series’ starting value varies according to the successive increasing and decreasing values.
- Using the Waterfall chart type in the Insert tab, we can create a Waterfall plot.
- We can create a Waterfall graph manually by selecting the 2-D Stacked Column chart from the Insert tab. But we will have to make the Base transparent, show the initial and ending values in the same color, and the increasing and decreasing values in different colors.
How To Create Waterfall Chart In Excel?
The procedure to create a Waterfall Chart in Excel for a dataset is:
Select the dataset, or the cell range > click the “Insert” tab > go to the “Charts” group à click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” drop-down à select the “Waterfall” option. The Waterfall Chart In Excel is created.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Basic Example
We will learn to create a chart using Waterfall Chart in Excel example.
The following table shows a company’s initial revenue in 2018, the contributions of five small ventures (both positive and negative), and the firm’s final income in 2019.
The Steps to create a Waterfall Chart in Excel are:
Step 1: Click the above table > click the “Insert” tab > go to the “Charts” group > click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” drop-down > select the “Waterfall” option.
Once we click the highlighted Waterfall chart icon, we will get the below graph.
However, we have to format the chart to get the perfect Waterfall Chart in the Excel template required to review data variation accurately.
[Note: Formatting a Waterfall Chart in Excel involves showing the Total, adding chart and axes titles, and changing the graph scale].
The chart legend shows three different column colors, Blue, Orange, and Grey. Blue and Orange denote the small ventures’ positive and negative revenue changes or contributions. On the other hand, there are no columns in Grey, though the legend shows the Total value as Grey.
Step 2: The first and last columns are the Total values, and they should not float. So, to format them, click on the first data point column and double-click it to choose it. We will get the “Format Data Point pane”. Check/Tick the “Set as total” checkbox in the “Series Options” tab of the “Format Data Point” window.
[Alternatively, choose the first column, right-click on it, and select the Set as Total option].
Next, click on the last data point column and repeat the above process.
Once we close the Format Data Point window, we will see the Total values, Initial Revenue – 2018 and Final Income – 2019, in Grey.
Step 3: Change the vertical axis scale to show revenue variations clearly. So, click on the chart area to enable the “Chart Elements” icon, ‘+’ > click the “Axes” right arrow > click the “More Axis Options…” to open the Format Axis window.
The Format Axis window opens as shown below. Next, click on the “Axis Options” drop-down > select the “Vertical Axis” option.
Change the Bound settings in the Format Axis window.
We will set the Minimum value as 45000, as the minimum value in the dataset is 50000.
Close the Format Axis window.
Step 4: Click on the chart area to enable the “Chart Elements” icon, ‘+’ > click the “Legend” right arrow > click the “Bottom” option to move it to the bottom of the plot, as shown below.
Instantly, the “Legend” moves to the bottom of the chart, as shown below.
Step 5: Double-click on the “Chart Title” to modify and change it to “Company Revenue Variations Due to Small Ventures’ Contribution”.
Step 6: Click on the chart area to enable the “Chart Elements” icon, ‘+’ > check/tick the “Axis Titles” checkbox to insert axis titles.
Once we insert the axis titles and resize the chart, the resulting Waterfall Chart in Excel will be:
Thus, we can visually review the effect of the small ventures’ contribution on the initial revenue in 2018 and compare it to the company’s final income in 2019.
Critical Features of Waterfall Chart in Excel
- Floating Columns: All the columns in a Waterfall Chart in Excel template, except the first and last, are floating type. They denote the positive and negative variations according to the first value in the data series.
- Spacers: Sometimes, the columns in Waterfall charts do not start from zero. Instead, the plots use spacers and adjust the column offset by a specific margin.
- Connector Lines: The Waterfall graphs utilize connector lines to show the connection between the columns.
- Color Coding: By default, the Waterfall plots show positive and negative value changes using different color-coded columns to highlight the variations.
- Crossover: When the data points plotted in the Waterfall graph shift across the X-axis, it automatically adjusts to show the value change flow across the axis.
Examples
We will understand the Waterfall Chart in Excel with some advanced scenarios.
#Example 1
The following table shows the initial revenue, net income, and contributions of revenue variation factors.
The steps to create a Waterfall Chart in Excel are:
Step 1: Click the above table > click the “Insert” tab > go to the “Charts” group > click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” drop-down > select the “Waterfall” option.
We already saw the steps in formatting a Waterfall Chart in Excel in the previous section. We can refer to steps 2 to 6 to create the required Waterfall chart.
Step 2: Choose the first column > in the Format Data Point window, go to the “Series Options” tab > check/tick the “Set as total” checkbox.
Repeat the process for the last column too.
So, the Total values, Initial Revenue, and Net Income are not floating columns.
Step 3: Click on the chart area to enable the “Chart Elements” icon, ‘+’ > check/tick the “Axis Titles” checkbox to insert axis titles.
Once we enter the axis titles and update the Chart Title, the resulting Waterfall Chart in Excel will be as shown in the image below.
The above plot shows those factors (Orange columns) which negatively impact the initial revenue and the one (Blue column) positively impacting it. And the last column shows the net income, which we can compare with the initial amount visually.
#Example 2
The illustrations we saw till now will work in Excel 2016 and the latest versions. But suppose we have an older version of Excel or wish to create a Waterfall Chart in Excel manually. Then we can refer to the below illustration.
The following table shows the initial sales and the monthly sales fluctuations.
The steps to create a Waterfall Chart in Excel manually are:
Step 1: Firstly, we will rearrange the above data. Insert three new columns between Columns A and B, Base, Decrease, and Increase. And we also have a new row at the bottom to determine the End value, representing the sales figure for the whole year. Therefore, the modified table will look as shown below. We will start by entering the values.
Step 2: Choose cell C2 and enter the formula =IF(E2<=0,-E2,0)
Drag the formula from cell C2 to C14 using the fill handle. We will get the output shown below.
Step 3: Choose cell D2 and enter the formula =IF(E2>0,E2,0)
Drag the formula from cell D2 to D14 using the fill handle. We will get the output shown below.
Step 4: Choose cell B3 and enter the formula =B2+D2-C3
Drag the formula from cell B3 to B15 using the fill handle. We will get the output shown below.
Step 5: Choose the cell range A1:D15, select the “Insert” tab > go to the “Charts” group > click the “Insert Column or Bar Chart” drop-down > select the 2-D Stacked Column chart type.
We will get the below plot.
Step 6: Now, we will convert the above graph into a Waterfall chart. Right-click on the chart, choose the “Series Base” from the drop-down > select the “Format Data Series”.
In the “Format Data Series” pane > click on the “Fill & Line” tab in the “Series Options” > choose No fill for Fill and No line for Border.
Also, delete Base from the legend.
Step 7: Right-click on the chart, choose the “Decrease” series data point from the drop-down > select the “Format tab from the Excel Ribbon > go to the “Shape Styles” group > click on the “Shape Fill” drop-down and choose the desired color, here, Red.
Likewise, click on an “Increase” series data point and repeat the above procedure to change its color.
Step 8: Show the Total values, Initial and End data points in the same color. Choose them one by one and repeat the procedure to change color. First, Initial Data Points is shown below.
End Data Points is shown below.
Step 9: Right-click on any data point to open the Format Data Series window. In the “Series Option” tab, set the “Gap Width” as 20% to reduce the gaps between the columns in the graph.
Finally, once we update the Chart Title and axis titles, the final Waterfall chart will be as shown in the image below:
Uses Of Excel Waterfall Chart
We can utilize the Waterfall Chart in Excel in the below scenarios:
- When analyzing entities whose quantitative values show a transition, either positive or negative.
- When performing quantitative analysis, such as inventory or performance analysis.
- When tracking contracts, such as those which are newly added and canceled.
- When visually analyzing balance statements.
- When featuring budget changes in a project.
- When developing dashboards.
Important Things To Note
- The Waterfall Chart in Excel graphically displays the collective effect of increasing and decreasing values.
- The Waterfall chart type is available in Excel 2016 version and above.
- In the case of older Excel versions, we can utilize the 2-D Stacked Column chart and modify it to create a Waterfall graph.
- Before creating a Waterfall graph, ensure the source data is accurate and complete.
Frequently Asked Questions
The Waterfall Chart in Excel is found in the Insert tab. The third chart shown below is the Waterfall chart type.
We can use Waterfall Chart in Excel when we need to visualize balance sheets, analyze inventory over a duration, and perform other quantitative analyses.
We can format a Waterfall Chart in Excel using the options available in the “Design” and “Format” tabs in the Excel ribbon, which will appear on the Excel ribbon when we click on the Chart.
Likewise, we can use the Format Data Series and Format Data Point windows to format the Waterfall graph. Check the above article to know how to open these windows.
We can click the three tabs in each window to apply the required format, from changing column colors to adjusting the gap width between the columns in a Waterfall plot.
Download Template
This article must help understand Waterfall Chart in Excel, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to Waterfall Chart in Excel. Here we create Waterfall chart, [Column Graph//Bridge Chart] with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply