How to Create a Dynamic Waterfall Chart in Excel?

Though Excel provides a default option to create a Waterfall chart, they are restricted in many formatting options.

This post offers an alternative to default waterfall charts, by using a combination of columns, line charts, and other techniques.

Waterfall charts are an effective way to visualize components of change (both positive and negative) from one point (or period) to another. This blog walks you through the steps required to create a single waterfall chart in such a way that it can handle 4 unique scenarios, dynamically.

The Approach:

Multiple variations of this can be created with a single chart. You’ll spend time creating this chart once, and then all that’s needed is to modify the data.

All that is required is 6 easy-to-follow steps.

  • Set up the table data: Create the base table upon which our chart will be built
  • Create the total column: Add the total value(s) column to the table and build the base chart
  • Create green and red columns: Add the start & end columns using which our green and red colour columns will be built
  • Create the connecting lines: Add a connector column, add it to chart and format to create the connecting lines
  • Create the labels: Create positive and negative label columns; add them to the chart
  • Format the chart: Apply standard formatting for a visually appealing chart


Let’s get to the steps involved in creating this dynamic waterfall chart.

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Check our detailed explainer video of the steps required here:


i. Consider sample data as shown below:

Dynamic Waterfall Chart sample data

Select all this data, and press CTRL+1 to convert this to a table.

ii. Now, add a new TOTAL FLAG column that will be used to identify whether a particular value is a total or not.

Dynamic Waterfall Chart add new total flag

This column does not have any formulas, just enter “Y” in the cell against the values that are to be considered as totals. In the first scenario where we display components of a total, only the last column has the total value.

Next, we’ll create a TOTAL series which will be plotted on the chart. Here we’ll include a formula only to return values where the corresponding TOTAL FLAG is “Y” else return nothing.

The formula would be:

Dynamic Waterfall Chart add total series

With this data, we’ll create a chart – select only the COMPONENT and TOTAL columns and go to Insert, choose a clustered column chart.

Dynamic Waterfall Chart insert 2D cluster

This creates a chart as shown below

Dynamic Waterfall Chart (a)

i. Click on the column, and press CTRL+1 to open the format pane. Under the fill & line option, choose the fill color to suit your needs.

Dynamic Waterfall Chart fill & line

ii. Again, click on the column and use the “+” icon that appears on the top right of the chart to add data label.

Dynamic Waterfall Chart Data labels

iii. Click on the label, go to the Home ribbon, and change the font color of the label as needed.

Dynamic Waterfall Chart title

With this, adding the Total part of the chart is done.

Dynamic Waterfall Chart total part of the chart

The focus is on creating the green/red columns which are the components. For this, we need to add additional columns to our data.

i. Firstly, we need to create a column to capture the row number.

To make this dynamic, we’ll use a formula.

Dynamic Waterfall Chart add additional column

This formula retrieves the row numbers, sequentially from inside the table.

ii. The next column is the CUMULATIVE VALUE, each cell in this column will contain the cumulative values of the current cell plus the ones above it. This total value is used for building our connector lines of the chart.

The logic for this formula is:

If the current cell is the first row or a total, return the same value, else return the value plus the previous cumulative total value

We get the previous cumulative total using a simple OFFSET function which is used to offset the number of rows we need (to get the prior values). The function syntax for OFFSET is:

With this, the table looks like this:

Dynamic Waterfall Chart cumulative value column

iii. For this part, we’ll look at the calculations required for the starting and ending points of each column in the waterfall chart.

In all the charts we’ve created above, notice that the green columns start below and the red columns start from above and end down.  Let’s add two columns, START and END for these.

START: The logic for this column is,

  1. For the total record, we do not need any columns created i.e. NA() (since we’ve already created the totals columns in step 2)
  2. If the corresponding row is the first row, then we start at 0
  3. If it’s any other row, we start from where the previous row ended i.e the previous row’s cumulative total (using the OFFSET function)

As a formula, this would be:

END:

  1. For the total record, we do not need any columns created i.e. NA() (since we’ve already created the totals columns in step 2)
  2. Any other row, this will be the sum of the start plus the value

As a formula, this would be:

With this, the table looks like this:

Dynamic Waterfall Chart start end columns

Let us add the calculated columns created in the previous step to the chart now.

i. Right-click on the chart, go to “Select Data”

Dynamic Waterfall Chart select data

ii. Add the START and END series to the chart as shown:

Dynamic Waterfall Chart add start end series

Add the START series first:

Dynamic Waterfall Chart add start series

Then, add the correct horizontal axis:

Dynamic Waterfall Chart add horizontal axis

Similarly, add the END series too. With this, the modified chart looks something like this:

Dynamic Waterfall Chart (b)

Right-click on the chart and now go to “Change Chart Type”. Here, change the chart types of START and END series to a line as shown below:

Dynamic Waterfall Chart change chart type

Now, do a single left-click on any one of the lines, then use the “+” icon which appears on the top right of the chart and add Up/Down bars.

Dynamic Waterfall Chart up and down bars

This creates the bars needed but requires formatting:

Dynamic Waterfall Chart bars without formatting

Click on the chart and press CRTL+1 to open the format pane. From the drop-down here, choose the Down-Bars 2 and Up-Bars 2 to format these bars, as explained in this step.

i. Choose the down bars

Dynamic Waterfall Chart choose down bars

ii. From fill and line, choose an apt color as needed:

Dynamic Waterfall Chart down bar fill

iii. Repeat the same steps for the up bars and choose an apt fill color/border color.

