Introducing the Waterfall chart—a deep dive to a more streamlined chart
Last month, we introduced six new chart types that will be available as part of Office 2016. Today, we will show you how to take advantage of the Waterfall chart, one of the most popular visualization tools used in small and large businesses. With this new chart, what used to take complicated and multiple iterative steps can now be done in a few quick clicks. And since the Waterfall chart is built natively into Excel, you automatically get all the rich and intuitive customization, styling and formatting capabilities you’ve come to expect with the Excel charts.
Waterfall charts are ideal for showing how you have arrived at a net value, by breaking down the cumulative effect of positive and negative contributions. This is very helpful for many different scenarios, from visualizing financial statements to navigating data about population, births and deaths.
Microsoft 365
Best-in-class productivity apps with intelligent cloud services that transform the way you work.
We added the sample workbook used to create the charts, which you can download here.
Getting started with the Waterfall chart
A classic example for Waterfall charts is visualizing financial statements. The example below illustrates how a Waterfall chart can visually display an income statement, also known as a profit and loss statement:
There are multiple ways to insert a Waterfall chart into your worksheet: the Recommended Charts tab and the All Charts tab. You can also insert a Waterfall chart directly from the ribbon.
Using the Recommended Charts feature in Excel helps you find the most effective and visually aesthetic chart for your data. Based on the range of data you have selected, the Recommended Charts feature suggests the charts that will best display your data. The Waterfall chart is recommended when the data has a column of category text, a mix of positive and negative values, and no more than a few empty value cells. The ideal dataset size for recommending charts is anywhere between 8 to 20 values, and given its most popular use case of financial statements, currency-formatted data will favor Waterfall charts. In this example, the parenthetical notation, i.e. ($2,412), is a variation of our currency formatting and denotes a negative value, -$2412.
To start, select your data and then under the Insert tab click the Recommended Charts button. The list of recommended charts is displayed. Select the Waterfall recommendation to preview the chart with your selected data.
The All Charts tab allows direct insertion of Waterfall charts.
You can also use the ribbon to insert the Waterfall chart regardless of the data’s characteristics. Because we know that this is a popular chart, we dedicated a spot in the Chart gallery with a Waterfall icon. Additionally, Stock Charts are also available under this drop-down because of the financial context associated with both chart types.
After the new Waterfall chart is created, notice that the order in which your data is shown in your table is preserved in the chart. In this example, Gross Revenue will always be the first column since it is the first data point, followed by Rev Adjustments, and so on, until Net Income—the last data point and therefore the last column.
Using subtotals as a visual checkpoint in the chart
To truly make our Waterfall chart understandable, we want to set subtotals of certain accounts. Subtotals, in this case, denote balances. In any Waterfall chart, you might set a subtotal to show the absolute value rather than as a floating value relative to the previous column. In this example, the line item accounts—Net Revenue, Gross Income, Operating Income and Net Income—are all totals that behave like checkpoints in understanding our financial statement. Currently the chart characterizes these accounts as increasing cash inflow, resulting in a positive skew.
To set a subtotal, right click the data point and select Set as Total from the list of menu options. We designed Waterfall charts so customers never have to make edits to their data in the Excel worksheet. Everything can be done in the chart.
Any column can be set as a total, but if the previous columns do not add or subtract to this total, then the connector line—the line bridging each column—will not be flat, rather resulting in a slanted or skewed connector line. The Waterfall chart’s effectiveness lies in the trust that all the accounts and columns will ultimately be balanced. If a column is mistakenly set as a total, the steps to correct the chart are the same as setting a total. Select the incorrect total column, and when it is the only item selected, right click and then click Clear Total. The example below shows a skewed connector line and how to clear the total to correct it.
After adding subtotals, we can easily see the breakdown between Gross Revenue to Net Revenue, or Net Revenue to Gross Income, and so on. Setting subtotals makes your visualization cleaner and easier to read and digest.
Customizing for maximum impact
Depending on the context of your chart, you may want to change the colors of the different types of values to bring more clarity and impact to the chart details that are most important. For example, in most financial contexts, the color red signifies loss in cash or assets. One way to change the chart’s color is on the Design tab, by opening the Change Colors gallery. Here you can select a color scheme that is automatically populated with themes suited to your document.
If you’d like to customize which colors correspond to Increase, Decrease or Total, you can rearrange the palette on the Page Layout tab and then click the Change Colors button in the Themes group. At the bottom of the gallery, select the option Customize Colors to open the Create New Theme Colors gallery. From there you can select the Accent 1, 2, 3, etc. colors to change your chart colors. However, keep in mind any changes will affect the entire theme of the document beyond the chart—including other charts you may have created in the workbook.
Data labels is another visual tool as part of your Waterfall chart that can be customized to help improve effectiveness. By default, we designed data labels so they are added in the direction of the column, which intuitively follows the progression of the Waterfall chart. However, if you prefer a more minimalistic chart, it is easy to remove the data labels—just click on any of them and press delete or backspace. You can additionally remove only select data labels by clicking twice, to focus on one data label, and press delete or backspace. The end result creates a Waterfall chart that only emphasizes the important points.
Using the Waterfall chart beyond financial analysis
The Waterfall chart can apply beyond the financial context. The chart is also perfect for highlighting the positive and negative contributions that ultimately derive the net value or outcome of any data. Below is an example of using the waterfall chart to effectively visualize year-over-year population changes by plotting the births and deaths, along with the net growth.
Data for year-over-year changes in population
After quickly setting all population category items as subtotals, we now have an effective visualization, which easily depicts the contributions of births and deaths to the total population. The Waterfall chart adds another layer of information beyond what a typical clustered column chart would show.
Get started now!
We’ve shown you just two examples of Waterfall charts and there are many more relevant features waiting to be explored.