Download the free chart template 👉 HERE and follow along.
What is a Waterfall Chart?
Waterfall charts show how an initial value changes over time. They use positive and negative factors. They are also known as bridge or cascade charts. They enable step-by-step data analysis.
Waterfall graphs clearly show how gains and losses affect each other over time. This clarity makes them the top choice for visualizing data shifts.
How Does a Waterfall Chart Work?
A waterfall chart is a powerful tool in business and finance. It shows how positive and negative values combine to make a final result. Here’s how it works:
- Starting Point: The chart starts with an initial value, like total revenue or a project budget. This value is displayed as the first bar on the chart.
- Positive and Negative Changes: After the starting point, the chart shows intermediate steps. Each step is represented by a bar that goes up (positive change) or down (negative change). These bars show how different factors increase or decrease the initial value.
- Cumulative Effect: As you move across the chart, each bar adds to or subtracts from the initial value. This cumulative effect helps you see the overall impact of each factor. The running total at each step shows how these changes add up over time.
- Final Result: The last bar shows the final result, like net profit or total project cost. This final value shows the overall outcome. It considers all positive and negative changes.
Here is an example:
Featured Course
Professional Excel Charts for Business Reports
How to Create a Waterfall Chart in Excel
Step 1: Prepare Your Data
Preparing your data correctly is crucial for creating an accurate Waterfall Chart. This involves setting up a cumulative series and specifying start and end values.
- Start Value: Enter your initial value in cell C7 and reference it in cell E7
- Up and Down Changes: Enter the changes (deltas) for each category in cells C8 to C12.
- Cumulative Series: In cell D7, enter the formula =SUM($C$7:C7) and drag it down to fill the column. This will calculate the cumulative total at each stage.
- Before Series: In cell F8, enter =D7 and drag down to cover all deltas.
- After Series: In cell G8, enter =D8 and drag down to cover all deltas.
- End Value: In cell E13, use the formula =D12 to get the final cumulative value.
Step 2: Create the Waterfall Chart
Now that your data is ready, you can create the Waterfall Chart.
- Select Data: Highlight cells B7 to B13, then hold down the Ctrl-key and select cells E7 to G13, including headers and empty cells for consistency.
- Insert Column Chart: Go to the Insert tab, choose “Insert Column or Bar Chart,” and select “Clustered Column.”
- Change Chart Type: Right-click the selected area, choose “Change Series Chart Type,” and set the “Before” and “After” series to a Line chart.
- Add Up/Down Bars: Select one of the line charts and click the ‘Plus‘ button on the top right of the chart. Check “Up/Down Bars” to visually represent changes.
- Adjust Gap Width:
- Select the Start & End series and reduce Gap Width to a number of your liking (e.g., 100%).
- For the Up/Down bars, select either the Before or After line chart and reduce the Gap Width to 100%.
- Remove Lines:
- Click on the Before series and select “No line” from the Fill & Line Options.
- Do the same for the After series.
Template Download
Excel Waterfall Charts
Step 3: Customize Data Labels
Customizing data labels will make your chart more informative. They show the value of each change directly on the chart.
- Add Data Labels for Start and End Columns: Select the Start and End columns, right-click, and choose “Add Data Labels.”
- Add Data Label Series: In cell H8, enter =MAX(F8:G8) and drag down to create a new series for data labels.
- Add to Chart: Right-click on your graph and select “Select Data.” Click “Add,” pick cell H6 as your series label, and range H7:H13 as your series values.
- Change to Scatter Plot: Right-mouse click on any series and select “Change Series Chart Type…”. Find the Data label position series and change it to a Scatter Plot.
- Position Data Labels: Click on the Data Label Position Series, activate data labels, and position them on top. Format as needed for clarity.
- Set Data Labels to Show Values: Select the data labels, go to the data label options, and check “Value From Cells.” A pop-up will appear. Highlight cells C7:C13 to select the values.
Step 4: Add Connectors
Adding connectors will link the bars in your Waterfall Chart. This will make it easier to follow the flow of data changes.
- Add Connector Series: Add a series for connectors using the cumulative series data. Use cell D6 for the series name and cells D7:D12 for the Y-values.
- Add Error Bars: Select the newly added series, click the Plus button, and check “Error Bars.”
- Format Error Bars:
- Hide the markers.
- Delete Y (vertical) error bars.
- Set direction to ‘Plus‘.
- End style to ‘No Cap‘.
- Error amount to a fixed value of 1.
Step 5: Final Touches
The final touches involve cleaning up your chart to make it visually appealing and easy to understand.
- Adjust Colors and Borders: Click on color options for the X error bars and change the line color to a subtle grey. Ensure the connector line flows well by matching the color border for the Start/End series and the Up and Down bars.
- Remove Y-Axis: Click on the Y-axis and press Delete to remove it.
- Remove Legends and Gridlines: Delete any legends at the bottom and gridlines for a cleaner look.
- Add Title: Add a descriptive title to your chart.
- Adjust Category Axis Labels: To ensure category axis labels move down if your cumulative values become negative, go to the X-axis options and set the Label Position to Low. Increase ‘Distance from axis’ if needed.
Using pre-made Waterfall Chart templates can save you time and ensure accuracy. These templates are easy to use and eliminate the need for complex setup and formatting. Get the complete Waterfall Chart templates here.
Download the Chart Template
Enhance your learning experience by downloading our waterfall chart template. Practice the techniques discussed in real-time and master waterfall charts in Excel with a hands-on example. Download the template here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
