Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Concatenate Excel Function
- RIGHT Function in Excel
- LEN in Excel
- LEFT Function in Excel
- Search Function in Excel
- TEXT Function in Excel
- PROPER in Excel
- MID in Excel
- Trim in Excel
- CLEAN Excel Function
- EXACT Excel Function
- REPT Function in Excel
- CODE Excel Function
- FIND Function in Excel
- VALUE Function in Excel
- Char Function In Excel
- Substitute Function in Excel
- Substring in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- TRANSPOSE Excel Function
- Row Function in Excel
- OFFSET Excel Function
- VLOOKUP with Multiple Criteria
- IFERROR with VLOOKUP in Excel
- Fixing VLOOKUP Errors
- Alternatives to Vlookup
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- COMBIN Excel Function
- INT Excel Function (Integer)
- TANH in Excel
- Date and Time Function in Excel
- Statistical Function in Excel
- AVERAGE Excel Function
- CORREL Excel Function
- COUNT Excel Function
- COUNTIF Excel Function
- FREQUENCY Excel Function
- MAX Excel Function
- MEDIAN Excel Function
- GROWTH Excel Function
- SLOPE Function in Excel
- TREND Function in Excel
- SMALL Function in Excel
- MODE Excel Function
- LARGE Excel Function
- PERCENTILE Excel Function
- LINEST Excel Function
- T-TEST in Excel
- QUARTILE Excel Function
- FORECAST Excel Function
- MIN in Excel
- Standard Deviation in Excel
- COUNTIF with Multiple Criteria
- Mean vs Median
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- Variance vs Standard Deviation
- Information Functions in Excel
- Excel Charts
- Column Chart in excel
- Stacked Column Chart
- Pie Chart in Excel
- Area Chart in Excel
- Stacked Chart in Excel (Column, Bar & 100% Stacked)
- Histogram Excel Chart
- Waterfall Chart in Excel
- Pareto Chart in Excel
- Bubble Chart in Excel
- Gantt Chart in Excel
- Radar Chart in Excel (Spider Chart)
- Clustered Bar Chart in Excel
- Clustered Column Chart in Excel
- Dynamic Chart in Excel
- Excel Tools
- Watch Window in Excel
- List Box in Excel VBA
- Conditional Formatting in Excel
- Data Table in Excel
- Data Validation Excel
- Paste Special in Excel (With Top 10 Shortcuts)
- One Variable Data Table in Excel
- Two-Variable Data Table in Excel
- Scenario Manager in Excel
- Solver in Excel
- Name Range in Excel
- Dynamic Named Range in Excel
- Sort Data in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Auto Format Excel
- Track Changes in Excel
- Text to Columns in Excel
- Merge Cells in Excel
- Lock Cells in Excel
- Scroll Bars in Excel
- Spell Check in Excel
- Filters in Excel
- Auto Filter In Excel
- Advanced Filter in Excel
- Recording Macros in Excel
- Add-Ins in excel
- Excel Fill Handle
- AutoFill in Excel
- Excel Tables
- Radio Button in Excel
- Combo Box in Excel and VBA
- Checkbox in Excel
- Excel Tips
- How to Unhide Columns in Excel?
- Convert Date to Text in Excel
- Top 20 Keyboard Shortcuts in Excel
- Convert Numbers to Text in Excel
- Show Formula in Excel
- Absolute Reference in Excel
- Relative References in Excel
- Superscript in Excel
- Subscript in Excel
- Formula Errors in Excel
- Convert Text to Numbers in Excel
- Insert Multiple Rows in Excel
- Remove Blank Rows in Excel
- Highlight Every Other Row in Excel
- External Links in Excel
- Insert Hyperlinks in Excel
- Remove Hyperlinks in Excel
- Combine Cells in Excel
- Remove Duplicates in Excel
- Random Numbers in Excel
- Drop Down List in Excel
- Refresh Pivot Table in Excel
- Column Sort in Excel
- Hiding a Column in Excel
- Count Unique Values in Excel
- CAGR Formula in Excel
- Equations in Excel
- How to Delete Pivot Table?
- Excel Subtraction Formula
- Excel Extensions
- Hide Formula in Excel
- Numbering in Excel
- Auto Numbering in Excel
- Row Limit in Excel
- Delete Row Shortcut in Excel
- Arrays in Excel VBA
- Array Formulas in Excel
- Dynamic Tables in Excel
- Convert Columns to Rows in Excel
- Formatting in Excel
- Print Comments in Excel
- Excel vs Google Sheets
Excel Waterfall Chart (Table of Contents)
- Waterfall Chart in Excel
- Waterfall Chart in Excel 2016
- How to Create Waterfall Chart in Excel (for 2013, 2010, 2007 and older versions)?
Waterfall Chart in Excel (2016, 2013, 2010, 2007 and others)
A waterfall, also known as a bridge or cascade, chart is a form of data visualization that helps to understand how the sequentially introduced positive or negative values affect the final value, showing a cumulative effect. It is also known as a flying bricks chart and Mario chart.
The Waterfall excel chart was introduced in 2016. The chart demonstrates how value increases or decreases through a series of changes. By default, the positive and negative values are colour coded. In 2013 or earlier versions of Excel, the Waterfall chart type is not present. However, in this article, you will also learn how to create a waterfall chart if you are using an older version of Excel.
Where to Use a Waterfall excel chart?
Waterfall graph in excel is widely used for visualizing financial statements, comparing earnings and analyzing sales or product value over a period of time. It is also used for analyzing profit and loss and visualizing inventory. They became popular in the late 20th century when McKinsey and Company first presented them in a presentation to their clients.
#1 – Waterfall Chart in Excel 2016
Suppose you have the revenue data for each month. The data is given in A3:B14 as shown below:
Now, you want to visualize the data on a waterfall chart in excel. You can select the cells A3:B14 and click on chart type and select Waterfall excel chart.
A waterfall chart in excel for this data will look like—
The chart will show the revenue generated in each month of the year.
You can also build a similar chart using older versions of Excel. Let us see how.
First, create five columns A:E – (1) Months (2) Decrease (3) Increase (4) Revenue.
The Start value will be in 3rd row and End in 16th row as shown below.
For Decrease column, use the following syntax –
= IF( E4 <=0, -E4, 0) for cell C4 and so on.
For Increase column, use the following syntax –
= IF( E4 >= 0, E4, 0) for cell D4 and so on.
For Base column, use the following syntax –
= B3 + D3 – C4 for cell B4 and so on.
The data will look like –
Now, select the cells A2:E16 and click on charts.
Click on Column and then plot a Stacked Column chart.
The chart will look like this.
Now, you can change the colour of the Base columns to transparent or no fill, and the chart will turn to a waterfall chart as shown below.
#2 -Waterfall Chart in Excel (older versions 2007, 2010, 2013)
Let us now see some examples of the waterfall graph.
Waterfall Graph – Example #1
Suppose you have the net cash flow for your company for each month of the year. Now, you want to see this cash flow over a Waterfall graph for better visualization of cash flow throughout the year and see which period faced the most crisis. The net cash flow data is shown below.
To plot this chart, simply select the cells B3: C16 and click on the waterfall chart to the plot.
If you have an earlier version of Excel, you can use an alternative method to plot.
Firstly, make five columns, Time, Base, Decrease, Increase and Net Cash Flow as shown below.
Now, fill in the details for the Start. The Base value will be similar to Net Cash Flow, Decrease and Increase will be 0.
For Jan, the increase will be given as –
= IF ( F23 >= 0, F23, 0) for cell E23.
For Jan, the decrease will be given as –
= IF ( F23 <= 0, -F23, 0) for cell D23.
For Jan, the base will be given as –
= C22 + E22 – D23
Now, simply drag them to rest of the cells.
Now, you can select the cells B21: E35, and click in Charts -> Columns -> 2D Stacked Column.
The chart will look like this.
Once the chart is prepared, you can set the Base fill to no fill, and you get this chart as shown below. Also, you may change the colour of Start and End bars.
You can adjust the thickness of the bars and Gaps in between to better visualization using the following steps –
Right click on the chart -> Select Format Data Series -> Decrease Gap Width.
Now, Finally, your chart will look like this.
In the chart, you can observe that there was an increase in the Net Cash Flow during the year and the Net Cash Flow was maximum during July. However, during Aug – Sep, the decrease was huge.
Waterfall Chart – Example #2
Suppose there are two items, Item 1 and Item 2, manufactured at your company. You have the details of the number of items manufactured in four different quarters and want to visualize them on a waterfall graph.
The data of the two items is given as:
Now, insert three columns in between- Base, Item 1, Item 2. Also, make a column of Total.
The Total column will contain the sum of items produced during that time period. For cell G13, it will be given as –
= E13 + F13
and the result will be:
Drag and drop for the remaining cells.
For the Initial time period, the base value will be the sum of the items, i.e, 220 in this case. C13 and C14 cells may be marked as 0.
For the base value, the syntax will be given as –
= B13 + C13 + D13 for cell B14.
Column C and D will be similar to E and F for all the four quarters.
So, simply drag it for the four quarters and you will observe the data as –
For End, the Base value will be –
=B17 + C17 + D17
and Item 1 and 2 will be zero for End.
Now, select the cells A12 : D18 and select Chart and make 2D Stacked Column.
Then, select No Fill to Base Column and you will get an Waterfall chart as shown below.
Alternatively, you may want to colour code the Initial values also. To do this, simply change the Base value for Initial to zero and keep the original Items values (100 and 120). You will get the plot as –
- The Waterfall excel chart is simple to create in Excel 2016+.
- Good visualization of change in values over time.
- Good visualization of negative and positive values.
- The waterfall graph type has limited options in Excel.
- Making this chart becomes cumbersome when using 2013 or lower version of Excel.
Things to Remember About Waterfall chart in Excel
- It shows a cumulative effect of how the sequentially introduced affect the final value.
- It is easy to visualize positive and negative values.
- This is a built-in chart type in Excel 2016.
You can download this Waterfall Chart in Excel template here – Waterfall Chart Excel Template
This has been a guide to Waterfall Chart in Excel. Here we discuss its uses and how to create Waterfall Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –