Waterfall Chart an action in Excel

Waterfall Chart an action in Excel

Waterfall Chart

A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. These intermediate values can either be time based or category based. Helps understanding how an initial value is affected by a series of intermediate positive or negative values.

Data source

Step 1: Form the data step by step:

  1. Insert five columns in between Month and Earnings column as shown in figure 1.1
  2. Name added columns as Base, End, Down, Up, Start respectively
figure 1.1
  • Now Insert the formula in the J2 cell as =K2 (to start value)
  • I3: =MAX(K3,0)   (Positive Movement in Earnings)
  • H3: =MIN(L3,0)*-1 (Negative Movement in Earnings)
  • F3: =SUM(F2,I2:J2)-H3 (will use to hide the Chart)

Next put followings in the formulas:

  • Copy the formulas in F3, H3 and I3 down to row 13
  • G14: =SUM(F10,H10:I10)

Step 2: Insert chart by selecting data as shown below:

Right Click on the Base series -> Format Data Series. Under Fill, click No fill and under Border, click No Line, to hide on the chart.

Next select Down series. Right click and select Format Data Series… Choose RED color under Fill section as shown below:

Repeat the process by Selecting Up Series and choose GREEN color under Fill section.

Next Right click on any series and choose Series Option and reduce Gap width to 30% as shown below:

Finally remove grid lines and legends to format the chart as shown below:

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published. Required fields are marked *