Excel waterfall chart is a tool that we use to see the changes in our data. These changes may be positive or negative. This chart shows the changes that occur from the initial to the final point. We can use this chart to show the impacts of positive or negative values on the resulting value.
For example, by making a chart of our monthly income, we can know which month had negative or positive results. It is the simplest way to visualize the initial amount, positive and negative changes on that amount, and the final resulting amount. The first column is the initial value, and the last column is the final value. The intermediate columns are positive and negative impacts on the total value.
The intermediate columns are floating columns that make a bridge-like appearance. Due to this appearance, the waterfall chart is also called a “bridge chart”. It is also named the “ flying brick chart” because of its floating or flying columns.
The waterfall chart plays an important role in financial estimates. These charts are important in visualizing the loss or profit. It is considered a helpful tool because of its following uses;
● Sale analysis of any product
● Shows budget changes
● Loss or profit showing chart
● To compare the profit with others
The waterfall chart is helpful if you want to show how initial progress is affected by various factors. We often use this chart to track necessary values. Moreover, it can also be for other purposes like education management, income listing, and profit from sales by understanding how negative and positive values are causing changes in the profit.
Basic points of waterfall chart
The most attractive thing in the waterfall chart is the “flying or floating columns”. The first and last columns are not floating, while the intermediate columns are floating. The floating columns are positive or negative impacts on the resulting value.
One thing you have to understand is that there should be positive and negative values in a perfect waterfall chart. The columns of positive values are floating columns. The positive values are coded with blue color to recognize such values. The positive value shows the profit point in the waterfall chart.
Like positive values, negative values are also important for a perfect waterfall chart. The negative values show the loss in the waterfall chart. As positive values are blue-coded, likewise negative values are also color coded. The columns of different values are color-coded.
Another noticeable thing about the waterfall chart is that the initial and final columns are not floating. These columns are coded in different colors to distinguish the resulting value easily. The final data is the total of all previous values.
Bridge like appearance
The columns of positive and negative values are floating columns, but the initial and final columns are not floating. The columns are connected from the endpoints. This connection of columns makes a bridge-like appearance. Due to this appearance, the waterfall chart is also named the “bridge chart.”
Create a waterfall chart in the Excel
There is no waterfall chart available in Excel. You have to make the waterfall chart by just following these simple steps.
●Rearrange or put your data in a column
●Put the formulas.
●Make the columns
●Transfer the column graph into the waterfall chart
●Composition of Excel waterfall chart
Put your data in the column
The first step of building the Excel waterfall chart is rearranging your data in the columns. For this purpose, make three additional columns in the chart. These are base, rise, and fall. The base is the amount that is used as the initial points, while in the fall section, all the negative charges are present, and in the rising column, all the positive results are present. Now the next step is to fill these columns with suitable values.
Put the formulas
An excellent way to fill up these blank columns is to put some important formulas in the columns.
➤The formula you have to enter in cell C4 is:=IF(E4<=0, -E4,0)
The negative numbers will be positive, and the positive numbers will be zero if the cell E4 has a value lesser or equal to zero.
➤ Copy this formula down to the table.
➤ Type =IF(E4>0, E4,0) in cell D4.
It says that all the positive numbers will be positive while the negative numbers will be zero if the value in cell E4 is larger than zero.
➤ Copy this formula down to the table by using a fill handle.
➤ Now, the last formula you need to enter in cell B5 is =B4+D4-C5.
Make the columns
Now, all your data is well arranged, and you can build your chart.
- Firstly select your data containing the rows and columns.
- Go to the charts option.
●Choose the stacked column option by clicking on “insert column”.
●Now the graph that appears on the screen hardly looks like a bridge chart. Now take the final step and turn the stacked graph into an Excel Waterfall Chart.
●Choose Format Data series in the context menu.
●An icon “Fill and line” will be shown there. Click on it.
●Click on no fill and no line in the Fill and Line section.
●When the blue base will disappear, then remove its marks.
●Now, search for “Shape Fill” in the shape styles category and click it.
●Here, you can choose colors for your chart. But make sure that all the columns of positive values have the same color, and the negative values have the same color.
●Here, you can also outline your columns or add special effects to make them more attractive.
●You can also remove the access white portion by bringing the columns close to each other.
●If you want to add data in your columns, Click “Add Data Label”. And here, you can add data to your columns.
As there is nothing hard in creating an Excel Waterfall Chart, but still, if you don’t want to rearrange your data, you can use an add-ins service that can arrange all your data by itself.
What is an Excel waterfall chart?
An Excel waterfall chart is a sort of chart that demonstrates how a final number is affected by a series of positive and negative values. It is frequently used to track financial data, such as income and expenses.
How do I create an Excel waterfall chart?
To make an Excel waterfall chart, you must:
- Make a data table that includes the starting value, positive values, and negative values.
- Choose the chart’s data range.
- Select Waterfall Chart from the Insert menu.
- Choose the kind of waterfall chart you wish to make.
- Make any necessary changes to the chart’s formatting.
What are the advantages of using an Excel waterfall chart?
These are an attractive method to track financial data. They are also simple to design and comprehend. Waterfall charts can assist you in identifying the important aspects influencing your financial success.
What are the disadvantages of using an Excel waterfall chart?
It can be challenging to make if you have a large amount of data. They can also be difficult to interpret if the data is disorganized. Waterfall charts are less adaptable than other forms of charts, such as line charts and bar charts.
Where can I find more information about Excel waterfall charts?
There are numerous tools available online that can educate you on how to make and use Excel waterfall charts. There are other Excel waterfall chart templates available to help you get started.
An Excel waterfall chart is a sort of chart that demonstrates how a final number is affected by a series of positive and negative values. It is frequently used to track financial data, such as income and expenses. Waterfall charts are simple to make and understand, and they can assist you in identifying the important aspects influencing your financial outcomes.