5 Simple Ways to Create a Waterfall Chart in Excel

Why Use a Waterfall Chart?

Waterfall charts excel in visualizing financial statements, project management progress, or any sequential data that builds up from one state to another. Here are compelling reasons to employ waterfall charts:

• Visualizing Profit and Loss: They clearly illustrate how individual items contribute to the net total.
• Project Progress Tracking: Show how each phase or task contributes to the overall project completion.
• Sales Performance: Help in understanding how different segments or regions affect overall sales figures.

Method 1: Stacked Column Chart

The stacked column chart technique is the simplest way to create a basic waterfall chart in Excel. Here’s how:

1. Arrange your data with the initial value, changes, and final value in columns.
2. Select your data range and insert a stacked column chart.
3. Remove the fill for the base column (total) to create the floating effect for changes.

💡 Tip: Ensure your data labels are visible and positioned inside or above the columns to highlight key values at each stage.

Method 2: Using Excel’s Built-in Waterfall Chart

Starting with Excel 2016, creating a waterfall chart has become even easier. Follow these steps:

1. Select your data range including headers.
2. Go to the ‘Insert’ tab, then select ‘Waterfall’ under the ‘Charts’ group.
3. Excel will automatically categorize your data into increases, decreases, and totals.

Remember, Excel identifies decreases by negative numbers. If your data doesn't follow this convention, you might need to manually format the chart.

Method 3: Creating a Waterfall Chart with Formulas

This method provides flexibility by using Excel formulas:

1. Set up your data with columns for Start Value, Increases/Decreases, Total at Each Step.
2. Use formulas to calculate increases/decreases:
   • =IF(A2=0,B2-B1,A2+C1) for the change column.
   • =SUM(C$2:C2) for the cumulative total.
3. Use the stacked column method but with these calculated values to plot your waterfall chart.

🚩 Note: This method requires some knowledge of Excel formulas but offers greater control over chart behavior.

Method 4: Conditional Formatting for Enhanced Visibility

To make your waterfall chart stand out:

1. After creating your chart, select the columns representing increases and decreases.
2. Apply conditional formatting to change the color of columns based on positive or negative values.
3. Use custom formatting for the cumulative totals to distinguish them from changes.

Method 5: Advanced Customization with VBA

For those needing custom waterfall charts beyond Excel’s defaults:

1. Create a macro to automate the chart creation or data manipulation:
2. Here’s a basic macro code to start:

Sub CreateWaterfall()
    ‘Code to create and format a waterfall chart
End Sub

3. Customize the chart’s appearance, scale, and behavior within the macro.

⚠️ Note: Knowledge of VBA is required for this method. It can automate repetitive tasks but might increase the complexity of your workbook.

In summary, waterfall charts are a dynamic and insightful way to visualize sequential changes in data. From basic built-in options to advanced VBA customization, Excel offers multiple avenues to create these charts effectively. Choose the method that best suits your proficiency level and the specificity of your data presentation needs. Each approach allows you to communicate changes in data visually, making it easier for stakeholders to grasp financial movements, project progress, or any cumulative impact over time.

How do I decide which method to use for creating a waterfall chart?

+

Choose based on your familiarity with Excel, the complexity of your data, and the level of customization you need. For beginners, using Excel’s built-in waterfall chart or the stacked column chart method is simplest. For more control or automation, VBA or formulas might be better.

Can waterfall charts show intermediate values?

+

Yes, by customizing the chart, you can show intermediate values. This might require adjusting the chart’s data labels or formatting the columns manually to display values at each step.

Are there any limitations when using Excel’s built-in waterfall chart?

+

The built-in waterfall chart can sometimes be inflexible with data structure or formatting. It assumes positive values as increases and negative as decreases, which might not suit all data scenarios. Additionally, advanced customization options are limited.