The art of data visualization can help transform complex numbers into insightful, easily understandable information. One way to achieve this is by using a waterfall chart in Excel. This visual tool enables you to break down the total change into its individual parts, like a waterfall, hence the name. Let's explore how to make a simple waterfall chart in Excel step by step.
Before we dive into the creation process, make sure you have the Microsoft Office suite installed on your computer. This article uses Excel 2013, but the steps are similar in other recent versions.
Setting Up Your Data
The first step is to organize your data. A waterfall chart compares the cumulative total of series items. This means that each category should have both its positive and negative contributions toward the total, not just the final sum.
Let's take an example: You want to visualize the monthly revenue of a company. Your data might look like this:
| Month | Revenue | Expenses |
|---|---|---|
| Jan | 50000 | 15000 |
| Feb | 60000 | 20000 |
| Mar | 75000 | 12000 |
Transforming Your Data
To prepare your data for the waterfall chart, you'll create a new column for the cumulative total. Here is the same table, but with the cumulative total added as the first column:
| Month | Previous Month Cumulative Total | Revenue | Expenses | This Month Cumulative Total |
|---|---|---|---|---|
| Jan | - | 50000 | 15000 | 35000 |
| Feb | 35000 | 60000 | 20000 | 70000 |
| Mar | 70000 | 75000 | 12000 | 103000 |
Setting up your data this way exaggerates the total change, but it makes the individual components easier to understand.
Using Excel's Built-in Waterfall Chart
Excel 2016 and newer versions have a built-in waterfall chart feature:
- Select your data, including the headers.
- Click on the "Insert" tab.
- In the "Charts" group, click on the "Waterfall" button.
- Choose the "Waterfall" layout.
The resulting chart should look something like this:
Customizing Your Chart
Of course, a default chart is only a starting point. Excel offers a variety of customization options to make your waterfall chart truly your own.
Adding a Title
Adding a title to your chart helps readers understand its purpose. Here's how:
- Click anywhere in your chart to activate the "Design" tab.
- In the "Chart Layouts" group, choose a layout that includes a chart title (e.g., "Add Chart Element | Chart Title").
- Type in your title, and then adjust the font, color, and other formatting options as needed.
Switching Rows and Columns
Excel's built-in waterfall chart switches months and totals, so they're not in the same order as the data. If you'd like to swap them, you can:
- Click anywhere in your chart.
- In the "Design" tab, click on "Switch Row/Column".
Following these steps, you'll have a clean, well-organized, and informative waterfall chart that effectively communicates your data. Now you can use this new skill to create compelling visuals that help drive informed decision-making. Happy charting!