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.
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.
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.
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.
Here is a typical data range and chart. All of the data is increasing and no subtotals or totals are included.
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.
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.
By now you should know the drill if you want subtotals and totals.
Here is a simple data range with some increasing stacks and some decreasing stacks. The program adds the blocks upwards or downwards.
Subtotals and totals work for positive and negative stacks.
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.
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.
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.
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.
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.