Dynamic Waterfall Chart up bar fill

iv. Since we only need the bars, from the format pane drop-down, choose the START and END series and choose No Line.

Dynamic Waterfall Chart choose series
Dynamic Waterfall Chart no line

Repeat the same steps for the END series as well. With this, the chart looks like this:

Dynamic Waterfall Chart (c)

It’s time to create the connector lines in our waterfall chart. For this, we’ll include a new calculated column.

We achieve this by using the concept of error bars. The logic here is that, only in the last row, we do not need any connector lines from there; for all the other rows this is equal to the corresponding cumulative value.

Here, the formula first checks if the row is the last row of the table, in which case the formula returns nothing, else the cumulative value.

Dynamic Waterfall Chart connector line.

Now, it’s time to add this CONNECTOR series to the chart (similar to the step 04)

i. Right-click on the chart, and go to “Select Data”. Add this series.

Dynamic Waterfall Chart add connector seriees

ii. Right-click and change the chart type. Change the connector line type to Scatter as shown:

Dynamic Waterfall Chart scatter type

iii. Go to the format pane drop-down, and choose the “CONNECTOR” series, remove the markers:

Dynamic Waterfall choose connecter series

iv. Immediately, use the “+” from chart to add error bars.

Dynamic Waterfall Chart error bars

With this, the chart looks something like this, with error bars on both the X and Y axis.

Dynamic Waterfall Chart (d)

Time to format the error bars from the previous step to create the connector lines for the waterfall chart.

i. Choose the Y error bars from the drop-down and press the delete button.

Dynamic Waterfall Chart choose Y error bars

ii. Now, choose the X error bars, and go to the Erro bar options. Here format, the error bar as shown:

Dynamic Waterfall Chart X error bar options

With this, the connector lines to the chart is added:

Dynamic Waterfall Chart line added

Note: Ensure that the connector lines and the column borders are all of the same width and color.

This step walks you through the process of adding data labels to the chart. We’ll add two more calculated (formula-based columns) to add the positive and negative labels.

Though this can be done by adding just a single column, our aim is to create a waterfall chart that is dynamic and more flexible, with positive and negative labels differing in fonts and colors.

POS LABELS:

If the value is not the total and is greater than 0, that is considered a positive label.

NEG LABELS:

Similar to positive labels, if the value is not total AND less than 0, we consider that as a negative label.

Dynamic Waterfall Chart add POS NEG Labels

To add these labels to the chart:

i. Right-click on the chart, and go to Select Data.

ii. Add the POS LABEL as a series name, and the component as X axis but use the Cumulative values in the series Y values.

We do this to position the positive labels above each column (as that is the desired output for the waterfall chart we are after)

Dynamic Waterfall Chart POS label as series name

iii. Similarly, add the negative series to the chart. Here too, include the Cumulative values in the place of the series Y values.

Dynamic Waterfall Chart NEG label as series name

With this step, it may seem like nothing has changed in the chart. Use the format pane to check if the pos and neg label series are added.

Dynamic Waterfall Chart check series is added

Let’s now add these values as labels to the chart

i. Choose the POS LABELS from the drop-down, use the “+” from chart to add data labels.

Dynamic Waterfall Chart Data labels

ii. Click on the data labels, and use the home ribbon to format as needed.

Dynamic Waterfall Chart Data labels

iii. Immediately go to the label options from the format pane and position the labels above.

Dynamic Waterfall Chart add label position

iv. And, as seen from the previous step, we have added the cumulative values as labels but we need the values from the POS LABELS series as the labels.

To do this, from the label options choose the “Value from cells” option and select the POS LABELS range as shown here:

Dynamic Waterfall Chart POS Label range

Uncheck the Y Value from the label options

Dynamic Waterfall Chart uncheck y

v. Repeat the same steps for adding the negative labels.: choose the NEG LABELS series from the drop-down, use “+” to add labels.

vi. Click on labels, use the Home ribbon to format and

Dynamic Waterfall Chart format labels

Use label options to position these labels below:

Dynamic Waterfall Chart label position below

vii. from the label options choose the “Value from cells” option and select the NEG LABELS range as shown here; uncheck the Y Values

Dynamic Waterfall Chart select NEG label range

Our waterfall chart is almost ready, it is time for some final formatting:

i. Click on the chart and use the “+” to add a suitable chart title. Similarly, add axis titles (here click and delete the X-axis title to allow more space for the chart.

Dynamic Waterfall Chart axis titles

ii. Right-click on the x-axis and go to the format axis.

Dynamic Waterfall Chart format axis

From the format pane, position these labels “Low”. This way, when we add negative values to the data, the label always remains at the bottom of the chart.

Dynamic Waterfall Chart add label position

iii. From the format pane drop-down, choose Vertical (Value) Axis Major Gridlines and choose No Line option.

Dynamic Waterfall Chart gridline to no line

iv. Now, go to chart area from the drop-down and modify the chart border as shown:

Dynamic Waterfall Chart chart border

With this, the dynamic waterfall chart is ready. Modify the data to have either multiple subtotals or negative totals and see that the chart dynamically gets updated.

Dynamic Waterfall Chart final chart

Check our Data to Decisions page for more amazing tutorials to make Excel eaeasy and fun to work with!

Check our 2-page, downloadable illustrative guide explaining all the steps for a quick reference.

To get our FREE downloadable Illustrative Guide for about 32 unique Column Charts, enter your email here!

Leave a Reply

Your email address will not be published. Required fields are marked *