Excel Waterfall Chart with Negative Values (3 Suitable Examples)

If you want create an Excel Waterfall chart with negative values, you have come to the right place. In this article, we will walk you through 3 easy and effective methods to do the task smoothly.

What Is a Waterfall Chart?

A waterfall chart is an exclusive type of chart that represents how both the positive and negative quantities in a data sequence donate to the total amount. To put it another way, the Waterfall chart is a perfect way of visualizing the Starting quantity, the positive and negative conversion made to the quantity, and the End quantity. The first column of the Waterfall chart represents the Starting quantity and the last column represents the End quantity. In between the first and last columns, the hanging columns are the positive or negative quantities.

How to Create a Waterfall Chart with Negative Values in Excel: 3 Methods

The following table contains the Period and Sales Flow columns. We can see in the Sales Flow column, that it has some negative values. Therefore, we will use this table to create a Waterfall chart with negative values in Excel. Since the Waterfall chart requires starting and end values, we add Start and End values in the Sales Flow column. Here, we calculated the End value by using the SUM function. The formula is given below.

The SUM function simply adds the cells from C5 to C10.

After that, we press ENTER.

As a result, you can see the End value in cell C11.

Here, by using the dataset given above, we will go through 3 easy and effective methods to create a Waterfall chart with negative values in Excel. Since the Waterfall chart has been added from Excel 2016 to higher versions of Excel, the first 2 methods are applicable for Excel 2016 and the higher versions of Excel. For Excel 2013 and the lower versions of Excel, we provide the 3rd method.

1. Using Waterfall Chart

This method applies to Excel 2016 and higher versions of Excel. In this method, we will use the Waterfall chart of Excel to create a Waterfall chart with negative values. To do so, we will go through the following steps.

Step-1: Inserting a Waterfall Chart

• First, we select the entire dataset.
• After that, go to the Insert tab.
• Next, from Insert Waterfall, Funnel, Stock, Surface, or Radar Chart >> select Waterfall Chart.

Therefore, you can see the Waterfall Chart with negative values.

• Afterward, click on the Chart Title to edit the title.

As a result, you can see the Waterfall Chart with our edited Chart Title.

Step-2: Formatting Waterfall Chart

In this step, we will format the Waterfall chart. Here, we want to set the Start and End columns as Total. Along with that, we want to format the Negative value column with Red color and the Positive value column with Green color.

• First of all, we will select the Start column by double-clicking.
• Next, we will right-click >> select Set as Total from the Context Menu.

• In the same way, we will select the End column by double-clicking.
• Afterward, we will right-click >> select Set as Total from the Context Menu.
• Therefore, you can see both the Start and End columns have been formatted.

Later, we will format the Positive value columns with the Red color.

• At this point, we will select the January column by double-clicking it.
• Moreover, we will go to the Format tab >> select Shape Styles.
• Furthermore, from Shape Fill >> select Red color.

• Repeat the following process for other Negative value columns as well.

Hence, you can see all the Negative value columns have been formatted in Red color.

Afterward, we will format the Positive columns with the Green color.

• Next, we will select the February column by double-clicking it.
• Then, we will go to the Format tab >> select Shape Styles.
• Furthermore, from Shape Fill >> select a Green color.

• After that, repeat the following process for other Positive value columns as well.

As a result, you can see the Waterfall chart with Negative values marked in Red color.

Read More: How to Create a Waterfall Chart in Excel

2. Use of Stacked Column Chart to Create a Waterfall Chart with Negative Values

In this method, we will use the Stacked column chart to create a Waterfall chart with negative values. Stacked Column Charts can show the variation of multiple variables in the most suitable way.

To do so, we will go through the following steps.

Step-1: Making Dataset

In this step, will complete the Base Value, Negative and Positive columns of the following dataset.

• First, we will complete the Negative column. To do so, we will type the following formula in cell D5.

• After that, press ENTER. Then you can see the result in cell D5.
• Next, we will drag down the formula with the Fill Handle tool from cell D5 to D10.

Therefore, you can see the complete Negative column.

Afterward, we want to complete the Positive column of the date.

• Next, we will type the following formula in cell E5.

• Next, press ENTER. You can see the result in cell E5.
• Afterward, we will drag down the formula with the Fill Handle tool from cell E5 to E10.

Therefore, you can see the complete Positive column.

Afterward, we want to complete the Base Value column.

• Therefore, we type the following formula in cell C6.

This simply adds cell C5 with E5 and then, subtracts D6 from the summation.

• Next, press ENTER. Hence, you can see the result in cell C6.
• At this point, we will drag down the formula from cell C6 to C11 with the Fill Handle tool.

As a result, you can see a complete Base Value column. Therefore the dataset is ready for creating a Waterfall chart with negative values.

Step-2: Inserting Stacked Column Chart

In this step, we will insert a Stacked column chart.

