Create Waterfall Chart in Excel With VBA

A Waterfall Chart is used to visually illustrate cumulative effects of sequential positive and negative values, often for financial data like revenue, expenses, and net profit. Since Excel 2016 introduced a built-in Waterfall Chart, we will use VBA to create a Waterfall Chart dynamically for earlier Excel versions as well.

1. Understanding the Waterfall Chart

A Waterfall Chart consists of:

• Starting Value: The first column (e.g., « Opening Balance »).
• Positive and Negative Changes: Columns representing increases (green) and decreases (red).
• Ending Value: The last column (e.g., « Closing Balance »).
• Bridges: The cumulative flow of values.

Since Excel does not provide built-in Waterfall Charts before Excel 2016, we will use Stacked Column Charts and format them manually.

2. Data Structure for the Waterfall Chart

We need a structured dataset:

• Base Column: Helps position floating bars.
• Increase Column: Positive values.
• Decrease Column: Negative values converted to positive.

3. VBA Code to Create the Waterfall Chart

This VBA macro:

• Reads data from an active worksheet.
• Processes data into the required format.
• Creates a stacked column chart.
• Applies colors for increases (green) and decreases (red).
• Removes the base series from visibility.

VBA Code

Sub CreateWaterfallChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chartWaterfall As Chart
    Dim lastRow As Long
    Dim rngCategory As Range, rngBase As Range, rngIncrease As Range, rngDecrease As Range
    ' Set the worksheet
    Set ws = ActiveSheet
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Define data ranges
    Set rngCategory = ws.Range("A2:A" & lastRow) ' Categories
    Set rngBase = ws.Range("C2:C" & lastRow) ' Base values
    Set rngIncrease = ws.Range("D2:D" & lastRow) ' Increase
    Set rngDecrease = ws.Range("E2:E" & lastRow) ' Decrease
    ' Add a new chart
    Set chartObj = ws.ChartObjects.Add(Left:=300, Width:=500, Top:=50, Height:=350)
    Set chartWaterfall = chartObj.Chart
    ' Set chart type
    chartWaterfall.ChartType = xlColumnStacked
    ' Add series
    With chartWaterfall
        .SetSourceData Source:=Union(rngBase, rngIncrease, rngDecrease)
        ' Format Base Series (Make it invisible)
        With .SeriesCollection(1)
            .Format.Fill.Visible = msoFalse
            .Border.LineStyle = xlNone
        End With
         ' Format Increase Series (Green)
        With .SeriesCollection(2)
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80) ' Green
        End With
        ' Format Decrease Series (Red)
        With .SeriesCollection(3)
            .Format.Fill.ForeColor.RGB = RGB(192, 0, 0) ' Red
        End With     
        ' Set Axis Titles
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Categories"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Values"     
        ' Chart title
        .HasTitle = True
        .ChartTitle.Text = "Waterfall Chart"
    End With
    ' Cleanup
    Set ws = Nothing
    Set chartObj = Nothing
    Set chartWaterfall = Nothing
    Set rngCategory = Nothing
    Set rngBase = Nothing
    Set rngIncrease = Nothing
    Set rngDecrease = Nothing
    MsgBox "Waterfall Chart Created Successfully!", vbInformation, "Success"
End Sub

4. Explanation of the VBA Code

• Data Selection:
  • The macro identifies the last row (lastRow) for dynamic range selection.
  • It assigns each column (Categories, Base, Increase, Decrease) to a VBA Range variable.
• Chart Creation:
  • Adds a new ChartObject to the active worksheet.
  • Defines it as a Stacked Column Chart (xlColumnStacked).
• Series Formatting:
  • Base Series (Series 1) is hidden to create the floating effect.
  • Increase Series (Series 2) is set to Green (RGB(0, 176, 80)).
  • Decrease Series (Series 3) is set to Red (RGB(192, 0, 0)).
• Axis and Titles:
  • Labels the X-axis as « Categories » and the Y-axis as « Values ».
  • Assigns the title « Waterfall Chart ».
• User Notification:
  • Displays a message box confirming chart creation.

5. How to Use the VBA Macro

• Open an Excel workbook and enter the data structure mentioned earlier.
• Press ALT + F11 to open the VBA Editor.
• Click Insert > Module and paste the VBA code.
• Run the macro by pressing F5 or from Developer > Macros > Run.

6. Conclusion

This VBA macro dynamically creates a Waterfall Chart in Excel, making it useful for users who don’t have Excel 2016 or later. It ensures:

• Automatic formatting with green/red color-coding.
• Dynamic data handling.
• User-friendly execution via a macro.