**What Is Waterfall Chart In Excel?**

The

Waterfall Chart in Excelis a Column graph that plots the increasing result of data points as a graphical running total when we add or remove data values.Using theWaterfall Chart in Excel,users can analyze how the initial value of a data series gets impacted by the ongoing positive and negative changes. Thus, we use this chart in quantitative and financial analyses.

For example, the following table shows the starting monthly revenue in January, the variations in income from February to May, and the final revenue in June.

**Using Waterfall Chart in Excel **feature, we can visually review how the monthly revenue fluctuates through a series of changes from January to June.

The above **Waterfall Chart in Excel example** shows the increasing and decreasing values and the starting and ending revenues in different color columns. If the subsequent value is positive, we can observe that it starts from the level where the previous data point ends. On the other hand, the **Waterfall Chart in Excel** shows the negative data points as a column moving downwards from the level where the previous data point ends. And as the graph also exhibits connector lines between each column, it gets referred to as the **Bridge chart**.

##### Table of contents

###### Key Takeaways

- The
**Waterfall Chart in Excel**shows how the data series’ starting value varies according to the successive increasing and decreasing values. - Using the Waterfall chart type in the Insert tab, we can create a Waterfall plot.
- We can create a
**Waterfall**graph manually by selecting the**2-D Stacked Column**chart from the**Insert**tab. But we will have to make the**Base**transparent, show the initial and ending values in the same color, and the increasing and decreasing values in different colors.

**How To Create Waterfall Chart In Excel?**

The procedure to create a **Waterfall Chart in Excel** for a dataset is:

Select the dataset, or the cell range > click the “**Insert**” tab > go to the “**Charts**” group à click the “**Insert Waterfall, Funnel, Stock, Surface, or Radar Chart”** drop-down à select the “**Waterfall**” option. The **Waterfall Chart In Excel **is created.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

**Basic Example**

We will learn to create a chart **using** **Waterfall Chart in Excel example.**

The following table shows a company’s initial revenue in 2018, the contributions of five small ventures (both positive and negative), and the firm’s final income in 2019.

The Steps to create a **Waterfall Chart in Excel** are:

__Step 1__**: **Click the above table > click the “**Insert**” tab > go to the “**Charts**” group > click the “**Insert Waterfall, Funnel, Stock, Surface, or Radar Chart”** drop-down > select the “**Waterfall**” option.

Once we click the highlighted **Waterfall **chart icon, we will get the below graph.

However, we have to format the chart to get the perfect Waterfall Chart in the Excel template required to review data variation accurately.

[**Note:** **Formatting a Waterfall Chart in Excel** involves showing the Total, adding chart and axes titles, and changing the graph scale].

The chart legend shows three different column colors, **Blue**, **Orange**, and **Grey**. **Blue** and **Orange** denote the small ventures’ positive and negative revenue changes or contributions. On the other hand, there are no columns in **Grey**, though the legend shows the **Total** value as **Grey**.

__Step 2__**: **The first and last columns are the **Total** values, and they should not float. So, to format them, click on the first data point column and double-click it to choose it. We will get the** “Format Data Point pane**”**.** Check/Tick the “**Set as total**” checkbox in the “**Series Options”** tab of the “**Format Data Point**” window.

[Alternatively, choose the first column, right-click on it, and select the **Set as Total** option].

Next, click on the last data point column and repeat the above process.

Once we close the **Format Data Point** window, we will see the **Total** values, **Initial Revenue – 2018** and **Final Income – 2019**, in **Grey**.

** Step 3: **Change the vertical axis scale to show revenue variations clearly. So, click on the chart area to enable the “

**Chart Elements**” icon, ‘

**+**’ > click the “

**Axes**” right arrow > click the “

**More Axis Options…**” to open the

**Format Axis**window.

The **Format Axis** window opens as shown below. Next, click on the “**Axis Options**” drop-down > select the “**Vertical Axis**” option.

