Waterfall Chart Excel
MS Excel or Microsoft Excel is currently the most popular spreadsheet software program with many built-in features and functions. When it comes to the visualization of data in the spreadsheet, Excel is a clear winner compared to other spreadsheet programs in the market. Excel supports several chart types, and the waterfall chart is one of the great options for visualizing the data via charts.
In this article, we elaborate on an introduction or definition of the Excel Waterfall Chart. The article also discusses step by step tutorials on how to create these charts in Excel sheets, including relevant examples.
What is a Waterfall Chart in Excel?
The waterfall chart is a special type of Excel chart that mainly helps display the supplied data series's beginning and ending position as per the change over time, either increasing or decreasing. In a waterfall chart, the first column usually represents the first value of the data series, while the last column represents the last value. However, all the columns together represent the whole value. Values are reflected positively and negatively using different colours, showing how the value increases or decreases through a series of changes.
Sometimes, the waterfall charts use different lines between columns in the plot area, which display a chart similar to a bridge. That is why it is also known as the Bridge Chart/Graph.
Components of Waterfall Chart
There are five main components of the Excel Waterfall Charts, such as:
Uses of Waterfall Chart
The waterfall charts got popular in the late 20th century when McKinsey and Company used this chart type for presenting their data. Initially, the waterfall charts were mainly used to track monetary statistics and performance. However, they have become useful in different financial tasks over time, and more and more companies are using these charts to represent data to their clients/ readers.
Nowadays, the waterfall charts are seen in most cases, from visualizing the data to navigating vast amounts of census data. Some essential uses of the waterfall charts can be seen in the following scenarios:
There are many other uses of the waterfall charts in Excel. These charts have been adopted by several departments and sectors, especially by sales companies, e-commerce companies, construction companies, retailers, educators, legal departments, lawyers, etc.
Important Features of Waterfall Chart
Generally, each waterfall chart looks different from the other one because of the supplied set of data. However, they all have some common features, such as:
Floating Columns: The waterfall chart consists of floating columns. Floating columns usually exhibit both positive and negative changes according to the initial values. It mainly helps to display a quick view of price positions over time.
Spacers: The waterfall charts do not always represent columns beginning from zero. Instead, they use spacers or padding and adjust columns offset by certain margins.
Connector Lines: Waterfall charts use connector lines (called datums) that help represent relationships between floating columns. Although connector lines are not needed for all waterfall charts, they can help enhance our chart's professional look and overall representation.
Colour Coding: Excel allows colour coding for specific floating columns. Using multiple colours in different columns, we can show positives from negative values and quickly view movement over time.
Crossover: There can be many scenarios in waterfall charts, based on the supplied values we plot in a chart, where the values will shift across the x-axis. It is one of the essential features of a waterfall chart, as the chart must automatically adjust to reflect motion across the axis.
How to create a Waterfall Chart in Excel?
When it comes to using the waterfall charts in Excel, there are two different methods depending on the excel version we have. In Excel 2016 and higher versions, there is an inbuilt tool for a waterfall chart type within the Charts section. Unfortunately, there is no built-in option for a waterfall chart in Excel before Excel 2016. Using Excel 2013 or any lower version, we will have to add some additional data and create a waterfall chart manually using the customized stacked column chart.
Let us understand the process of creating/inserting waterfall charts in both old and new versions of Excel:
Creating Waterfall Charts in Excel 2016 and Higher Versions
The following are the steps to create a waterfall chart in Excel:
That is how we can easily insert a waterfall chart in Excel 2016 and higher versions, and it is typically a two-clicks process.
Creating Waterfall Charts in Excel 2013 and Lower Versions
Creating waterfall charts in Excel 2013 and lower versions is a somewhat long and time-consuming process, and it requires multiple steps to create a waterfall chart in Excel 2013 and lower versions.
Let us take the same example as above and understand the step by step process of creating the corresponding waterfall chart in Excel 2013 and lower versions:
Step 1: Rearrange the Data
First, we need to rearrange our excel sheet to create a waterfall chart in Excel 2010 and lower versions. We have to insert three new columns in between our example data columns, and we name them Base, Fall and Rise, as shown below:
In the above image, the Base column (Column B) will contain a calculated amount as an initial point or value for the other two columns, Fall and Rise. The Fall column (Column C) will contain all the negative values of the Revenue flow, while the Rise column (Column D) will contain the positive values. Next, we insert the Start row at the beginning of the table. We also insert the End row after the most bottom (last) row of our data. This row mainly helps to analyze the revenue values throughout the entire year.
Step 2: Insert Formulae
After arranging the data, we need to enter specific formulas in the first cells or corresponding columns and drag them down to the entire column using the fill handle. Alternately, we can copy-paste the formula in corresponding adjacent cells. But, first, we need to enter zero in all the cells of the Start row.
Step 3: Insert a standard Stacked Column Chart
After getting all the data values, we need to insert the standard stacked chart with the corresponding data.
Step 4: Transform the Stacked Column Chart into Waterfall Chart
To transform the created stacked column chart into the waterfall chart, we must make the Base series data invisible. For this, we must perform the following steps:
Step 5: Format Excel Bridge Chart (Waterfall Chart)
Once the chart has been inserted into the sheet, we must change chart elements to make it attractive and informative. For example, we can edit the chart title, add colours, change the width of columns, add labels, etc.
However, step 5 is the optional step.
Add-ins/ Extensions for creating Waterfall Charts in Excel 2013 and Lower Versions
As we have discussed above, the process of creating waterfall charts in Excel 2013 and lower versions is quite difficult and time-consuming as compared to current versions of MS Excel. But, if we don't want to mess around the data and insert a waterfall chart at a click in Excel 2013 and lower versions, we can use specific special Excel add-ins or plugins. Some add-ins can help add waterfall charts in Excel 2013 and lower versions similar to Excel 2016 and above.
Most of the add-in tools for waterfall charts are paid. For example, Peltier Tech Chart Utility, Think-cell Chart, AnyChart, Aploris, etc. However, we can try the PlusX Excel add-in. It is free to use and can be download from their official site here:
We can go through the 'Downloads' section and download the relevant add-in as per the Excel version that we are currently using.
After downloading the add-in, we need to install it like any other typical software. Once the add-in has been installed, we will see a new tab named PlusX on the Excel ribbon. Whenever we want to insert a waterfall chart, we have to select the data and click on the PlusX tab, select the tool 'Waterfall chart', as shown below:
We can easily insert a waterfall chart in Excel 2013 and lower versions upon clicking the Waterfall Chart option. After that, inserting a waterfall chart is similar to inserting a waterfall chart in Excel 2016 and higher versions.
Customizing the Waterfall Chart in Excel
Excel Charts can be customized or edited using different methods. The process for customizing the waterfall chart is almost identical to customizing other charts available in Excel. The following are some typical methods to customize Waterfall Charts in Excel:
The common method to customize charts and their elements in Excel is to use double-clicks. As soon as we double-click on any element of the inserted chart, Excel displays additional editing or customization options relevant to the selected element. In Excel 2010 and lower versions, the additional options are displayed within the pop-up window. But, in Excel 2013 and higher versions, the corresponding options are displayed in a right-side panel, as shown below:
If we want to edit another element of the chart, we don't need to double-click again. Instead, we can select the chart element and activate relevant editing options. The side panel consists of element-specific customization options and formatting features, such as changing colours and effects.
Right-Click (Context) Menu
Accessing the right-click menu using the mouse is a quite common task in Excel, and it also consists of several customizations or formatting options. We must use the right-click on specific chart elements or even the chart itself to access relevant options. The context menu (right-click or contextual menu) lists down some quick options for basic customizations like changing colours and other formattings.
We can also access the detailed side-panel customization or pop-up window using the context menu. For this, we need to choose an option that starts with the text 'Format'. However, we must select the desired element before accessing the context menu or pressing the right-click button. For example, the image above shows the 'Format Data Series' option when selecting and right-clicking a data series in a chart.
Chart Shortcuts section is another great option to edit chart elements, formatting and filtering. It is only available in Excel 2013 and higher versions. By default, it is situated on the top-right side of the plotted chart in Excel. The Chart Shortcuts section consists of quick toggle checkboxes to turn specific chart elements on or off. Using the checkboxes, we can insert or remove desired chart elements.
The primary advantage of using the Chart Shortcuts section is that it displays the preview of options when we hover onto the checkboxes or the options. In the above image, we only move a cursor on the Chart Title > Above Chart, and the title preview is displayed on top of the plot area in the chart even before applying it.
The Ribbon is the most basic area that includes all the options present in Excel. Once we have inserted a chart in a sheet, we will see some new tabs on Ribbon relevant to the inserted chart. The tabs provide options for advanced editing and customization in the inserted chart. The tabs, namely DESIGN and FORMAT, are displayed under the category Chart Tools. In Excel 2010 and earlier, we also get an additional tab named LAYOUT.
The Design tab contains options such as inserting chart elements, colours, styles, quick layouts, and other editing options. In contrast, the FORMAT tab includes special formatting options that remain common with several other objects in the chart. Additionally, the LAYOUT tab has advanced options for editing layouts and their elements.
Note: It is important to note here that we will be editing or customizing the Waterfall chart and its elements in Excel 2016 and higher versions. But, in Excel 2013 and lower versions, we will customize the Stacked Column chart and its elements as an alternative to the Waterfall chart.
Advantages of using Waterfall Chart
The following are some advantages of using the waterfall charts in Excel:
Disadvantages of using Waterfall Chart
The following are some disadvantages of using the waterfall charts in Excel: