Microsoft Excel is a spreadsheet program that is found in the Microsoft Office suite. Excel allows you to create many different types of charts, including waterfall charts. A waterfall chart is a type of floating column chart. It can be helpful in showing proportions, or how parts contribute to a whole. The X axis (or horizontal axis) on a waterfall chart typically shows a variable like volume, height or quantity. The Y axis shows a percentage or other proportional value.
Open a new spreadsheet in Excel.
Place your data in column C, starting at cell C5. For example, you might have a list of children’s heights.
Write the word “Total” in cell B5.
Fill in column B with item numbers. Each piece of data in column C should have an item number in the cell to the left. Run the numbers sequentially, starting at “Item 1.” For example, in cell 6 write “Item 1” and in cell 7 write “item 2.” Continue writing item numbers until each piece of data in column C has a corresponding item number.
In column D, Sum the numbers below the current row for each item listed in column C. For example, in cell C6, click on the cell and add the formula “=sum(c7:C$11)” into cell D6.
Place a zero in the total and last item fields in column D.
Highlight all of the data in your spreadsheet by left-clicking at the top left corner of your data and dragging the cursor down to the bottom right.
Insert a column chart into the spreadsheet. In Excel 2003, run the chart wizard and choose “column.” In Excel 2007, click the “Insert” tab and then choose “Column-2D Column.”
Hide the set of columns that you do not want to appear on your chart. Left-click on the set of bars and choose “Format Data Series.” In Excel 2003, click the series order tab and then move series 2 over series 1. Then click on the “Patterns” tab and click the radio tab next to “none” for area and border. In Excel 2003, select “Fill” and then click the "no fill" radio button. Set the Gap Width to "no gap" by moving the slider.
A pie chart is a more common representation of proportion. If your waterfall chart only contains positive numbers, a pie chart may suit your needs more than a waterfall chart.
Tips and warnings
- A pie chart is a more common representation of proportion. If your waterfall chart only contains positive numbers, a pie chart may suit your needs more than a waterfall chart.