Change the **Bound** settings in the **Format Axis** window.

We will set the **Minimum** value as **45000**, as the minimum value in the dataset is **50000**.

Close the **Format Axis** window.

** Step 4: **Click on the chart area to enable the “

**Chart Elements**” icon, ‘

**+**’ > click the “

**Legend**” right arrow > click the “

**Bottom**” option to move it to the bottom of the plot, as shown below.

Instantly, the “**Legend**” moves to the bottom of the chart, as shown below.

** Step 5: **Double-click on the “

**Chart Title**” to modify and change it to “

**Company Revenue Variations Due to Small Ventures’ Contribution**”.

__Step 6__**: **Click on the chart area to enable the “**Chart Elements**” icon, ‘**+**’ > check/tick the “**Axis Titles**” checkbox to insert axis titles.

Once we insert the axis titles and resize the chart, the resulting **Waterfall Chart in Excel** will be:

Thus, we can visually review the effect of the small ventures’ contribution on the initial revenue in 2018 and compare it to the company’s final income in 2019.

**Critical Features of Waterfall Chart in Excel**

**Floating Columns**: All the columns in a**Waterfall Chart in Excel template**, except the first and last, are floating type. They denote the positive and negative variations according to the first value in the data series.**Spacers**: Sometimes, the columns in**Waterfall**charts do not start from zero. Instead, the plots use spacers and adjust the column offset by a specific margin.**Connector Lines**: The**Waterfall**graphs utilize connector lines to show the connection between the columns.**Color Coding**: By default, the**Waterfall**plots show positive and negative value changes using different color-coded columns to highlight the variations.**Crossover**: When the data points plotted in the**Waterfall**graph shift across the X-axis, it automatically adjusts to show the value change flow across the axis.

**Examples**

We will understand the **Waterfall Chart in Excel** with some advanced scenarios.

**#Example 1**

The following table shows the initial revenue, net income, and contributions of revenue variation factors.

The steps to create a **Waterfall Chart in Excel **are:

__Step 1__**: **Click the above table > click the “**Insert**” tab > go to the “**Charts**” group > click the “**Insert Waterfall, Funnel, Stock, Surface, or Radar Chart”** drop-down > select the “**Waterfall**” option.

We already saw the steps in **formatting a Waterfall Chart in Excel** in the previous section. We can refer to steps 2 to 6 to create the required **Waterfall** chart.

__Step 2__**: **Choose the first column > in the **Format Data Point** window, go to the “**Series Options**” tab > check/tick the “**Set as total**” checkbox.

Repeat the process for the last column too.

So, the **Total** values, **Initial Revenue**, and **Net Income** are not floating columns.

__Step 3__**: **Click on the chart area to enable the “**Chart Elements**” icon, ‘**+**’ > check/tick the “**Axis Titles**” checkbox to insert axis titles.

Once we enter the axis titles and update the **Chart Title**, the resulting **Waterfall Chart in Excel** will be as shown in the image below.

The above plot shows those factors (**Orange** columns) which negatively impact the initial revenue and the one (**Blue** column) positively impacting it. And the last column shows the net income, which we can compare with the initial amount visually.

**#Example 2**

The illustrations we saw till now will work in Excel 2016 and the latest versions. But suppose we have an older version of Excel or wish to create a **Waterfall Chart in Excel** manually. Then we can refer to the below illustration.

The following table shows the initial sales and the monthly sales fluctuations.

The steps to create a **Waterfall Chart in Excel **manually are:

__Step 1__**: **Firstly, we will rearrange the above data. Insert three new columns between Columns A and B, **Base**, **Decrease**, and **Increase**. And we also have a new row at the bottom to determine the **End** value, representing the sales figure for the whole year. Therefore, the modified table will look as shown below. We will start by entering the values.

__Step 2__**: **Choose cell **C2** and enter the formula **=IF(E2<=0,-E2,0)**