• First of all, we will select the Period, Base Value, Negative, and Positive columns from cells B4 to E11.
• Furthermore, we will go to the Insert tab.
• Moreover, from Insert Bar or Column Chart >> select Stacked Column chart.

Hence, you can see a Stacked column chart.

• After that, we will click on the Chart Title to edit the title.

As a result, we can see the Stacked chart with an edited Chart Title.

Step-3: Creating Waterfall Chart

In this step, we will create a Waterfall chart with negative values from the Stacked column chart.

• First of all, we will click on the Base Values of the Stacked column chart.

Next, you will see a Format Data Series dialog box appears on the right side of the Excel sheet.

• Afterward, from Fill & Line >> select Fill >> select No fill.
• Then, from Border >> select No line.

Therefore, you will see that the chart now looks like a Waterfall chart.

Step-4: Formatting the Waterfall Chart

In this step, we will Format the Waterfall chart.

• First, we will click on a Negative bar, and you will see that all the Negative bars will be selected.
• At this point, go to the Format tab.
• After that, from Shape Styles >> select Shape Fills >> select Red color.

Here, you can select any color, however, we keep the Negative bars as Red color.

Afterward, you can see all the Negative bars have become Red.

• Next, we will click on a Positive bar, and you will see that all the Positive bars will be selected.
• Then, go to the Format tab.
• After that, from Shape Styles >> select Shape Fills >> select a Green color.

Here, you can select any color, however, we keep the Positive bars as Green color.

Hence, you can see all the Positive bars have become Green.

Next, we will format the Start and End bars so that the chart looks more visible.

• At this point, we will double-click on the Start bar.
• Afterward, go to the Format tab.
• After that, from Shape Styles >> select Shape Fills >> select a color.

Here, you can select any color, however, we keep the Start bar color as Light Gray.

After that, we will make the End bar visible and we will format the End bar.

• Next, we will click on the Base Value, and therefore, we will see the End bar has been selected.
• Moreover, we will click on the End bar.
• Afterward, go to the Format tab.
• Along with that, from Shape Styles >> select Shape Fills >> select a color.

Here, you can select any color, however, we keep the End bar color as Light Gray.

Finally, you can see the Waterfall chart with negative values.

Read More: How to Make a Waterfall Chart with Multiple Series in Excel

3. Using a Combo Chart to Create Waterfall Chart with Negative Values

If you have Excel 2013 or lower versions of Excel, and you need to create a Waterfall chart that looks like the Waterfall chart in Excel 2016 then this method will be helpful for you. Here, we will demonstrate to you step by step how to create a Waterfall chart with Negative values in Excel 2013 or lower versions of Excel. To do so, we will insert a Combo chart first, and after that, we will modify and format the chart to make it a Waterfall Chart with negative values.

Step-1: Completing Dataset

The following dataset has Period, Sales Flow, Start and End Data, Pre Data, Post Data, and Data Label columns. In this step, we will complete this dataset’s Pre Data and Post Data columns.

• First, we will type the following formula in cell E6.

The SUM function simply adds cell $C$5 with cell C5.

• Next, press ENTER. You can see the result in cell E6.
• Afterward, we will drag down the formula with the Fill Handle tool from cell E6 to E10.

As a result, you can see a complete Pre Data column.

Next, we will complete the Post Data column.

• After that, we will type the following formula in cell F6.

The SUM function simply adds cell $C$5 with cell C6.

• Next, press ENTER. You can see the result in cell E6.
• Furthermore, we will drag down the formula with the Fill Handle tool from cell F6 to F10.

As a result, you can see the complete Post Data column.

Therefore, the dataset is now ready for inserting the Combo chart.

Step-2: Inserting Combo Chart

In this step, we will insert a Combo chart.

• First of all, we will select the entire Period column.
• Next press and hold the CTRL key, and select the Start and End Data, Pre Data, and Post Data columns.
• Furthermore, go to the Insert tab.
• Then, select the Recommended Charts.

An Insert Chart dialog box will appear.

• Afterward, select All Charts >> select Combo.
• After that, click on the downward arrow of the Pre Data.

A number of chart types will appear.

• Next, select a Line chart.

Later, you can see the Start and End Data are set as Clustered Column, Pre Data is set as Line, and Post Data is Set as Line.

• Afterward, click OK.

Hence, you can see a Combo chart.

• After that, click on the Chart title to edit it.

As a result, you can see the Combo chart with the edited Chart Title.

Step-3: Creating Waterfall Chart from Combo Chart

In this step, we will create a Waterfall chart from the Combo chart.

• First of all, we will click on the Post Data line.
• After that, from Chart Elements >> select Up/Down Bars.

As a result, you can see the chart now looks like a Waterfall Chart.

Step-4: Formatting Waterfall Chart

In this step, we will format the Waterfall chart.

• First, we colored the Positive bars in Red, Negative bars in Green. Along with that, we colored the Starting and End bars in Light Gray.
• Here, to color the bars, we follow Step-4 of Method 2.

