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!

Sending a mass email to your facebook friend list, the excel-lent way!

Notes: Export contacts from facebook



Facebook is a great tool to connect with people. However, there are times when it get's really frustrating.

The other day, I wanted to drop a mass mail to all my friends in Singapore. So I went to the messaging system, tried to enter "Singapore", hoping facebook would be smart enough to populate the To-List with all my contacts with current city as Singapore.

Alas, it didn't happen. Perhaps I was expecting a lot. So the next thing I did was create a friend list, manually choosing all the people I knew who still lived in Singapore, into it. The exercise took ~5-10 mins with around 200+ friends.



I went back to facebook messaging tool, composed a message, and added "Friends in Singapore" in the to list. Facebook identified it as a friend list, but prompted that I couldn't send a message to more than 20 friends.

I don't know why fb would think putting a limit of 20 on a message is smart. Anyways, I was losing patience. I remembered facebook announcing a data export tool, to export all your information -contacts, pictures etc. You can find it under account settings



However, since it exports everything in one go, it takes a while. And I wonder how big the zip file would be, since it contains all my fb activity since I first opened my account.

I had heard of a chrome extension that helps you do the same, but it has the same issues. I can't selectively export out a list. When you have 500+ friends on facebook, it's a pain to sort through the list again to identify folks you want to send your message to.

So this is what I finally did. Yahoo, (once a great email system) has a nifty contact import tool, which can pull contacts from facebook directly. It is fast, since it restricts itself to email ids only.



Once imported, you can't use yahoo to send emails to 200+ people. It will also give you a "too many contacts selected" error. Export your contacts out into CSV. Now the Excel Magic begins.

I went to my "Friends in Singapore" list I had created, dragged my mouse, selected all the names in the page, and copied them into my notepad. Went to excel and copied them again. Opened the contacts from the CSV into the next tab. Did a vlookup of the list against the entire contacts and extracted the names and their email ids. Took another 5 mins.

Wrote a small formula that added quotes, brackets and semicolons to each, to make it email friendly.


 Copied the entire column and pasted it directly into my gmail To list, and sent the message. Now after 15 mins of effort, which also included numerous failed attempts, I now have all my contacts in a small excel sheet, which can be used again for other purposes. I could have written a small script, but not this time.

People underestimate Microsoft Excel but it can do some really smart things, really quickly, if you only know how to use it :)

Do let me know if there are better ways to selectively export contacts in a list from facebook. Happy Surfing!

Excel Quick Tips: Reactivating right-click context menu after Essbase Add-in installation

If your company uses Hyperion cubes for all financial reporting, there is more a chance of you using an Essbase addin to pull financials from the Essbase cube into Excel. Interestingly, this plugin has notorious reputation of overriding your right click button with it’s own functionality. To disable this behaviour and get back your usual context menu, follow these steps:

1. Go to Essbase Addin Options

Options

2. Browse to Global Settings and uncheck the “enable Secondary function..”

Global Setting

3. Voila, you have your context menu back. Easy!

Excel Quick Tips: Disable Privacy Warning

If each time when you save your macro enable workbook, you receive an annoying popup "Privacy warning:This document contains macros, ActiveX controls, XLM expansion pack information, or web components. These may include personal information that cannot be removed by the Document Inspector", follow these steps

1.  Go to Excel Options, navigate to Trust Center and click on “Trust Center Settings”

2. First, enable all macros in the Macro Settings (Note: Assuming all your macros are safe and tested)

Enable-Macro

3. Then go to Privacy Options and uncheck “Remove personal information…”

DocInspector

4. Save and close the file. Next time when you open it, no more annoying privacy warnings.