Drag the formula from cell **C2** to **C14** using the fill handle. We will get the output shown below.

__Step 3__**: **Choose cell **D2** and enter the formula **=IF(E2>0,E2,0)**

Drag the formula from cell **D2** to **D14** using the fill handle. We will get the output shown below.

__Step 4__**: **Choose cell **B3** and enter the formula **=B2+D2-C3**

Drag the formula from cell **B3** to **B15** using the fill handle. We will get the output shown below.

** Step 5: **Choose the cell range

**A1:D15,**select the “

**Insert**” tab > go to the “

**Charts**” group > click the “

**Insert Column or Bar Chart”**drop-down > select the

**2-D Stacked Column**chart type.

We will get the below plot.

** Step 6: **Now, we will convert the above graph into a

**Waterfall**chart. Right-click on the chart, choose the “

**Series Base**” from the drop-down > select the “

**Format Data Series**”.

In the “**Format Data Series**” pane > click on the “**Fill & Line**” tab in the “**Series Options**” > choose **No fill** for **Fill** and **No line** for **Border**.

Also, delete **Base** from the legend.

** Step 7: **Right-click on the chart, choose the “

**Decrease”**series data point from the drop-down > select the “

**Format**tab from the Excel Ribbon > go to the “

**Shape Styles**” group > click on the “

**Shape Fill**” drop-down and choose the desired color, here, Red.

Likewise, click on an “**Increase**” series data point and repeat the above procedure to change its color.

__Step 8__**: **Show the Total values, **Initial** and **End** data points in the same color. Choose them one by one and repeat the procedure to change color. First, **Initial** Data Points is shown below.

**End** Data Points is shown below.

__Step 9__**: **Right-click on any data point to open the **Format Data Series** window. In the “**Series Option**” tab, set the “**Gap Width**” as **20%** to reduce the gaps between the columns in the graph.

Finally, once we update the Chart Title and axis titles, the final **Waterfall chart** will be as shown in the image below:

**Uses Of Excel Waterfall Chart**

We can utilize the **Waterfall Chart in Excel** in the below scenarios:

- When analyzing entities whose quantitative values show a transition, either positive or negative.
- When performing quantitative analysis, such as inventory or performance analysis.
- When tracking contracts, such as those which are newly added and canceled.
- When visually analyzing balance statements.
- When featuring budget changes in a project.
- When developing dashboards.

**Important Things To Note**

- The
**Waterfall Chart in Excel**graphically displays the collective effect of increasing and decreasing values. - The
**Waterfall**chart type is available in Excel 2016 version and above. - In the case of older Excel versions, we can utilize the
**2-D Stacked Column**chart and modify it to create a**Waterfall**graph. - Before creating a
**Waterfall**graph, ensure the source data is accurate and complete.

**Frequently Asked Questions**

**Where to find Waterfall Chart in Excel?**

The **Waterfall Chart in Excel** is found in the **Insert** tab. The third chart shown below is the **Waterfall** chart type.

**When to use Waterfall Chart in Excel?**

We can use **Waterfall Chart in Excel** when we need to visualize balance sheets, analyze inventory over a duration, and perform other quantitative analyses.

**How do I format a Waterfall Chart in Excel?**

We can format a **Waterfall Chart in Excel** using the options available in the “**Design**” and “**Format**” tabs in the Excel ribbon, which will appear on the Excel ribbon when we click on the Chart.

Likewise, we can use the **Format Data Series** and **Format Data Point** windows to format the **Waterfall** graph. Check the above article to know how to open these windows.

We can click the three tabs in each window to apply the required format, from changing column colors to adjusting the gap width between the columns in a **Waterfall** plot.

**Download Template**

This article must help understand **Waterfall Chart in Excel**, with its formula and examples. We can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Waterfall Chart in Excel. Here we create Waterfall chart, [Column Graph//Bridge Chart] with examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply