Search Contextures Sites

 

 

 

 

 

 

 

 

 

 

 

 

Create an Excel Waterfall Chart

Set Up the Data
Enter the Waterfall Chart Formulas
Create the Waterfall Chart
Quick and Easy Excel Waterfall Chart Solution

You can use an Excel Waterfall chart to show the cumulative effect of positive and negative amounts, based on a starting value.

For example, show the monthly net cash flow amounts in a waterfall chart, and quickly see which months had positive and negative results.

excel waterfall chart

Excel doesn’t have a Waterfall chart type, but you can create your own version, by carefully rearranging your data, and using a Stacked Column chart type.

Follow the instructions below, to create your own waterfall chart, and download the sample waterfall chart file to see how it works. The file is zipped, and in Excel 2007 format.

Or, to save time and effort, you can invest in the Excel Waterfall Chart Utility to make the job easier. It's described at the end of this page..

Set Up the Data

To create your own waterfall chart, the first step is to set up your data. In the screen shot below:

  • 5 columns have been inserted between the list of months, and the column with Net Cash Flow amounts
    • Base is a calculated amount for a series that will be hidden in the completed chart. It creates a starting point for the Up and Down series in the chart.
    • End is the final column in the chart
    • Down is a list of negative numbers in the net cash flow column
    • Up is a list of positive numbers from the net cash flow column
    • Start is the starting value, from the net cash flow column.
  • A row was inserted above the Start row, and it will create spacing at the left of the chart. An arbitrary value (2000) was entered there, and any number within the range of net cash flow amounts could be used instead.
  • An End row was inserted at the bottom of the months list
  • A row was inserted below the End row, and it will create spacing at the right of the chart. An arbitrary value (2000) was entered there

Enter the Waterfall Chart Formulas

The next step is to enter the formulas that will be used in the chart. In the screenshot above, values are typed in the yellow cells, and other coloured cells contain formulas. The formulas are listed below the screenshot.

excel waterfall chart

B2: =" "

F3: =G3

B4: =SUM(B3,E3:F3)-D4

D4: = - MIN(G4,0)

E4: =MAX(G4,0)

E16: =SUM(B15,E15:F15)-D16

Copy the formulas in B4, D4 and E4 down to row 15

Create the Waterfall Chart

To create the waterfall chart:

  1. Select cells A1:F17 -- the heading cells and data -- but don't include the column with the Net Cash Flow numbers.
  2. On the Excel Ribbon, click the Insert tab, and click Column Chart, then click Stacked Column
  3. Click on the Base series to select it, and format it with no fill and no border, so it isn't visible in the chart.
  4. Select one of the Down series columns, and format the series with red fill colour
  5. Select one of the Up series columns, and format the series with green fill colour
  6. Format the Start and End columns with grey fill colour
  7. Select any column, and on the Excel Ribbon, click the Format tab
  8. Click Format Selection, and reduce the Gap Width to a small amount, about 10-12%
  9. Remove the Legend

Excel Waterfall Chart

Quick and Easy Excel Waterfall Chart Solution

If you don’t have the time or patience to arrange your data and create your own Excel Waterfall chart, Excel charting guru, Jon Peltier, offers a time-saving Excel Waterfall Chart Utility. You can relax, while the chart utility does all the work.

Just select your data, click the add-in button, set a few options, and click OK, and your waterfall chart is ready. A few columns with formulas are added in your workbook, to provide the data for the waterfall chart.

Reasonably priced, the Excel waterfall chart add-in will quickly pay for itself, because you won’t have to waste your time fiddling with data arrangements and chart settings.

You can see how well the Excel Waterfall Chart Utility works, in this short video.

Note: These are affiliate links, and I'll earn a small commission if you purchase the chart utility through these links.

Learn how to create Excel dashboards.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2012
All rights reserved.