If you are looking for complete step-by-step guidance on how to create a Waterfall Chart in Excel or How to modify and insert a meaningful waterfall chart in Excel, then your search ends here. 

Those who frequently use Excel, are probably already familiar with the advantages of charts. When you want to compare your data or quickly identify a trend, a graphical depiction of your data can be quite useful.

There are many predefined chart types in Microsoft Excel, including column, line, pie, bar, radar, etc. In this article, we’ll venture beyond merely creating simple charts and take a closer look at a unique sort of Excel chart called a Waterfall Chart. 

We’ll explain Waterfall charts to you and show you how helpful they could be. You will learn how to create a waterfall chart in Excel and about the resources that can speed up the process for you.

What is a Waterfall Chart?

Let’s look at an easy-to-understand waterfall chart’s appearance and possible usages first.

Actually, a waterfall chart is a particular type of Excel column chart. It is usually utilized to demonstrate how a starting position changes over the course of time, either increasing or decreasing.

In a conventional waterfall chart, the first and last columns show total values. The ultimate total value is reached by adding together the intermediate columns, which have the appearance of floating and showing positive or negative fluctuations over time. 

These columns typically use color coding to distinguish between positive and negative numbers. You’ll learn the technique of how to make the intermediate columns float a little later in this tutorial.

Since the floating columns act as a sort of bridge connecting the ends, a waterfall chart is also referred to as an Excel bridge chart. These graphs can be quite helpful while conducting analysis. A waterfall chart in 

Excel is exactly what you need if you need to assess a company’s profit or product earnings, make an inventory or sales study, or simply display how the number of your Facebook friends changed over the course of the year.

How do you insert a waterfall chart in Excel?

One of the most crucial elements when it comes to acquiring insights and understanding the information that is contained in the data for further analysis is the way we look at or engage with it. 

We can better extract information from the columns in Excel by building a relationship between the user and the columns by designing a visually appealing waterfall chart with strategic color selections and measurements. Here, we’ll go over step-by-step instructions for making a waterfall chart in Microsoft Excel.

Data Preparation and Loading the Data 

The first need for constructing a waterfall diagram is to ensure that you have some form of data on which to build the visualization. You must format the data in initial value, last value, intermediate positive value, and negative value after you get the desired data. You may clearly identify the data by categorizing it and marking it accordingly.

Let’s create two new columns in Excel and name them as Month and Profit/Loss, and then fill the rows with information to construct the waterfall chart in Excel. Positive values in this case indicate a profit, whereas negative ones indicate a loss.

Creating the Waterfall Chart in Excel

• After the data has been added, the waterfall chart is then made in an Excel file. The graph will display changes in values over time as well as volatility and ups and downs in revenue.
• By pressing Ctrl + Shift + Arrow down or manually choosing rows with the mouse, all the rows will be selected. 
• Now go to the INSERT tab and click on the CHARTS option shown below in red marking.

• Select the 1st option “Waterfall” from the dropdown menu and the waterfall chart will appear on your Excel and it will display the month-wise Profit/Loss values in the Waterfall Chart.  

Adding the End / Closing Bar to the Chart

• Add a row at the end of your base data and name it as End or Closing value
• Take the sum of all the values above buy using the formula { =SUM(B6:B18) }
• Now, click anywhere on the Waterfall Chart and you will find that the base data is selected with colored lines. Click on the bottom corner of the colored line and drag it till the end of the base data, that is, include the End column within the selected data.

Modifying the Waterfall Chart

• If you compare the Waterfall Chart shown above with the chart shown at the beginning of this tutorial, you will find that the End column is not the same. 
• In the above chart, the End column actually does not present the true insight that we are trying to portray because here End column unlike the other column, is not just a value for any time period, rather it represents the aggregate or total value for all month’s profit or loss amounts.
• The base data suggests that the opening or start value for FY 2022-22 was 400. Every month there were some profits or losses that we added or deducted from the start value. Then at the end of FY 2022-23, we will have 1145 as the End or closing revenue. So, the Waterfall Chart also should treat the End value as a total value.

• To Modify the End value to the total value, double-click on the bar of the End value which shows 1145. Once you double-click on this bar, the Format Data Series panel will be opened on your right side as shown below.
• Now if you once again single-click on the End bar, then all other bars will fade and only the End bar will be highlighted. Now notice that on the Format Data point panel you can see an extra checkbox option called “Set as total”. 

Select this checkbox and see the transformation of the End bar. The color of the End bar will be changed to a different color to distinguish the End bar as a total value. 

Now if you want to modify the colors of these bars into 3 different colors – Blue for the Start and End bars, Green for representing Profit bars, and Red for identifying Loss bars then do the following:

• Double-click on the Start bar and then again single-click on the Start bar so that only the Start bar is highlighted and the other bars are faded. Now as shown in the below picture click on the Change Colors dropdown and select the blue color. 
• Repeat the same process for each and every bar and select Green color for the profit bars and red for the Loss bars. 

• Click on the Chart Title and give a suitable name that represents the purpose of the chart
• Click on the “+” button on the top right side of the chart and select the Axis Title (shown in Red underline)
• Provide suitable Axis names that represent the value of the X and Y axis 
• Within the Design tab, you can see different predefined waterfall chart templates are available within the Chart Style section. 
• Choose any of the predefined formats as per your choice.
• I have chosen the gray format (shown in red underlined) and the final Waterfall Chart is shown below

FAQs

What is waterfall chart in Excel?  

A Waterfall Chart is a particular type of Excel column chart. It is usually utilized to demonstrate how a starting position changes over the course of time, either increasing or decreasing.

What is the primary purpose of a waterfall chart?

A waterfall chart helps users visualize and understand the cumulative effect of positive and negative values on a dataset.

How can I make my waterfall chart more visually appealing?

You can enhance your waterfall chart’s appearance by customizing colors, labels, and formatting options.

Can you do a combo waterfall chart in Excel? 

Unfortunately, there are no readymade Excel options available for the same.  However, by carefully structuring your data and utilizing a typical Excel Stacked Column chart type, you may quickly construct your own version of a stacked waterfall chart.

Are there any limitations to using waterfall charts?

Waterfall charts are most effective when visualizing data with a clear sequential order. They may not be suitable for all types of datasets.

Where can I find more resources to master Excel’s advanced features?

You can find tutorials, forums, and online courses dedicated to Excel on various websites and platforms.

The post Creating Waterfall Chart in Excel: Step-by-Step Tutorial appeared first on Pickl.AI.