A waterfall chart is a data visualization tool that shows how positive or negative values in a data series change in a sequential manner. A common scenario is to display the cumulative effect of income and expenses on net income. The values can be distributed either time-based or category-based. In this guide, we’re going to show you how to create a waterfall chart in Excel.
Waterfall Chart Basics
A waterfall chart mainly consists of 5 sections:
- Plot Area: This is where the graphic representation takes place. A waterfall chart visualizes values as columns. Each column starts where previous column ends.
- Chart Title: The title of the chart.
- Vertical Axis: The axis representing the measured values, also known as the y-axis.
- Horizontal Axis: The axis that contains the categories of the data, also known as the x-axis.
- Legend: The legend is an indicator that helps distinguish data series from one another.
Insert a waterfall chart in Excel
Start with selecting your data. Include the data label in your selection for it to be recognized automatically by Excel.
Activate the Insert tab in the Ribbon and click on the Waterfall Chart icon to see the chart types under category. Click the Waterfall chart to create your chart.
Clicking the icon inserts the default version of the chart. Let's now take a look at some further customization options.
Customize a Waterfall Chart in Excel
Ways of customizing a waterfall chart
You can edit most chart elements. Let's take a look at some of these options.
Double-Clicking
Double-clicking on any item pops up side panel of Excel which provides options for the selected element. Please keep in mind that, once the side panel is open, you don't need to double-click again - selecting the next item on the chart area will switch the options menu to that item.
Side panel includes element specific options, as well as some generic ones, like coloring and effects.
Right-Click (Context) Menu
Right-clicking an element displays context menu with advanced options. You can modify basic style properties, like changing colors, delete items, or activate the side panel for more options. To display the side panel, choose the options which starts with Format string. For example; Format Plot Area… in the following image.
Chart Shortcuts (Plus and Styles Buttons)
In Excel 2013 and newer versions, charts display shortcut buttons. You can add/remove elements, apply predefined styles and color sets and filter values with a few clicks.
Another handy feature is that you can see the effects of your selections on the fly, without actually applying them. For example; in the following image, the mouse is on the Axis Titles item and we can see the labels on our chart.
Ribbon (Chart Tools)
Whenever you activate a special object, Excel adds new tab or tabs to the Ribbon. You can see these chart-specific tabs under Design and Format. While the Design tab contains options to add elements, apply styles, modify data, and modify the chart itself. The Format tab provides more generic options that are common with other objects.
Briefly; the chart tabs in the Ribbon is the only place where you can find all options that are mentioned before.
Waterfall Chart Customizing Tips
Preset Layouts and Styles
You can find styling options in the Design tab or in brush icon of Chart Shortcuts. Here are some examples:
Applying a Quick Layout:
Changing colors:
Update Chart Style:
Switch Row/Column
Excel assumes that vertical labels are categories, and horizontal labels are data series by default. If your data is designed in reverse order, you can normally easily shift the labels with a single click. However, this is not the case for Waterfall charts. Switch Row/Column button in Chart Design tab is disabled.
Move a chart to another worksheet
Charts are created in the same worksheet as the selected data. If you need to move your chart into a new sheet or another sheet, use the Move Chart dialog. To open the Move Chart dialog you can click its icon in the Design tab or in the right-click menu for the chart itself. Please keep in mind you need to right-click in an empty place in chart area to see this option.
In the Move Chart dialog, you have 2 options:
- New sheet: Select this option and type a name to create a new sheet under specified name which includes your chart.
- Object in: Select this option and select a name of an existing sheets in the dropdown input to move your chart to that sheet.
