Overview
This lecture explains how to create waterfall (bridge) charts in Excel using two methods: the legacy stacked column approach and the built-in Waterfall chart feature in newer Excel versions.
What is a Waterfall Chart?
- Waterfall charts, also called bridge graphs, visually display changes between values (e.g., EBIT from prior to current year).
- Positive drivers are shown in green and negative drivers in red, providing a clear view of variance analysis.
Method 1: Stacked Column Chart (Older Excel Versions)
- Prepare separate columns: initial value, final value, positive changes, negative changes, and invisible bars.
- The chart is built as a stacked column chart with floating bars on invisible bars (set to "no fill" and "no outline").
- Positive/negative bars are colored green/red and stacked over appropriately calculated invisible bars.
- Adjust chart formatting: set axis limits, remove the legend, color-code bars, and add data labels for clarity.
Method 2: Built-In Waterfall Chart (Excel 2016/2019/365)
- Arrange data in two columns: labels and values (including start, increases, decreases, and end).
- Insert the waterfall chart via Insert → Charts → All Charts → Waterfall.
- Set start and end values as totals by right-clicking and selecting "Set as Total".
- Format increase, decrease, and total bars using green, red, and blue colors, respectively.
- Adjust axis limits, add a chart title, and remove the legend if desired.
Key Terms & Definitions
- Waterfall Chart — A graphical display of changes in value (e.g., profit) across periods or categories.
- Stacked Column Chart — A chart type where values are stacked in columns to show sub-totals and total.
- Invisible Bar — A column in a stacked chart with "no fill" used to position floating bars.
Action Items / Next Steps
- Practice creating both types of waterfall charts in Excel.
- Experiment with chart formatting, color coding, and axis settings to enhance clarity.
- Prepare a sample variance analysis dataset for hands-on practice.