Create an excel waterfall chart series#

In the selection combo select the series you want to update. We are going to hide the base series and customize the colors of the increasing and decreasing values.Ĭlick on any of the bars and select the Chart Format option Ribbon. Once the data has been prepared, we need to tweak the series bars. Step 4 – Editing the chart series to represent the waterfall The following image shows all the formulas applied to the data. The Start and End Column contain the initial and final values of the flows respectively. An IF statement or a Max function can be computed. The Positive Column contains the positive values of the flow. It can be computed with an IF statement.or a Min function: The Negative column will contain the negative values of the flow. The Cummulative column is the addition of the flows.ī4 = G3 + G4. In order to populate the new columns some formulas need to be used. Note that the only manually populated columns are the Series column and the Net Cash Flow columns. This are the values that should be shown in the PowerPoint Waterfall Chart. Net Cash flow: This column stores the values of the flows.

Start: This series will store the starting value of the flow.

Positive: This column will store all the values of the flow that represent an increase.

Negative: This column will store all the values of the flow that represent a decrease.

End: The end series is a column that will store the final value of the commulative flow.

The Cummulative column starts and ends with fake values (cells B2 and B17) used to create an invisible column to set the chart scale. This is just a simple add operation of the value in the order sequence of the flow.

Cummulative: As the name describes, this columns stores the cummulative value applied till the series step.

In this example the series is a “ monthly” cash flow and each row has a month label, but this names could be anything the user wants to represent in the X Axis of the Waterfall Chart.

Series: This columns will have the Labels of the value Series.

Later, the user can edit the series and hide the ones that do not add visual value to the representation. We are going to create special series that will allow the Stacked Bar Chart to create the ideal bar, as a composite of different series values. Step 2 – Preparing the Stacked DataĪs we mentioned before, the Waterfall Chart will be a special case of PowerPoint Stacked Bar Chart. This cashflows will be the base data for the Waterfall Chart, so each bar should represent one of this values. In this example the flow starts with a value of 5000. In the series Column, we have added the Start and End Labels, in order to understand the initial and final value of the flows. Write down directly in the editable cells the data values that are going to be used used for the flows. We have an initial set of data, in this case is an example of cash flows. This will help the formulas understanding. Please make sure to edit the data using Microsoft Excel, and not the pop-up window of PowerPoint 2013. We will start editing the base template Bar Chart Template for PowerPoint. We will create different series that will provide the effect of the waterfall, and after some edition tricks, the chart will look like the expected results.

Create an excel waterfall chart how to#

In this tutorial, we are going to show how to edit one of our Data-Driven PowerPoint Bar Chart Templates, based on an initial set of values. In order to create this charts is necessary to create a Stacked Bar Chart and edit some of the series to create the effect of the waterfall. Waterfall Charts are not native charts provided by PowerPoint or Excel. The graphical representation clearly describes the cumulative effect of positive and negative amounts.

The Bar Chart X Axis contains different series labels, and the Y Axis starts at a certain level and each series moving right starts reducing/increasing the value until it reaches a final value. Finally, the last bar represents the final value after applying each of the cashflows in order. For example, if the chart is describing business cash flow, it will represent each months net value, if the Waterfall Chart is representing how money has been spent in different areas of the company, the series will represent each area. The next series of bars describes the periods or the categories of the flows. The first bar describes the initial value of the flow (starting point). This kind of charts is usually used to describe cash flows fluctuations. Waterfall Charts are a special representation of Stacked Bar Charts that resembles a waterfall due to its decreasing/increasing values moving from left to right.