Creating Waterfall Charts in Excel: A Step-by-Step Guide

Waterfall charts, also known as bridge charts, are powerful visualizations used to illustrate how an initial value is affected by a series of intermediate positive and negative values, culminating in a final value. They are especially effective for demonstrating changes in revenue, profit, inventory, or any other metric that experiences fluctuations over time or across different categories.

Excel doesn’t have a built-in waterfall chart type, but you can easily create one using a stacked column chart with a few clever adjustments. This guide will walk you through the process, providing clear instructions and helpful tips.

Data Preparation is Key

Before you begin creating the chart, you need to structure your data appropriately. Your data table should contain the following columns:

• Category: This column lists the individual categories or periods contributing to the overall change. Examples include months, product lines, or departments.
• Increase: This column contains the positive values that increase the initial value. If a category doesn’t have an increase, enter 0.
• Decrease: This column contains the negative values that decrease the initial value. Represent these as positive numbers. If a category doesn’t have a decrease, enter 0.
• Start: This is the crucial column that forms the “floating” bars. Its values determine the starting point of each subsequent bar. We’ll calculate these values.

Here’s an example of how your data might look:

Calculating the ‘Start’ Values

The ‘Start’ column determines the height of the invisible “base” that makes the increase and decrease bars appear to float. Here’s how to calculate it:

1. First Row: The ‘Start’ value for the first row is usually the initial value you’re tracking (e.g., Beginning Balance). In our example, it’s 100.
2. Subsequent Rows: For each subsequent row, the ‘Start’ value is calculated as follows:

   Start (current row) = Start (previous row) + Increase (previous row) - Decrease (previous row)

   In Excel, you would use a formula like this (assuming your data starts in cell A2): =D2+B3-C3, and then drag the formula down to apply it to the rest of the rows.

3. Ending Balance: The ‘Start’ value for the final row (e.g., Ending Balance) should be the calculated ending value, reflecting all increases and decreases. You can manually input the expected final value if needed, which helps verify the accuracy of your data and formulas.

Creating the Stacked Column Chart

1. Select Your Data: Select the entire data range, including the column headers (Category, Increase, Decrease, Start).
2. Insert a Chart: Go to the “Insert” tab on the Excel ribbon. In the “Charts” group, click on the “Column” chart icon. Choose “Stacked Column” from the dropdown menu.
3. Initial Chart Appearance: You’ll now have a basic stacked column chart. It will likely look messy, with the “Start,” “Increase,” and “Decrease” values all stacked on top of each other. Don’t worry, we’ll fix it.

Transforming the Chart into a Waterfall Chart

1. Hide the ‘Start’ Series: This is the key step. Right-click on any of the columns representing the “Start” series in the chart. Select “Format Data Series.” In the “Format Data Series” pane, go to the “Fill & Line” option (the paint bucket icon). Under “Fill,” select “No fill.” This will make the “Start” series invisible, creating the floating effect.
2. Adjust the Gap Width (Optional): You can adjust the gap between the bars for a cleaner look. In the “Format Data Series” pane, go to the “Series Options” icon (the column chart icon). Adjust the “Gap Width” slider. A smaller gap width creates wider bars and a more visually impactful chart. A value between 20% and 50% often works well.
3. Format the Colors: Customize the colors of the “Increase” and “Decrease” series to clearly distinguish them. Common choices are green for increases and red for decreases. Right-click on the “Increase” series columns, select “Format Data Series,” and choose a green color under “Fill.” Repeat the process for the “Decrease” series, choosing a red color. You can also format the “Beginning Balance” and “Ending Balance” columns with distinct colors.
4. Add Data Labels (Optional): Adding data labels can improve the chart’s readability. Right-click on any of the series columns, and select “Add Data Labels.” You can customize the labels’ content and position in the “Format Data Labels” pane. Consider showing the value of each increase and decrease.
5. Customize the Axis Titles and Chart Title: Double-click on the axis titles (horizontal and vertical) and the chart title to edit them. Give your chart a descriptive title that accurately reflects the data it presents. Ensure the axis titles clearly indicate the units of measurement (e.g., “Revenue (USD)”).
6. Remove the Legend (Optional): Since the chart now has distinct colors for increases and decreases, and you might have included labels, the legend may be redundant. Click on the chart, then click the “+” icon that appears next to it. Uncheck the “Legend” box to remove it.

Refining the Waterfall Chart

Here are some additional tips for refining your waterfall chart:

• Consider Using Connector Lines: While not natively supported, you can add line shapes to visually connect the tops of the bars, enhancing the “waterfall” effect. This requires manual drawing and adjustment of lines.
• Dynamic Updates: Ensure that your formulas in the ‘Start’ column are correctly linked to the ‘Increase’ and ‘Decrease’ columns. This will automatically update the chart whenever the underlying data changes.
• Error Handling: If you encounter issues with the chart, double-check your ‘Start’ column formulas for errors. Pay close attention to the signs (plus and minus) in the formula.
• Alternative Chart Types: While a stacked column chart is the most common approach, you can also create a waterfall chart using a combination of column and line charts for more complex visualizations. This is more advanced and requires a deeper understanding of Excel charting.

Conclusion

By following these steps, you can easily create a clear and informative waterfall chart in Excel. These charts are invaluable for communicating complex financial data, project progress, or any scenario where you need to visualize the incremental changes leading to a final result. Remember to prioritize data accuracy and clear labeling to ensure your chart effectively conveys the intended message.