Creating a waterfall chart in Excel is an effective way to visually compare a starting value to an ending value, and to track the cumulative effect of intermediate values. This chart, also known as a cascade chart, is useful for displaying the sequential contributions to a total or the resulting impact of a series of positive and negative values. In this blog post, I will provide a step-by-step guide on how to make a waterfall chart in Excel. From setting up your data to customizing the chart design, this tutorial will teach you how to effectively create and use a waterfall chart in Excel. This guide is designed for users of any skill level and will provide the user with the tools to create a visually appealing and effective waterfall chart. Follow along to learn the basics of making and using a waterfall chart in Excel.
When to use a waterfall chart in Excel
Depending on the type of projects you’re working on and the industry you work in, there are several situations where you might find it useful to create a waterfall chart in Excel. The following are some of the most typical justifications for using waterfall charts as visual aids:
What is a waterfall chart in Excel?
A waterfall chart shows how positive and negative values affect a data set’s overall end value. They depict an initial value, how positive and negative factors affect it, and how the final value changes as a result of those changes. They are also known as bridge charts or cascade charts. In a waterfall chart, the front and back ends’ starting and ending values line up with the horizontal axis. The fluctuating values represent how the initial value has changed positively and negatively and how those changes affect the overall sum.
Because they make it simple to depict how values change over time, waterfall charts have gained popularity in recent years. They were first used by management and consulting firms to enhance presentations made during meetings with staff and clients. With a waterfall chart, you can see how different variables affect values over time, particularly in relation to the first variable. For instance, even if months 11 and 12 show positive sales growth, a decline in month 10 could have a significant impact on the final total in a waterfall chart that shows how a company’s sales changed over the course of a year.
How to make a waterfall chart in Excel
To help you create an educational and aesthetically pleasing waterfall chart, consider the following steps:
1. Enter and select your data set
Entering your data set into an Excel table is the first step in creating a waterfall chart. To do this, launch the Excel program and start entering your data values into the various columns marked with labels. When entering values, be sure to use the minus sign (-) to denote when it makes sense to take those numbers out of the chart’s previous values. After entering your data, highlight the entire data set, including your column labels, to select it.
2. Select “Waterfall” chart
The “Insert” tab can be found on the top ribbon of the screen. This is the next step in creating your waterfall chart. Navigate to the top bar’s center to access a selection of boxes with graphics that represent various chart types. Choose “Waterfall” from the drop-down menu after clicking on the box that features a waterfall chart. In your worksheet, this will automatically create a waterfall chart using the information in your initial table.
3. Adjust starting and ending totals
The starting and ending totals may not line up with the chart’s vertical axis when the waterfall chart populates on your spreadsheet, but you can fix this in a few easy steps. Double-click anywhere on your chart to launch the “Format” side pane as a starting point. In the dropdown that appears after you click “Series Options” in the side pane, you can check the box next to the phrase “Set as total” by double-clicking the starting total bar. Excel will align the bars with the vertical axis if you repeat the process for the final total.
4. Adjust labels on your chart
The starting and ending total bars will have the same color as the “Total” label in the charts legend once you select “Set as total” from the format side pane. By selecting the “Chart Title” text box and entering your own title, you can add a title to your waterfall chart to continue formatting it. Click on your chart to reveal “Chart Elements,” then click the “+” tab to adjust the labeled elements of your x axis. You can modify your axis titles, data labels, grid lines, and legend here.
5. Add design touches
When creating your waterfall chart, you might think about making any last-minute changes that will enhance its creativity and increase interest from the intended audience. Double-click the chart to reopen the Format side pane. Then, to make aesthetic adjustments to your chart’s appearance, such as adding borders, modifying shadows, or scaling it or resizing it, click the “Fill & Line,” “Effects,” and “Size & Properties” tabs. To access additional customizable features, such as your charts’ color scheme, layout, or style, navigate to the top ribbon of the screen and select the “Chart Design” tab.
6. Save and share your waterfall chart
Checking accuracy, including value or label errors, is the last step in creating your waterfall chart. By selecting the “File” tab from the top ribbon of your screen after making sure that your chart successfully displays your data set, you can save your work. To save your chart and make it easy to find it later, click “Save as,” then give it a name. You can email it as a PDF attachment or share a live link by selecting the “Share” button in the top right corner of the screen if you want to share it with coworkers or clients.
FAQs about making a waterfall chart in Excel
Here are some frequently asked questions about creating a waterfall chart in Microsoft Excel, along with their responses:
What are some of the non-financial or business uses for waterfall charts?
Waterfall charts are useful for a variety of industries and fields, even though they are most frequently used in the financial and business sectors. A waterfall chart is used to simply show how a total value changes over time based on a set of variables that can be used in a variety of situations. Exam scoring, examining health and medical trends, quality of life metrics, and even as a gauge of how people behave in response to particular variables, like the climate or the economy, are a few non-financial uses for waterfall charts.
Can I add connecting lines between the bars on my waterfall chart?
On their waterfall charts, some people prefer to include connecting lines, or bridges, between the bars. Double-clicking on any location on your waterfall chart will reveal the Format side pane on the right side of your screen. Select the “Series Options” tab, then select the checkbox next to “Show connector lines.” This will mimic the curve of a line graph by adding connecting lines between your bars to show the rise and fall of your total.
Is it necessary to include a legend with my waterfall chart?
When creating a waterfall chart, a legend is not necessary, and many professionals actually prefer to remove the legend before showing their chart to coworkers or investors. Most people who are familiar with waterfall charts are aware of the typical color scheme, in which red or orange denotes a decrease in values and blue denotes an increase. To save space and maintain a professional appearance, you may want to completely omit the legend if your audience is familiar with reading waterfall charts. It might be advantageous to keep the legend if you’d rather use a distinctive color scheme for your bars so that everyone can understand the graph.
How can I move my waterfall chart to a different worksheet on Excel?
Start by right-clicking on a section of the waterfall chart that is empty to display a drop-down menu if you want to move the chart to another worksheet. Next, select “Move Chart” from the drop-down menu at the bottom of the screen to see a menu of options. Select the “New sheet” bubble and type the name of the new Excel worksheet where the waterfall chart will appear if you’re moving it. If you want to move your chart to an existing worksheet, choose the “Object in” bubble, choose the appropriate worksheet from the drop-down menu, and then click “OK” to complete the transfer.
Please be aware that Indeed is not connected to any of the businesses or certifications mentioned in this article.
How to create a waterfall chart in Excel
FAQ
How do I create a waterfall chart in Excel with multiple measures?
Select your data. Click Insert > Insert Waterfall or Stock chart > Waterfall. Additionally, you can create a waterfall chart using the Recommended Charts’ All Charts tab.
How do I create a waterfall chart in Excel 2010?
Based on a starting value, an Excel Waterfall chart can be used to demonstrate the cumulative impact of positive and negative amounts. For instance, display the monthly net cash flow amounts in a waterfall chart to quickly identify the successful and unsuccessful months.
Does Excel have waterfall charts?
Based on a starting value, an Excel Waterfall chart can be used to demonstrate the cumulative impact of positive and negative amounts. For instance, display the monthly net cash flow amounts in a waterfall chart to quickly identify the successful and unsuccessful months.
