Dual, Rotated, Split Bar, and Paired Waterfall charts are available in the Advanced Edition. Waterfall and Stacked Waterfall charts are available in Standard and Advanced Editions of Peltier Tech Charts for Excel. You can create a stacked waterfall chart by clicking on the Waterfall dropdown arrow, and clicking the Stacked Waterfall item in the dropdown menu. Stacked Waterfall Chart in the Peltier Tech Ribbon Stacked waterfall charts show the contributions of multiple values at each category by stacking these values within the waterfall’s floating bars. Bars for the beginning and ending values are connected by floating bars that show the rise and fall if the intermediate values. Row 24 indicates if the current value is the same as the previous value, in that case the option bars are used, and all other bars are zeroed out (rows 11 – 18).Waterfall charts are common in business to show how a value changes from one state to another through a series of intermediate changes. Row 22 then calculates which case to use (1 to 4) based on whether the previous and current values are positive or not (rows 21 to 22). You can look through each cell to understand how each of the bars (positive – green, negative – red, and the spacer) are calculated on each of those cases. Previous value is negative, the current value is negative.Previous value is negative, the current value is positive.Previous value is positive, the current value is negative.Previous value is positive, the current value is positive.There are four sections, each calculating the appropriate bar lengths based on 4 cases: The actual calculations of the length of each bar (green, red, spacer, etc.) is done at row 26 to 54. That is because Excel automatically stacks each series bottom first. Positive and negative optional 0-change bars: The back line you will see if a column does not change from the previous one (See the column “Change 6” from the waterfall chart example at the top of this tutorial)Įach of these series are calculated and shown on row 11 to row 18. The sequence of the rows are opposite from what you would normally expect in the chart.Upward, downward and spacer in the negative region (values below the x-axis).

Spacer to properly position the bar at the right height in the position region.Downward change (red) in the positive region.Upward change (green) in the positive region in the bar chart (values above the x-axis).The waterfall chart is a stacked bar chart, each column has 8 series (each is a row in the table below), each representing: But it is useful for those who are curious about how the chart actually works. This section is not necessary for using the actual template. How does it work? What is behind the blackbox? Paste formulas over of those columns: Paste special -> Paste as values (Shortcut: Alt e s t Enter).Highlight the entire column E (the column with all the errors).Copy the formulas over to the column with errors: Highlight entire column F -> Right click -> Copy.Now you will notice that there is a bunch of “#REF” errors in the new column E.Highlighting only a part of the column may break the file. To highlight an entire column, click on the Column Heading (the letter “E” at the top of the spreadsheet). Paste formulas over of those columns: Paste special -> formulas (Shortcut: Alt e s t Enter).This step is really important as it fixes the shifted formula. Highlight all the new columns you created in step 3 to 5 and previous column I (would be shifted to the right so it is somewhere else now).Repeat step 3 to 4 until you get the number of columns desired.Do not click any where else, and right click on the current selection -> Insert Copied Cells.To highlight an entire column, click on the Column Heading (the letter “I” at the top of the spreadsheet). Save the input values on row 4 to 5 so that they can be used later.Backup the file if you made changes and you want to be safe in case you break the file.Try to add and delete columns before you have made any changes to the template.Note that input cells have a orange background and blue text color.

Stacked waterfall chart excel 2010