Therefore, the Waterfall chart looks more presentable.

Next, we want to hide the Pre Data and Post Data lines from the graph.

• To do so, we will select the Post Data line and right-click on it.
• Afterward, we will select Format Data Series from the Context Menu.

A Format Data Series dialog box will appear.

• After that, from Fill & Line option >> click on Line.
• Then, select No Line.

• Repeat the following procedure for the Post Data line as well.

Hence, you can see the Waterfall chart has no line, and the chart looks more visible.

Next, we will delete the Gridlines from the chart.

• Afterward, we will click on the Gridlines and press DELETE.

Step-5: Adding Data Label to Start and End Bars

In this step, we will add Data Label to the Start and End bars.

• In the beginning, we will click on the End bar, and along with that, we will right-click on it.
• After that, we will select Add Data Labels from the Context Menu.

• In the same way, we add Data Label to our Start bar.

Therefore, you can see both the Start and End bar has Data Label.

Step-6: Completing Data Label Column

In this step, we will complete our Data Label column. This is because we will add a Data label to our floating bars by using the values from the Data Label column.

• First, we will type the following formula in cell G6.

• After that, press ENTER. Then, you can see the result in cell G6.
• Moreover, we will drag down the formula with the Fill Handle tool.

Hence, you can see the complete Data Label column.

Step-7: Adding Data Label to Floating Bars

In this step, we will add Data Label to the Waterfall chart’s floating bars.

To do so, we have to add the Data Label column’s data to our Waterfall chart.

• First of all, we will right-click on the chart >> choose Select Data from the Context Menu.

A Select Data Source dialog box will appear.

• Afterward, click on Add to add Data Lable’s data.

At this point, an Edit Series dialog box will appear.

• Moreover, we will select cell G4 as Series name>> select from cells G5:G10 as Series value.
• Next, click OK.

Later, in the Select Data Source dialog box, you can see Data Label is added to the Legend Entries (Series).

• After that, click OK.

Next, we can see the Waterfall chart has become like the following picture.

Step-8: Modifying Chart

In this step, we will Modify the above-created chart to a more presentable and visible Waterfall chart.

• First, to get the chart back to its regular shape, we have to right-click on any of the floating columns.
• Along with that, we have to select Change Series Chart Type from the Context Menu.

A Change Chart Type dialog box will appear.

• After that, click on the downward arrow of the Data Label box.
• Then, select a Scatter chart.

• At this point, click OK.

Therefore, you can see, that the chart has now regained the Waterfall chart shape.

Next, we want to add Data Label to the chart.

• To do so, we will right-click on any of the floating bar’s scatter points, these are the small Yellow points on top of the floating bars.
• Then, select Add Data Labels from the Context Menu.

Therefore, we can see each of the floating bars now has Data Labels. However, these are the numeric values of the Data Label column. Hence, this is not the Data Label of the floating bars.

Here, for the Floating bars, we want the value from the Sales Flow column.

To do so, we have to select the Data Labels of the floating bars by clicking on any of the Data labels.

A Format Data Labels dialog box will appear.

• After that, from Label Options >> select Value from Cells.

Next, a Data Label Range dialog box will appear.

• Afterward, from the Sales Flow column,  we will select cells C5:C11 as Select Data Label Range.

At this point, we will unmark the Y Value and Show Leader Lines boxes.

• Along with that, we will select the Above as the Label Position. This is because we want our Data Label above the floating columns.

As a result, you can see all the floating bars have Data Label on their top.

Step-9: Hide Scatter Point

In this step, we want to hide the Scatter points from our Waterfall chart to make the chart more visible and presentable.

• First of all, click on the Yellow colored Scatter point.

A Format Data Series dialog box will appear.

• After that, from Fill & Line >> select Marker option.
• Next, from the Fill group >> select No Fill.
• Along with that, from the Border group >> select No line.

Hence, you can see there is no Scatter point in the Waterfall chart.

Step-10: Putting Chart Axis Position at Low

In this step, we will put the chart Axis at the low position of the Bars. This will make the chart more presentable.

• In the beginning, we select the chart Axis.

A Format Axis dialog box will appear.

• Next, from Text Options >> select Labels.
• After that, select Label Position as Low.

• Along with that, we will select the Legend of the chart and press DELETE.

As a result, you can see the Waterfall chart with negative values in Excel.

Read More: How to Create a Stacked Waterfall Chart in Excel

Practice Section

You can download the above Excel file to practice the explained methods.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

Conclusion

Here, we tried to show you 3 methods to create an Excel Waterfall chart with negative values. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

Related Articles

• How to Create Stacked Waterfall Chart with Multiple Series in Excel
• How to Make a Vertical Waterfall Chart in Excel
• Excel Waterfall Chart Change Colors
• Excel Waterfall Chart Total Not Showing

<< Go Back to Waterfall Chart in Excel | Excel Charts | Learn Excel