Google Trix! - Waterfall Charts on google spreadsheet

Waterfall charts are very common. You see them everywhere. They let you visualize a series of Business events, drivers or activities to explain 2 data points. An example below is a 2013 Revenue waterfall for a Business. It explains where the incremental revenue in 2013 was generated from.
There are tonnes of excel tutorials out there, but here is one for google spreadsheets.






There are multiple ways to achieve the same result. I like to use a stacked column chart. The live spreadsheet can be downloaded here.  Steps:

1. First get the basic data for your waterfall. Mine looks like the one below. Notice the start is 2012 Revenue, the end is 2013 Revenue. And all other rows are either +ve or -ve numbers.



2.  Reorganize your data into rows and columns below to plot your stacked column chart.


This is probably the most important step and I'll explain in detail. For our stacked chart, we need 2 bars. One a base bar which will control the height, and then the second bar to plot the positive and negative values for each row. Bar 1 and Bar 6 are the initial and final bars in the waterfall.
Bar 2 to 5 control the height of each waterfall element.

Negative values are trickier, since in a waterfall chart, negative values are displayed as bars starting at the same height as the previous bar, and going down. We achieve that using 2 simple formulaes for the Base and Bar 2-5.

Value for Base: =if(B3 >= 0 , sum($B$2:B2), sum($B$2:B2) + B3)
Values for Bar2-5: =if($B3 >= 0, $B3, -$B3)
Values for Bar1,6: =$B3

3. Select data from Columns C-J and plot a column stack chart


4. Now some formatting tricks to make the waterfall appear.
a) Select the Base bar and change the fill color to None
b) select the Bar 5 and change it's color to red, since it represents a drop/decrease
c) Change other colors as desired. I like to show growth/incrementals in green
d) Remove the legend






5. Viola! Waterfall chart is ready. Simple right. There is obviously some extra work needed in terms of reorganzing the data into multiple columns to plot the chart but it works





Did you find this helpful? Feel free to share your comments. And if there are better ways of achieving the same, do share them across. Thanks!

Excel Quick Tip: Counting occurrences of a character in a cell

I found an interesting problem recently. We have a shared google docs at work for folks to sign up for hobby clubs. The idea was people to sign up against clubs by adding their names in a cell.

In the next week or so, the docs was filled with names and I wondered if there was a cool way to count the people who had signed up, using a simple formulae.



So here is the solution, which works both in excel and google docs.


Since each name is separated by a single comma (,)  the total number of people signed up is the count of Commas in a cell + 1.

In the above example, I want to count the , in column B. In the next cell, type this formula

C2  =LEN(B2)-LEN(SUBSTITUTE(B2,",","")) + 1

Let me explain how this works. As the name suggests, Substitute replaces a certain text/character ( in our case ,)within a string, with something else. Trick is to replace it with empty string (notice nothing between the last double quotes), and count length of strings before and after. So if there are 5 commas, Substitute returns a string 5 characters shorter, and that's what we want. Pretty easy right!

Hope you learnt something new today. Happy number crunching!








Excel Quick Tip: Finding Year and Quarter from dates

I deal with huge data around dates all the time.  Be it daily revenue, volume or activities. Though daily data is interesting,  we often need to aggregate data into weeks, months or quarters, to see trends, forecast projections and track them against targets.



Here is a quick tip on getting you the year, month, quarter and weeks from a given date. Read on.




The formula is quite simple.
Year= Year(A2) 
Month= Month(A2) 
WeekOfYear= WEEKNUM(A2)


Formula for Quarter and Sem Annual labels are bit tricky.
Quarter = "Q"&INT((MONTH(A2)-1)/3+1)
Month ="H"&INT((MONTH(A5)-1)/6+1) 

Simple. Isn't it. Here is another tip. When you press Control + ~ (tilde), all the formulas on your sheets will be visible. Press the same combination to turn this off.  Happy Excel Crunching!


Google Spreadsheet tip: how to SUMIFS


SUMIFS is probably the most useful feature, Microsoft rolled out in Excel 2007. It lets you SUM a range, using multiple criteria. For instance, in the situation below, if we wanted to find out how much John made in 2010 Q3, you could use this simple formula.



Now how do you do this in google spreadsheet? Unfortunately, it only has the older SUMIF formula, which lets you select a single criteria. Let's see how to make it work.





SUM can be chained with the FILTER formula, to achieve the same results. The syntax is very simple.
SUM (Filter ([Sum Range], [All the criteria Ranges separated with a ; or a , ]))
e.g. =sum(filter(D2:D11,C2:C11= "John";B2:B11="Q3";A2:A11=2010))        

Sample example sheet

Pretty easy right!... Enjoy!