What is an Excel Waterfall Chart?A waterfall chart will let you see the changes that occur between a starting point and an ending point. You can use a Microsoft 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 a clear picture of which months had positive and negative results. Different Versions of ExcelThe steps that you follow to create a Waterfall chart will depend on which version of Excel you're using.
|
Make a Waterfall Chart (2016)In Excel 2016, and later versions, there is a built-in Waterfall chart type, so it only takes a few seconds to create one from your data.
This animated screen shot shows how to use the built-in waterfall chart type. There are written steps below the screen shot. As you can see - it's quick and easy! Create Waterfall Chart in ExcelFirst, follow the steps below, to set up the data that you want to show in the Waterfall chart. When the data is ready, then you can create the Waterfall chart, and make a few formatting changes, if necessary. Set Up the DataIn this example, the chart will show monthly gains and losses, over the course of a year.
|
Create the Waterfall ChartNext, follow these steps to select the data, and build the chart.
|
Format the Waterfall ChartThe default Waterfall chart that Excel created has red and blue columns, to show increases (blue) and (decreases). It also has a generic title - Chart Title. You can make any formatting changes to the chart that you'd like, and I recommend the following, at least. Chart TitleEither delete the chart title, if you don't need it, or change its text to something meaningful.
Total AmountsIf any of the amounts in your chart represent totals, you can change a setting, so Excel formats them differently. To format a Total column, follow these steps:
The column will change to green fill colour, to show that it is a total amount. Chart StyleInstead of keeping the default chart style, with numbers shown on each column, you can apply one of the other built-in styles.
|
Create a Waterfall Chart (2013)In Excel 2013 and earlier versions, there isn't a built-in Waterfall chart type. For those versions, you can follow the instructions below, to build one yourself. Example 1--Step 1-- Set Up the Data --Step 2 -- Enter Waterfall Chart Formulas --Step 3 -- Create the Waterfall Chart More ExamplesEx 2 -- Pocket Price Waterfall Chart Ex 3 -- Slides: Excel Waterfall Chart Video: Waterfall Chart 2013 Example 1Watch this short video, to see how to create a waterfall chart from your data, and the written instructions are below the video. Step 1 - Set Up the DataTo create your own waterfall chart, the first step is to set up your data. In this example:
Extra columns and rows will be added to the data, as described below. |
Add Blank ColumnsNext, follow these steps to add blank columns - these will get formulas added, in the next section:
Add 3 Rows to DataNext, follow these steps to add 3 rows to the data range:
Here is the revised data layout, with the rows added |
Ex 2: Pocket Price Waterfall ChartThis variation on a waterfall chart is a Pocket Price Waterfall chart, and you can get one in the sample file section below. That is different in this chart?Instead of just two highlighted columns (start and finish), like a regular waterfall chart has, a Pocket Price Waterfall chart has several highlighted columns. Formulas for Highlighted ColumnsTo create those highlighted columns, I created a new formula in the End column, cells C4:C14, to check the Net Cash Flow column (G). If that cell is empty, show the sum of the previous amounts.
The Base column is the reverse, showing the sum if the cell in column G is NOT empty.
Completed Pocket Price Waterfall ChartHere is the revised waterfall chart, with a highlighted column for each quarterly total, ending in September. In the sample workbook, I used the same formulas with some faked Pocket Price data, to create the Pocket Price waterfall chart. There aren't any green columns, because all of the values are negative amounts, bringing down the price. |
Get the Sample FilesWaterfall Chart: To follow along with the video and written instructions, get the sample waterfall chart file. The file is zipped, and in xlsx format. The file does not contain any macros. Pocket Price Waterfall Chart: To see the Pocket Price Waterfall chart and data, get the sample Pocket Price Waterfall chart file. The zipped file is in xlsx format, and does not contain any macros. More Links |
Last updated: July 22, 2023 4:19 PM