In every industry, at some point of time you come across a need to analyse your sales or total profit values over time. Waterfall Chart, also known as Bridge chart or cascade graph can be a useful visualization in excel to show ups and downs and see how you reached to the final value at the end of time. Excel doesn’t have waterfall chart in its default chart library but by little more creativity, we can customize stacked bar chart to make waterfall chart. You can download helping document Creating Waterfall Chart to understand process simultaneously.

Setting up the data

To create waterfall chart we first of all need to setup and align our data. Here, we are assuming raw data as net sales flow over months as given in Raw Data sheet. Three columns have been inserted between month list and net sales flow column as shown in Creating Chart Range sheet.

• Base is a calculated filed and it will remain hidden in the chart. Only first and last bars will remain visible to show start and end values.
• Fall column lists the positive numbers in net sales.
• Rise column will contain negative sale numbers.
• A row was inserted above first record to show starting sales and below last record to show ending sales value in chart.
• Type below formulas for base, fall and rise as shown in below screen dump.

C9: = C8+E8-D9

D9: = – MIN(F9, 0)

E9: = MAX(F9, 0)

Copy formula in cell C9 down to row 18 and formulas in cells D9 and E9 down to row 17.

Creating Waterfall Chart

Follow below steps to create waterfall chart

• Select range B7 to E18, click Insert tab on excel ribbon and select stacked chart in column chart as shown below.

• Delete legends and grid lines from chart area.
• Click base series to select them, right click and choose Format Data Series… option.
• Now in series option adjust Gap Width to around 20%.

• Again select base series column, right click and select No Fill option in Fill.
• Select Start and End base series columns to fill grey color.
• Select Fall series column to fill Red color and select Rise series column to fill green color.

Further modifications can be done to enhance chart’s appearance and boost visualization.

Download Example

Please share your comments below……………..

The post How to make waterfall chart in Excel appeared first on Actiknow : BI consulting | Data Analytics Consulting | Automation | Mobility.