Google Spreadsheet formulas is a powerful tool which can make your life much more easier if you are working with data a lot. There are a lot of interesting extensions that can be very useful for a online marketeer, like Google Ads extension or Google Analytics extension for Google Spreadsheets, but output data which you`ll get from them sometime need some more processing like filtering, matching and so on. So I`ve prepared some most useful formulas, which would help you to take over that kind of tasks.
1. QUERY function
Syntax:
=QUERY(data, query, [headers])
Example:
=QUERY(conv!A13:M,”SELECT A, B, C, E, Sum(M) WHERE B=’Main Conversion’ GROUP BY A, B, C, E”, TRUE)
One of the most powerful formulas in Google Spreadsheet and my favorite – because it imitates SQL language. With the help of SQL queries you can choose specific columns of the range and manipulate data using for example SUM(), MAX(). AVG(), etc.
2. VLOOKUP
Syntax:
=VLOOKUP(search_key, range, index, [is_sorted])
Example:
=VLOOKUP(A1,B:C,2,TRUE)
Very useful formula if we want to match records based on some common key. Works similar to JOIN query is SQL. Formula is searching for the same key in another range, and returns the appropriate value of that record.
3. ARRAYFORMULA
Syntax:
=ARRAYFORMULA(any_formula)
Example:
=ARRAYFORMULA(A:A & ” USD”)
Use this formula if you want to apply some formula to the whole column. Of course you can do that by hand, but it`s always faster and more flexible to use arrayformula so you always can be sure, that your formula is used to all records of the column.
4. IMPORTRANGE
Syntax:
=IMPORTRANGE(spreadsheet_url, range_string)
Example:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”, “sheet1!A1:C10”)
This formula would help you to import range of data from completely different spreadsheet. All you would need is the access to that spreadsheet and it`s url. Can be very useful while working with lots of spreadsheets which you have to combine.
5. IF function
Syntax:
=IF(logical_expression, value_if_true, value_if_false)
Example:
=IF(A1>0,”True”,”False”)
Quite intuitive formula that would help you to get values based on simple logic: if some statement is true – than this, else – this.
There are the main formulas which I personally use in my everyday tasks. I would suggest them at first place to everyone, who works with data in Google spreadsheets. Some practice and you will be able to mix formulas and get something like this:
=ARRAYFORMULA(IF(ISNA(VLOOKUP(IF(A2:A <>"";A2:A & "|" & B2:B & "|" & C2:C;"");'conv - filter'!F2:G;2;FALSE));0;VLOOKUP(IF(A2:A <>"";A2:A & "|" & B2:B & "|" & C2:C;"");'conv - filter'!F2:G;2;FALSE)))