Using Google Sheets instead of Excel?
If like me, for the best part of your SEO career you used Excel, then changing to Google sheets can be a little off putting.
But you can do pretty much everything you knew how to do on Excel in Google Sheets. It’s just a matter of discovering how to work it if there isn’t an identical formula or solution.
The only way to do this is persistence, sticking at it, researching online where needed and not being scared of spending an extra few hours on tasks while you find your feet.
Investing the time is definitely worth it, as next weeks blog will show there are some incredibly cool time-saving tricks available once you master the basics that you can’t even do in Excel!
How to Use Google Sheets Formulas?
Google Sheets works in very much a similar and sensible fashion to Excel. Before moving on to more complicated formula or combining multiple, try to get the hang of these more simple formulas that give a good Google Sheets foundation.
RIGHT
=right(string, [num_chars])
Can be used when you want to extract characters starting at the right side of the string.
LEFT
=left(string, [num_chars])
Can be used when you want to extract characters starting at the left side of the string.
LEN
=len(string)
Returns the length of the specified string, this is very useful for meta title and description writing.
TRIM
=trim(string)
This function returns a text value with the leading and trailing spaces removed, that often catching people out.
COUNTIF
=countif(range, criteria)
This formula counts the number of cells in a range that match the specified criteria.
SUMIF
=sumif(range, criteria, [sum_range])
This formula adds all numbers in a range of cells based on one criterion.
UNIQUE
=unique(array)
By highlighting a range, a new column can list all the unique keywords, terms, numbers.
CONCATENATE
=concatenate(string1, string2, string3)
This formula allows a string of different cells to be merged together into one. This can be useful for creating email addresses, for example.
SPLIT
=split(string, delimiter)
This formula splits a string based on the given delimiter, putting each section into a separate column in the row.
IF
=if(condition, value_if_true, value_if_false)
I use this formula so often with SEO work, simply it’s used to check if a condition is true or false. It’s worth noting that multiple IF formulas can be combined with a “&” between.
In the above example, we have example keywords, made up of search volume (SV) and a Good or Bad rating derived from the IF formula.
This example shows how the IF formula can be used to identify keywords that are likely to result in 100+ visitors per month.
Note: This is all hypothetical, assuming that for each keyword we ranked in the first position, as well as this there is a 29% click-through-rate (CTR). This is all used as an example of what the IF formula can be used for, we recommend using your own method to estimate search traffic as CTR can vary.
IFERROR
=iferror(original_formula, value_if_error)
This allows you to set a default value should the formula result in an error.
So in this instance when there isn’t a numerical value for SV the above IF formula can’t be calculated and shows “#VALUE!”, by applying an IFERROR formula before it can be set to “N/A”, “-” or “0” whatever works.
IMPORTRANGE
=importrange(spreadsheet_ID, range_to_import)
This formula allows you to import data from any other Google Sheet. Which is great to create client-facing sheets that piggyback off your “master” spreadsheet.
That’s my top 12 formulas for beginners to help save SEO’s time. Have a go for yourself getting to grips with any you are unfamiliar with and after the initial lag, your invested time will be saved infinite times over in years to come.
As you progress through and become more confident with Google Sheets you’ll realise the importance of these basics in being able to perform more advanced time-saving tricks.
If you’ve got the hang of these basics try work through the more advanced formula that I will post in next weeks blog.