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.

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.

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.

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.

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.

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

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

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)

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

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