Google Sheets & Excel formulas for Digital Marketers ‘Review’
This is my 7th post on the major things that I have learnt from my ongoing ‘Digital Analytics’ Minidegree from CXL Institute.
In this post, I’ll cover some Excel and Google Sheets formulas that can help a great deal to all the fellow digital marketers. I’ll also be sharing some Power tips for Excel that were covered in the course.
1) Add multiple Sorts on a Table
I believe we are too accustomed to Sorting values only in one row. It rarely helps because we often end up realising that we have to sort multiple rows to get the values in the order we need.
Therefore, we should always know there is Data>Sort Range to help us.
2)The SUM Commands (SUM,SUMIF, SUMIFS)
You probably know the Sum Command, which is summing multiple things together. But the SUMIF & SUMIFS is what I want to highlight as a game changer. Let me illustrate this example through a table:
If you want to add see the Total impressions (in Column G) — =sum(g:g)
Now, let’s create a separate tables to explain the SUMIF & SUMIFS -
-: Notice the $ signs used in the formula for SUMIFS. This is because we are trying to find the absolute values.
Now comes the Count formula
The important distinction to note in this formula is that we use =countA() when counting the texts and the =count() when counting the number.
Let’s apply is formula by using the above screenshot: -: If you want the count the queries ( aka text) in the Column B, the formula is =COUNTA(B:B) -: If you want the count the desktop queries in the Column B, the formula is — =countif (E:E,”desktop”) -: If you want the count both the desktop queries and USA queries the formula is — =countifs(E:E,”desktop”,D:D,”USA”) -: Please note you can either write USA or usa. This formula is case insensitive.
Pivot table in google sheets is not as strong as Excel. Excel offers you a lot of features to play with and lot of options to segment your data which is not that easily possible with Google Sheets.
Segmenting the Pivot table
Proper segmentation is the first thing which separates an Excel/Google sheets Pro from an amateur. Use Pivot table filters to segment your data.
Use slicers and timeline option in the Pivot table to easily allow the other people to segment your data while viewing your reports. Read more about the Slicers and timeline here.
https://www.howtoexcel.org/tutorials/slicers/ Always turn your data source into a Table
This will save you from the hassle of updating your Pivot table when you want to add a new row into your data source. The data source table will automatically add the new row into the Pivit table.
Click on a numbered cell to know the details behind it
Clicking on a numbered cell will help will take you a new sheet that shows the details behind that number.
Eg: If you click on a cell that shows 1000 Impressions of a Query, it will take you to the details behind that constitutes those Impressions i.e. the numbers from other Metrics which makes up those 1000 Impressions.
You can turn that off as well.
Show Value as field Settings
This is another awesome feature which lets you change the arithmetic operation of your Values. eg: You need Average of certain metrics, not the typical SUM. This will allow you change your formula for that metric (let’s say CTR) from the default SUM to AVERAGE.
When the numbers are not returned in certain cells (because they are not meant to be), it gives you the annoying #NA that clutters your sheet.
Error Trapping is way to let those #Na (not returned values) shine in your sheet but in an acceptable manner.
I am using a Vlookup formula to show your how error trapping is done.
=vlookup(A2,A:A,3,FALSE)
If a desired output is not returned using this formula, I will get a #NA, but error trapping makes things better looking -
=IFERROR(VLOOKUP(A2,A:A,3,FALSE),”No Value found”
The “No Value Found” is the output I need my formula to return, as an alternative to #NA.
Index and Match overcomes the weaknesses of VLookup. Vlook can only be successful if the values are in the same column.
Index and Match overcomes that limitation. Here’s a resource to quickly learn it:
https://www.excelcampus.com/functions/index-match-formula.
‘
Named Ranges
Named Ranges is super easy and effective way that allows you to quickly reference the places in your data set while applying the formula.
Here’s a Vlookup example to understand it:
Using formula without Named Ranges
=vlookup(A2,Sheet1!A:B,2,false)
Using formula without Named Ranges
=vlookup(A2,page_table,false)
Here, I have already pre-selected my range as page_table. Its not required but eases the process.
https://medium.com/@rzacharia/excel-named-ranges-the-good-and-the-really-ugly-894e04fb16e5 X Lookup
X Lookup is the one which helps you overcome all the limitations Vlookup. It is only available in Excel. The XLOOKUP was introduced later so make sure you have it in your version of Excel.
Sparkline
Sparkline is yet another freakishly simply formula to see the trends of the numbers. You can never forget this formula because it is -
=sparkline ()
Sparklines can be created in Time Series, Bar Chart and Column Chart. You can play around the colors as well but that feature is limited in Google sheets as compared to excel.
Here’s a great resource to know more on Sparklines — https://www.benlcollins.com/spreadsheets/sparklines-in-google-sheets/ String Manipulation
Here are some great formulas to help you with the String Manipulation (these applies to both Google sheets and excel) -
Substitute
Column A2 — /blogs/google-analytics ( Action: change to blog/google-analytics)
=substitute(A2,”/blogs”,”/blog”)
Output: /blog/google-analytics
Length
Column A2 — /blogs/google-analytics ( Action: find length of the url ie. the total number of characters)
=len(A2)
Output: 25 (which is the length of the URL)
Here’s a resource to understand more advanced functions. You have to go through them thoroughly. I am sure you will find use cases for each of them.
I’ll be learning to create impressive, time-saving reports with Data Studio.I’ll be sharing the tips from this course in the upcoming week.