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 has 5 main 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: Simply a title of the chart. Try to keep it descriptive and short as much as possible.
- Vertical Axis: The axis representing the measured values, also known as the y-axis.
- Horizontal Axis: The axis that includes 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
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. In addition to its own suggestions, Excel also provides several options and different menus to customize your chart. Let’s take a look at these methods.
Double-clicking on any item pops up side panel of Excel which provides options for selected element. Please keep in mind that, once the side panel is open, you don’t need to double-click again – selecting it from the chart area is enough.
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)
With Excel 2013, charts have started to display shortcut buttons. You can add/remove elements, apply predefined styles and color sets and filter values in respective order with a few clicks.
Another neat 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. Charts follow the same process. 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. Tthe Format tab provides more generic options that are common with other objects.
Waterfall Chart Customizing Tips
Preset Layouts and Styles
Try preset layouts or styles to improve visualization of your chart. Excel will also give you suggestions for this purpose.
You can find styling options in the Design tab or in brush icon of Chart Shortcuts. Here are some examples:
Changing chart type
You can change the type of your chart any time from the Change Chart Type dialog. Although, most of the chart types has multiple variations, Excel provides only a single type for Waterfall charts.
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, you can 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.