Google Trix! - Automatic Org charts on google spreadsheet

I was recently looking for a smarter way to draw my team's organizational chart. The longer, fancier way is to meticulously build the org tree using your favorite vector/office tool (like visio, powerpoint etc). It is perfectly doable for a smaller team. But what if your org has ~300 people.

I had a spreadsheet with Employee name, title and manager, and was looking for a programmatic way to automatically build the tree using the manager names. Visio has a way to import a csv into a org chart but since I don't have it and my coding being a bit rusty, I decided to look for an easier solution in Google docs.

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.