How to Create a Waterfall Chart in Excel? 2 Proven Methods
(Note: This guide on how to create a waterfall chart in Excel is suitable for all Excel versions including Office 365
Excel has a wide variety of charts using which you can represent different types of data. Depending on the statistics and purpose of your presentation, you can choose any one chart from the list of charts.
One of the ways to represent the positive and negative rise and fall of values in a table is by using the Waterfall chart.
In this article, I will tell you how to create a waterfall chart in Excel using 2 easy methods.
You’ll Learn:
Related Reads:
How to Create an Excel Gauge Chart the Easy Way?
How to Create a Funnel Chart in Excel? 2 Useful Ways
How to Save Excel Chart as Image? 4 Simple Ways
What is a Waterfall Chart and Where is it Used?
Before we learn how to create a waterfall chart in Excel, let us see what a waterfall chart is and where it is used.
The waterfall chart is a special type of chart in Excel and is not commonly used. It is mainly used to represent how the data changes either positively or negatively through a series of changes and finally reaching a result.
The first and final bars are attached to the axis, whereas the intermediate data are represented as floating bars. The bars which represent the data are color-coded to indicate the rise or fall in the values.
Since the intermediate data act as a bridge between the first and final data, this chart is also called a Bridge chart.
The waterfall chart finds itself useful in a variety of places. They are predominantly used by sales and retail companies and academic institutions. For instance, visualizing the profit or loss, comparing and contrasting the growth of your business or your competitors, creating dashboards, and showing the rise or fall in the prices of commodities.
How to Create a Waterfall Chart in Excel?
Let us now see how to create a waterfall chart in Excel with an example.
A sales company started with an initial revenue of a certain amount ($4000) at the start of the year. The company submits the turnover made after a year for auditing purposes. They would also like to represent the profits and losses in the year.
In such a case, the waterfall chart helps to portray the data more effectively.
By Selecting the Waterfall Chart [For Excel 2016 and Higher Versions]
In Excel 2016 and later versions, you can easily depict the data in the form of a waterfall chart just by selecting the chart.
- To create a waterfall chart using the data, first, select the data.
- Navigate to Insert. Under the Charts section, click on the Waterfall Chart.
- This populates the Waterfall chart in the center of the sheet.
From the chart, you can see that the increases and decreases in profit are indicated in the form of steps, waterfalls, or bridges. You can also see the increases are represented in blue, the decreases are represented in orange.
Customize the Chart
Start the Bars from the Axis
When you populate a Waterfall chart, only the data at the beginning and end starts from the axis. In some cases, you might also want the data in between to start from the axis.
- To make the bars start from the x-axis, double-click the particular data point or right-click and select Format Data Point to open the Format Data Point pane to the right of the sheet.
- Under the Series option, check the checkbox for Set as total.
Show the Connector Lines
By default, Excel shows the floating data points connected by thin, semi-transparent lines.
- To remove the connector lines, click once on the bars in the graph.
- This opens the Format Data Series pane. In the Series Option, check the checkbox for Show connector lines.
Note: To change the color of any particular type of data, click on the particular legend. And, in the Format Legend Entry dialog box, click on the Fill & Line section and change the Fill color.
Additional Customizations
Once you click on the Waterfall chart, Excel inserts the chart in the center of the spreadsheet. However, there are a couple of additional customizations you can make to the chart.
Move or Resize: The chart you select will populate the center of the worksheet in the default size. Sometimes, the size might not be enough or the chart populating might hide the data. In such cases, you can move or resize the chart by clicking on and dragging the outline and anchor points respectively.
Chart Design Option: You can use the Chart Design ribbon option to change the layout, colors, data, or type of the chart and add additional elements to the chart.
Format Option: To change the chart styles, insert shapes, add alt text, arrange or resize the chart, use the Format ribbon from the menu bar.
Shortcut Customization Options: You can find the Shortcut Customization buttons next to the chart to alter or modify the Chart Elements or the Chart Styles.
Suggested Reads:
How to Create a Step Chart in Excel?
How to Make a Pie Chart in Excel? A Step by Step Guide
How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps
From Stacked Column Chart [For Excel 2013 and Lower Versions]
You can easily select the data and insert a Waterfall chart in versions of Excel 2016 and later. However, if you want to use a waterfall chart in earlier versions or learn an in-depth method to create data for a waterfall chart, use the method below.
With the given data, we need to create additional columns for the data points. Insert three columns (Base, Decrease, and Increase) in between the original values.
The idea behind creating three columns is that the Base column will contain the data as an initial point. The other two columns will contain the positive increasing values and negative decreasing values respectively. Also, let us insert two rows, one at the start of the data set and the other at the end.
Let us now populate the columns by using certain formulas. But before that, let us enter 0 at the start of the Sales Flow column as a starting value.
- First, let us start with the Decrease column. From the Sales Flow column, enter only the negative values in the Decrease column. Fill the rest of the places with 0.
- Another easy way to separate data is by using the IF formula. Enter the formula =IF(E6<=0, -E6,0) and press Enter.
- Now, you can use the drag handle to apply the formula to the remaining cells.
- In the same way, enter the formula =IF(E6>0, E6,0) in the Increase column. And use the fill handle to apply the formula to the other cells.
- Next to fill the base column, leave the first cell and enter the formula =B6+D6-C6 (base+increase-decrease). This acts as the initial point. Now, press Enter and use the drag handle to fill and apply the formula to the remaining cells.
Now that we have all the data in the columns ready, let us now get ready to create the waterfall chart.
- To create a waterfall chart, select the data except for the Sales Flow column.
- Navigate to Insert, and insert the Stacked Column Chart.
- This populates the chart in the center of the sheet.
Now, let us transform the stacked column chart into a waterfall chart.
- Right-click on any data and select Format Data Series.
- Under the Format Data Series pane, click on the Fill & Line icon.
- Under the Fill section, click on No fill, and under the Border section, click on No line.
This gives you the required Waterfall chart.
Customize the Chart
Let us now customize the Waterfall chart.
- You change the chart style from the Shortcut Customize buttons present next to the chart or you can use the Chart Design ribbon to change the overall layout of the chart.
- If you want to change the color of the bridges, you can change them by navigating to the Format main menu and choosing the color from Shape Fill. Or, you can easily change the colors by double-clicking on the bar and choosing the color from the Fill & Line option.
- If you want to change the spacing of the bars or make them wider or smaller, double-click on any of the bridges. This opens the Format Data Series pane. Click on the Series Options and adjust the Gap Width to change the spacing between the bridges.
Also Read:
How to Make a Box Plot Excel Chart? 2 Easy Ways
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make an Excel Bullet Chart – 2 Easy Examples
Frequently Asked Questions
Why can’t I find the Insert Waterfall chart in the Insert ribbon?
The Insert Waterfall Chart can only be found in Excel 2016 and higher versions. If you are using Excel 2013 or earlier versions, you will have to create the data and use the Stacked Column Chart to depict the data manually.
Why is the total data not appearing from the axis?
When you populate a Waterfall chart, only the data at the beginning starts from the axis. To make the bars start from the x-axis, double-click the particular data point or right-click and select Format Data Point to open the Format Data Point pane. Under the Series option, check the checkbox for Set as total.
How to create a waterfall chart in Excel 2016?
To create a waterfall chart in Excel, first, select the data. Navigate to Insert and select the Waterfall chart from Charts. This populates the waterfall chart in the center of the sheet.
Closing Thoughts
The Waterfall chart is best suited to depict the rise and fall of data compared to the original data. The waterfall chart has a variety of functions from corporate to academic presentations.
In this article, we saw how to create a waterfall chart in Excel using 2 easy methods. In newer versions of Excel, you can easily select the data and insert the waterfall chart. However, in older versions, you will have to create the data to insert a stacked column chart and then customize it to create a waterfall chart.
Please visit our free resources center for more high-quality Excel guides.
Ready to take the next step and hone your skills in Excel?
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
