Mastering Excel 2013: Step-by-Step Guide to Create Waterfall Charts

Creating a waterfall chart in Excel 2013 can be a powerful way to visualize cumulative data, such as sales performance over time or project costs. This step-by-step guide will walk you through the process, ensuring you create an engaging and informative waterfall chart to enhance your data storytelling.

Before we dive into the steps, ensure you have your data ready. For a waterfall chart, you'll need at least two columns: one for the starting value and another for the positive or negative changes that lead to the final value.

Preparing Your Data

Let's assume you have the following data in cells A1 and B1:

In this example, the starting value is 100, then it increases by 50, decreases by 30, increases by 80, and finally decreases by 50 to reach the final value of 200.

Formatting Your Data

To create a waterfall chart, you'll need to add cumulative sums and format your data accordingly:

1. In cell C1, enter the formula =A1+B1 to calculate the cumulative sum.
2. Copy the formula down to the other cells in column C.
3. Format cells in column B as numbers with no decimal places.

Creating the Waterfall Chart

Now that your data is prepared, it's time to create the waterfall chart:

1. Select the range A1:C5.
2. Click on the Insert tab in the ribbon.
3. In the Charts group, click on Column and then select the Clustered Column chart.
4. Right-click on the chart and select Select Data.
5. In the Legend Entries (Series) list, click on the first series (starting value) and then click Edit.
6. In the Edit Series dialog box, change the series name to Starting Value and click OK.
7. Repeat steps 5 and 6 for the other series, changing their names to Change and Cumulative Sum respectively.
8. Close the Select Data Source dialog box.
9. Right-click on the chart and select Format Selection.
10. In the Format Selection pane, click on Add Data Labels and then Value From Cells.
11. Select the range C1:C5 and click OK.
12. Format the data labels as desired, and your waterfall chart is complete!

Customizing Your Waterfall Chart

To make your waterfall chart even more engaging, consider the following customizations:

Change the Color Scheme

Right-click on the chart and select Change Colors to choose a color scheme that best fits your data story.

Add a Title and Axis Labels

Click on the Design tab in the ribbon, and then click on Add Chart Element to add a title, axis labels, or a legend to your chart.

Creating a waterfall chart in Excel 2013 allows you to effectively communicate cumulative data and help your audience understand the journey from the starting value to the final result. With these steps and customization tips, you're well on your way to creating engaging and informative waterfall charts. Happy charting!