Google Sheets is the cloud-based alternative to Microsoft Excel. Although Excel might still be the choice for some business needs, I’d strongly advise everybody to know your way around Google Spreadsheets. It’s fast, free and pretty easy to use.
We’re not going to talk about the basic stuff here (you should know the basic functions in spreadsheets) but two advanced functions that are very useful and will probably move you well above the crowd of people that say they know excel.
1) Index Match
Most people will use vlookup much too often. Really try to master INDEX(MATCH()) instead. It’s way more flexible. Just a bit harder to remember.
I always just remember one thing. You start with =INDEX( and the first thing to enter is the cell reference to the column that holds the desired results – once you’ve put that in say like this =INDEX(C:C just open the match formula and start of with the “search phrase” like so =INDEX(C:C;MATCH(A1, then refer to the column that you want to search in e.g. INDEX(C:C;MATCH(A1;F:F. Super important – always end the Match Formula with a 0 so that your finished function looks something like INDEX(C:C;MATCH(A1;F:F;0))
I’ve prepared a google sheet to show you a working example also visualising one of the advantages of Index-Match: the order of columns is not important – with VLOOKUP you need to worry about that stuff.
Just try to always use INDEX(MATCH()) instead of VLOOKUP() – you’ll understand the advantages soon enough.
2) Query Function
This one is a bit more complex but well worth your time. The Query-Function uses the Google Query Language to make data transformations quick and easy. The Google Query Language resembles MySQL a lot and just as MySQL it allows you to quickly drill down into large Datasets / Tables.
I’ve prepared another google sheet to show you a few examples. In the document you’ll see a simple dataset and a few options for a drilldown with the query function.
The Query Function looks like this:
=QUERY(A:Z;"select ...")
In this example A:Z would represent your dataset – just reference the whole dataset you want to grab data from.
The next parameter always starts with select and is enclosed by apostrophes. I’ll try to explain two different queries as an example here:
=QUERY(A:Z;"select B where A = '2016')
So, let’s say you have revenue in column B and the year in column A. With the query you’ll just get the values from column B where the column A is exacly 2016. Pretty easy right?
Off to something a bit more complex:
QUERY(A:Z;"select A,sum(B) group by A")
We’re looking at the same dataset, this time we want the sum of revenue grouped by the year. With select A,sum(B) we’re basically telling the function that we want A (the year) in the first column and sum(B) (the sum of revenue) in the second column. As there could be two rows with data for the same year we need to tell the function over which variable we want to calculate a sum thats what group by A is for.
We don’t want the result to look like this:
2011 | 2000
2011 | 1000
2012 | 5000
But instead what we want to get is this:
2011 | 3000
2012 | 5000
That’s why we need to group the result – in this case by A (the year)
Check out the documentation of the Google Query Language for a lot more examples and many more options. These examples really only show you a starting point of all the advantages.