Create a Waterfall Chart in Excel

 

 
A waterfall chart helps to visualize the contribution made by each of the parties to the total. This chart type is distinguished by having “floating” columns because they give the impression of being suspended in the air.
 

This is the waterfall chart that we are going to do:

Waterfall Chart with Microsoft Excel

In our example we are showing the sales of a company in 4 countries where they have presence: Brazil, Colombia, Argentina and Mexico. Each country brings a portion of the total sales of the company. TO begin with the construction of the chart, we have to take in count 2 elements: – Base Values: These values are the blank spaces on which “will float” the columns. – Connectors: A series of data will be needed to show the connectors between the floating columns. Now we are going to see how the Data Table is.

Waterfall Chart Data Table

Create the Waterfall Chart To create a waterfall chart select the data range to be graphed (A2: G6) and go to the Insert tab in the Charts Section and click the button to select Column and the Stacked Column option. As a result you have the following chart in Excel.

Initial Waterfall Chart

Now we swap the rows and columns. Right click on the graph and select the Select Data option, in the dialog box click on the Switch Row/Column button and accepts the changes. The graph will look as follows:

Switched Rows and Columns in a Waterfall Chart

The upper columns are the corresponding connectors and now will become lines. To make the change, you need to right click on the first of the series of connectors (in this example the green columns). In the pop-up menu you must select Change Series Chart Type and go to the Line section and choose the Lines chart type. Repeat this process for all columns of connectors and you finally you will have the chart below.

Change Chart Type for the Connectors in a Waterfall Chart

To format each connector you need to right click on each line and select the Format Data Series and you should consider making the following changes: In the Line Color section select Solid Line option and change the color to black In the Line Style section sets a width of 0.25 pt. and in Dash type choose Square Dot. Apply these changes for each connector. Optionally remove the gridlines of the chart and have the following result.

Change Connectors Format in a Waterfall Chart

It only remains to remove the fill color of the base values, for do this right-click on the corresponding data set (in this example the blue columns) and select the menu option Format Data Series and select in the Fill section No Fill option. When you close the dialog box will get the following result.

Format Base Columns in a Waterfall Chart

You need only to remove the legend to having a waterfall chart as shown the beginning of the article. (Source: Excel Total)