Formatting the Waterfall Chart

Changing the Bridge Series to Line Connectors

Now, we can begin to turn our strange stacked column chart into a proper waterfall chart. First, we are going to change all of the Bridge series into line charts. Select the top bar in the first column of the chart. Check to make sure it is highlighting the Bridge column data in your table. Right-click the bar and choose Change Series Chart Type.

In the Chart Type dialog that appears, choose the first chart in the Line category and click OK.

The result will be that the first Bridge row is now a connecting line between the two columns of Q1 and Q2 data.

Repeat the process for the other Bridge series, clicking on the bottom bar on the second column, and so on. You can always make sure you are selecting the right series on the chart by looking at what row is highlighted in your data table above. When you are finished changing the lines, your chart will look something like this:

Hide the Spacer Bars

Now we need to hide the spacer bars from view. Click on the spacer bar on the chart (and double-check that the correct row is selected).

Then, click on the Format menu tab under Chart Tools. In the Shape Styles section, choose Shape Fill and click No Fill. Repeat for the other spacer bar (if it is in use with your data).

Adding Data Labels

Next, we’ll add data labels to the bars. Each bar needs slightly separate formatting. Click the top bar in the first column and make sure it is a >=0 Label row. Right Click and select Add Data Labels.

Right now, it is showing decimal places and it is also showing zeroes. We want to hide both and move the numbers down on top of the other bar, so double-click on one of the new labels.

In the Format Data Labels dialog that appears, in the Label Options section, choose Inside Base under Label Position.

Then choose the Number section. We are going to create a special format for the numbers that makes the zero values disappear. Further, since this is the >=0 Label, we only need to format the positive numbers. (For more details about Excel’s number formats, check here). Choose the Currency category, specify 0 decimal places, and then modify the format to include three semi-colons at the end. The Format Code field should look as follows:

$#,##0;;;

Click Add, then Close.

Repeat this process for all 4 label data series. There are different number formats to use for each data series.

For the +Y >=0 Label:

$#,##0;;;

For the +Y<0 Label:

-$#,##0;;;

For the -Y >=0 Label:

;$#,##0;;

For the -Y <0 Label:

;-$#,##0;;

Pay careful attention to the minus signs and the location of the semi-colons. Don’t forget to click Add and then Close each time you make a change.

Now we can add the internal label for the total FY column. Right-click the FY bar and select Add Data Labels. Double-click the label you just created. In the Format Data Labels dialog that appears, in the Label Options section, choose Inside End under Label Position. Then choose the Number section and set the format to Currency, 0 digits. Click Close.

Finally, we need to make the bars for the label data series invisible. As we did for the spacer bar, click on the label bar on the chart (and double-check that the correct row is selected). Then, click on the Format menu tab under Chart Tools. In the Shape Styles section, choose Shape Fill and click No Fill. Repeat for the other label bars.

When finished, the chart should look something like this:

Finishing the Connector Lines

The last thing we need to do before choosing colors and format is turn the bridge lines into proper connectors. Unfortunately, this must be done individually for each of them.

Select one of the connectors, right-click it (this can be a bit hard, since they are small) and choose Format Data Series.

In the Format Data Series dialog that appears, choose the Line Color section. Change the setting to “Solid Line” and make sure the color is black.

Choose the Line Style section. Change the Width to 0.25 pt. Change the Dash Type to Square Dot (the third option on the list).

Repeat this process for all the connecting lines. When you are done, the chart should look something like this:

Final Formatting

We’re in the home stretch… There is just some final formatting to do to line everything up, make it easier to read, and make the waterfall pop!

The Series Legend is useless, so let’s get rid of it. Click on the legend and press Delete.

Now, let’s color-code the gains and losses. I chose a green for the gains and a red for the losses, which is pretty standard. Click on the bar for the gain (purple in the last picture). Under Chart Tools, click the Format menu tab. Under Shape Styles, choose the color you want from the Shape Fill drop down.

To better hide the connecting lines, it’s also good to give the bar an outline. Under the Shape Outline drop down choose black.

Repeat the process for the rest of the gains and losses. You’ll need to color code the +Y-Axis and -Y-Axis bars individually, but they should match in color. You can also adjust the color on the final total FY bar.

When you are finished, the chart should look like this:

I prefer to move the X-Axis labels down below the chart to make it easier to read the bars. Just right-click the labels and choose Format Axis to bring up the Format Axis dialog.

Under Axis Labels, choose Low.

Click Close to exit the dialog.

There is a lot of white space on the chart, so let’s close that up to make it more readable. First, we’ll adjust the bar widths… Double-click on one of the chart bars to bring up the Format Data Series dialog. Change the Gap Width to something smaller, like 35%. Click Close.

Next, let’s fix the Y-Axis formatting. Right click on the Y-Axis and choose Format Axis.

Most of the bar labels fit within the 350 to -350 range, so let’s set those as the max and min of the chart view. Under Axis Options, change Minimum to Fixed and set it at -350. Change Maximum to Fixed and set it at 350.

While we’re here, go to the Number section and change the Category to Currency and set it at 0 decimal places. Click Close.

At this point, we’re pretty close! Your chart should look something like this:

As a final step, you can adjust the text size, bold setting, and color to make it more readable. Just click to select an element, and change the font settings under the Home menu tab in the Font section. I brought everything up to font size 14 and made the text bold for readability. I also make the FY total white to make it easier to see.

With that, you should have an effective, easy to read waterfall chart!