Peltier Technical Services, Inc.

Peltier Tech Charts for Excel - Documentation

Stacked Waterfall Charts

Stacked Waterfall Buttons

Standard Edition Ribbon
Stacked Waterfall Button on Standard Edition Ribbon
Advanced Edition Ribbon
Stacked Waterfall Button on Advanced Edition Ribbon

Standard Edition Mac Menu
Stacked Waterfall Button on Standard Edition Mac Menu

Stacked Waterfall Dialog

The stacked waterfall chart dialog is shown below next to a typical data range. If a range has been preselected or if the active cell lies within a valid range, the program inserts the range's address into the range selection box. The program checks the box if it detects labels in the first row of this range.

Stacked Waterfall Dialog

You can specify the width of the gaps between bars (50% means the gap width is 50% of the bar width).

You can select whether to place the chart on the active sheet or on a new sheet inserted in front of the active sheet.

You can also choose whether to show value labels on the chart.

The settings that you've chosen will be selected by default the next time you launch this dialog.

Data Requirements

The Stacked Waterfall program requires a contiguous data range (no multiple areas). The first row contains series names, and if this row is missing, a rwo will be inserted and dummy series names applied. The first column should include categories for the horizontal axis.

The program adds several columns of calculations to the right of the selected data range. If you choose to draw the chart on the active sheet, the program makes sure that the required range is empty. If it is not empty, the program returns to the dialog, with the "new sheet" option selected.

The program does not automatically place any subtotal or total stacked bars on the chart, but you can add a total or subtotal stack by including a row in the data table, with a label for the bar and blanks instead of numerical values.

Program Output

Here is a typical output worksheet from the stacked waterfall program. There are many columns of calculations, and the chart floats above them all.

The yellow cell indicates the value of the smallest bar that will be labeled. It is estimated by the program based on the axis scale of the chart, but you can adjust it as desired. You can hide labels altogether by unchecking the "Show Labels in Bars" checkbox below that.

Stacked Waterfall Program Output

Configuring the Data and Chart

Here is a typical data range and chart. All of the data is increasing and no subtotals or totals are included.

Stacked Waterfall Program Output

If you want totals at the end of the chart, add a line at the bottom of the data with a label in the first column and nothing in the rest of the columns. The program will calculate the totals.

Stacked Waterfall Program Output

If you want intermediate subtotals, insert a line with a label in the first column and nothing in the rest of the column, at the point where you want the subtotal stacked bar. The program will calculate the subtotals.

Stacked Waterfall Program Output

By now you should know the drill if you want subtotals and totals.

Stacked Waterfall Program Output

Here is a simple data range with some increasing stacks and some decreasing stacks. The program adds the blocks upwards or downwards.

Stacked Waterfall Program Output

Subtotals and totals work for positive and negative stacks.

Stacked Waterfall Program Output

Finally, if there are both positive and negative values in a given stack, the program cannot show both directions in a given stack (but see the Split Bar Waterfall Chart). The program simply computes the net change, and plots a neutral gray bar.

Stacked Waterfall Program Output

If a subtotal or total stack has values of mixed signs, then this stack will also be replaced by a neutral gray bar showing the net total or subtotal.

Stacked Waterfall Program Output

Using VBA to Create Stacked Waterfall Charts

You can access the waterfall chart feature from your own VBA code, bypassing the dialog. Use the following function, which returns a chart as the value of the function.

    Public Function PeltierTech_StackedWaterfallChart(ChartData As Range, _
        LabelsInFirstRow As Boolean, GapWidth As Long, _
        Optional NewSheet As Boolean = True, Optional ShowDataLabels As Boolean = True) As Chart

The parameters correspond to options in the dialog. You should familiarize yourself with the dialog, so you understand how the parameters work. The variable types are Excel's cusomary variable types.

For some general hints on using VBA to operate the utility, refer to Using VBA with the Peltier Tech Chart Utility.

Additional Documentation

Contact Peltier Tech

If you encounter a bug, or if you have an idea for a killer feature, contact Peltier Tech and tell us about it. If submitting a bug report, please include your versions of Excel and Windows and the exact error description (since the error numbers may be assigned to many issues), and if possible please provide the data you were using when the bug appeared and any intermediate output of the utility.

About Peltier Tech

Peltier Technical Services offers a variety of useful, easy-to-use, and guaranteed utilities. Peltier Tech offers rapid, personalized customer service in case there are any problems or questions.

All Peltier Tech Chart Utilities are integrated into Microsoft© Excel© spreadsheet software as standard add-ins for Excel. When installed the utilities provide buttons on the Excel menu or ribbon that allow you to select a regular worksheet range and create a specialized and customized Excel chart.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Peltier Technical Services, Inc., Copyright © 2016. All rights reserved.