For the next 30 days, we're going to be sharing quick simple videos to help you power up your Google Sheets knowledge. Along with each tip, we'll share an #SEO and #PPC use case to help identify the opportunities to use these formulas.
Day 1/30 - Merge 2 or more cells together using =CONCATENATE
1. Type =CONCATENATE
2. Select the first cell you'd like to merge
3. Select the second cell you'd like to merge
4. Hit enter!
Ideal for adding http:// or https:// to a list of URLs or merging domains with subfolders.
1. Type =CONCATENATE
2. Select the first cell you'd like to merge
3. Select the second cell you'd like to merge
4. Hit enter!
Ideal for adding http:// or https:// to a list of URLs or merging domains with subfolders.
Day 2 of 30 #GoogleSheets for #SEO - Text to columns
Need to split a URL into domain, subfolder and slug? This is quick and simple with just a few clicks.
1. Select all cells in column A
2. Click data > Split text to columns
3. Change separator to Custom and set as /
Need to split a URL into domain, subfolder and slug? This is quick and simple with just a few clicks.
1. Select all cells in column A
2. Click data > Split text to columns
3. Change separator to Custom and set as /
Day 3 of 30 #GoogleSheets for #SEO - UNIQUE
Need to tidy a list of repeating domains so they are all UNIQUE?
1. Type =UNIQUE
2. Select the cells you'd like to tidy (A2:A18 in the video)
Super quick and super easy.
Need to tidy a list of repeating domains so they are all UNIQUE?
1. Type =UNIQUE
2. Select the cells you'd like to tidy (A2:A18 in the video)
Super quick and super easy.
Day 4 of 30 - LEN
Working on title tags, #PPC ad copy, or maybe managing a #social account.
Character limits are a pain! Check your copy length in bulk.
1. =LEN
2. Select the cell you'd like to check
3. Magic! Drag down over the other cells
Tomorrow, conditional formatting.
Working on title tags, #PPC ad copy, or maybe managing a #social account.
Character limits are a pain! Check your copy length in bulk.
1. =LEN
2. Select the cell you'd like to check
3. Magic! Drag down over the other cells
Tomorrow, conditional formatting.
Day 5 of 30 #GoogleSheets for #SEO - Conditional Formatting
Easily spot when a limit has been reached or what’s yet to be done on a to-do list
1. Select the column you want to format
2. Format -> Conditional formatting
3. Set rules
4. Select Formatting
DONE!
Easily spot when a limit has been reached or what’s yet to be done on a to-do list
1. Select the column you want to format
2. Format -> Conditional formatting
3. Set rules
4. Select Formatting
DONE!
Day 6 of 30
Get rid of rogue spaces in your cells by using TRIM functionality.
A common #SEO use case is to remove additional spaces at the end of URLs. These spaces often break future formulas such as VLOOKUP.
1. =TRIM
2. Select the cell & hit enter
3. Drag over other rows
Get rid of rogue spaces in your cells by using TRIM functionality.
A common #SEO use case is to remove additional spaces at the end of URLs. These spaces often break future formulas such as VLOOKUP.
1. =TRIM
2. Select the cell & hit enter
3. Drag over other rows
Day 7 of 30 #GoogleSheets for #SEO - IMPORTRANGE
Need to grab data from another sheet? You could use copy and paste, but maybe that data is updated frequently!
1. =IMPORTRANGE
2. Add URL of the sheet to be copied
3. Add the tab name and cell range
Need help? Give us a shout!
Need to grab data from another sheet? You could use copy and paste, but maybe that data is updated frequently!
1. =IMPORTRANGE
2. Add URL of the sheet to be copied
3. Add the tab name and cell range
Need help? Give us a shout!
Day 8 of 30 #GoogleSheets for #SEO - LEFT, RIGHT, MID
Our first nested formula! Extract all the character either side of a specific character.
1. =LEFT
2. Select the cell
3. Nest a FIND formula for the first empty space
4. Select the cell to look in
5. -1 to remove the space
Our first nested formula! Extract all the character either side of a specific character.
1. =LEFT
2. Select the cell
3. Nest a FIND formula for the first empty space
4. Select the cell to look in
5. -1 to remove the space
Day 9 of 30 #GoogleSheets for #SEO - COUNTIF
Quickly calculate how many keywords are ranking above, below or equal to a certain position / number.
1. =COUNTIF
2. Select the cells to be counted
3. Enter the criteria e.g “<=5”
😃
Quickly calculate how many keywords are ranking above, below or equal to a certain position / number.
1. =COUNTIF
2. Select the cells to be counted
3. Enter the criteria e.g “<=5”
😃
Day 10 of 30 #GoogleSheets for #SEO - SUMIF
Quickly tally up sessions or conversions in a subfolder.
1. =SUMIF
2. Select the range of cells to be checked
3. Input the subfolder e.g. “*products*” (a little bit of regex here)
4. Select the cells containing data to be totalled
Quickly tally up sessions or conversions in a subfolder.
1. =SUMIF
2. Select the range of cells to be checked
3. Input the subfolder e.g. “*products*” (a little bit of regex here)
4. Select the cells containing data to be totalled
Day 11 of 30 #GoogleSheets for #SEO - PROPER/UPPER/LOWER
Quickly change the case of all your copy using any of the above.
1. =PROPER
2. Select the cell to be adjusted
3. Drag down the rest of the column!
Quickly change the case of all your copy using any of the above.
1. =PROPER
2. Select the cell to be adjusted
3. Drag down the rest of the column!
Bonus tip for Day 11 - You can see all 491 Google Sheet functions by using
=IMPORTHTML("support.google.com/docs/table/252…" , "table" , 1)
🔥🔥🔥
=IMPORTHTML("support.google.com/docs/table/252…" , "table" , 1)
🔥🔥🔥
Day 12 of 30 #GoogleSheets for #SEO - IF
Writing SEO titles or social copy & fed up of having to check if you have broken your character limit?
1. =IF
2. Select the cell to be checked(B2)
3. Add greater or less than your limit (>60)
4. Add copy for if this TRUE or FALSE
Writing SEO titles or social copy & fed up of having to check if you have broken your character limit?
1. =IF
2. Select the cell to be checked(B2)
3. Add greater or less than your limit (>60)
4. Add copy for if this TRUE or FALSE
Day 13 of 30 #GoogleSheets for #SEO - SUBSTITUTE
Building page subtitles for loads of different products?
1. =SUBSTITUTE
2. Select the cell to be searched (A2)
3. Add the text to replace (“Jeans”)
4. Add the text to replace this with (“Shorts”)
👖🔁👗🔁🧥🔁🥼
Building page subtitles for loads of different products?
1. =SUBSTITUTE
2. Select the cell to be searched (A2)
3. Add the text to replace (“Jeans”)
4. Add the text to replace this with (“Shorts”)
👖🔁👗🔁🧥🔁🥼
Day 14 of 30 #GoogleSheets for #SEO - COUNTIFS
Counting data that needs to satisfy two criteria?
1. Type =COUNTIFS
2. Select the column you want to check (B1:B11)
3. Input the test that data needs to pass to be counted (<=5)
5. Repeat steps 2-3 with any other conditions
Voila!
Counting data that needs to satisfy two criteria?
1. Type =COUNTIFS
2. Select the column you want to check (B1:B11)
3. Input the test that data needs to pass to be counted (<=5)
5. Repeat steps 2-3 with any other conditions
Voila!
Day 15 of 30
Want to check if keywords contain two feature words? Like "Birthday” and “Card”
1. =AND(ISNUMBER(SEARCH(
2. Type the feature word wrapped in “”s
3. Select the cell you want to check (A2)
4. Close off with 2 ))’s and a ,
6. Add another ISNUMBER(SEARCH(" and repeat!
Want to check if keywords contain two feature words? Like "Birthday” and “Card”
1. =AND(ISNUMBER(SEARCH(
2. Type the feature word wrapped in “”s
3. Select the cell you want to check (A2)
4. Close off with 2 ))’s and a ,
6. Add another ISNUMBER(SEARCH(" and repeat!
Day 16 of 30 - Pivot Tables
Pivot tables are great to quickly summarize and organize data.
In this case, how many keywords a landing page ranks for.
1. Highlight data
2. Select Data > Pivot Table
3. Set rows as Landing Page
4. Set values as Keyword
5. Adjust Order & Sort by
Pivot tables are great to quickly summarize and organize data.
In this case, how many keywords a landing page ranks for.
1. Highlight data
2. Select Data > Pivot Table
3. Set rows as Landing Page
4. Set values as Keyword
5. Adjust Order & Sort by
Day 17 of 30 - Sumifs
Sum cells based on multiple criteria. Ideal for totalling traffic based on device and subfolder.
1. =sumifs
2. Select values to be totalled
3. Highlight the Device column & set criteria as “Desktop”
4. Highlight Landing Page URLs & set as “*products*”
Sum cells based on multiple criteria. Ideal for totalling traffic based on device and subfolder.
1. =sumifs
2. Select values to be totalled
3. Highlight the Device column & set criteria as “Desktop”
4. Highlight Landing Page URLs & set as “*products*”
Day 18 of 30 #SEO & #PPC tips - IMPORTXML
Extract all the links (or anything else) on a page into #GoogleSheets!
1. =IMPORTXML
2. Insert URL wrapped with “ “
3. Use the XPath query “//@href”
Extract all the links (or anything else) on a page into #GoogleSheets!
1. =IMPORTXML
2. Insert URL wrapped with “ “
3. Use the XPath query “//@href”
Day 19 of 30 #GoogleSheets for #SEO -REGEXREPLACE
Regex, uh oh! 😟
Here’s a quick way using regex to replace a variety of dates.
This is potentially useful if you’re looking to update blog posts that are spread across multiple years.
1. =REGEXREPLACE
...
1/2
Regex, uh oh! 😟
Here’s a quick way using regex to replace a variety of dates.
This is potentially useful if you’re looking to update blog posts that are spread across multiple years.
1. =REGEXREPLACE
...
1/2
2. Select the cell to be rewritten
3. “[0-9]+” - this means we're matching at least one digit up to infinity times
4. Add the new year e.g. “2020”
Much quicker and easier than manually rewriting or using multiple find and replaces. 🔥🔥🔥
2/2
3. “[0-9]+” - this means we're matching at least one digit up to infinity times
4. Add the new year e.g. “2020”
Much quicker and easier than manually rewriting or using multiple find and replaces. 🔥🔥🔥
2/2
Happy long weekend UK & US.
To send you on your way here’s day 20 out of 30 of #GoogleSheets for #SEO & #PPC
Quickly pull categories from URLs using REGEXEXTRACT.
1. =REGEXREPLACE
2. Select the cell to be rewritten
3. “football|golf” - | means "or" in regex
4. Drag down
To send you on your way here’s day 20 out of 30 of #GoogleSheets for #SEO & #PPC
Quickly pull categories from URLs using REGEXEXTRACT.
1. =REGEXREPLACE
2. Select the cell to be rewritten
3. “football|golf” - | means "or" in regex
4. Drag down
Double post time to catch up from the Bank Holiday. Today we'll be taking a look at day 21 & 22 of #GoogleSheets.
Day 21 of 30 #GoogleSheets for #SEO - SPLIT
Earlier in this series, we used text to columns to break up URLs. SPLIT is an alternative.
Ideal for breaking URLs into subfolders.
1. =SPLIT
2. Select the cell to be split
3. Set the character used to decide where to split e.g. /
Earlier in this series, we used text to columns to break up URLs. SPLIT is an alternative.
Ideal for breaking URLs into subfolders.
1. =SPLIT
2. Select the cell to be split
3. Set the character used to decide where to split e.g. /
Day 22 of 30 #GoogleSheets for #SEO - SEARCH
Combining ISNUMBER and SEARCH can help quickly group keywords.
1. =ISNUMBER(
2. SEARCH(“keyword”
3. The cell you’d like to check
4. Close off brackets
Combining ISNUMBER and SEARCH can help quickly group keywords.
1. =ISNUMBER(
2. SEARCH(“keyword”
3. The cell you’d like to check
4. Close off brackets
Day 23 of 30 #GoogleSheets for #SEO - VLOOKUP
This is a formula worth mastering. Pull data from one sheet into another, ideal for collating information from different sources in just one sheet.
1. =VLOOKUP
2. Select the cell you’d like to find information for (A2)
1/3
This is a formula worth mastering. Pull data from one sheet into another, ideal for collating information from different sources in just one sheet.
1. =VLOOKUP
2. Select the cell you’d like to find information for (A2)
1/3
3. Select where you’d like to get that information from. Often this is another tab or sheet. A simple method here is to select all columns
4. Input the column where the data you’d like to merge is found. In the example, this is the 2nd column in the data source (ExtBackLinks)
2/3
4. Input the column where the data you’d like to merge is found. In the example, this is the 2nd column in the data source (ExtBackLinks)
2/3
Set “is_sorted” to false
This is super powerful and worth exploring. If you’d like to explore this in Excel then check out distilled.net/excel-for-seo/… for a more detailed tutorial.
3/3
This is super powerful and worth exploring. If you’d like to explore this in Excel then check out distilled.net/excel-for-seo/… for a more detailed tutorial.
3/3
Day 24 of 30 #GoogleSheets for #SEO - IFERROR
Combine IFERROR with VLOOKUP to tidy all those #N/A’s.
1. =IFERROR(
2. Complete your VLOOKUP (see Day 23!)
3. Close brackets and add ,“No Data”
4. Close brackets again!
Combine IFERROR with VLOOKUP to tidy all those #N/A’s.
1. =IFERROR(
2. Complete your VLOOKUP (see Day 23!)
3. Close brackets and add ,“No Data”
4. Close brackets again!
Day 25 of 30 #GoogleSheets for #SEO - JOIN
Quickly build URLs using JOIN. Also great for piecing together some keyword research.
1. =JOIN(
2. “/” or a delimiter of your choice
3. Select the cells you’d like to merge
4. Job done 😃🌟
Quickly build URLs using JOIN. Also great for piecing together some keyword research.
1. =JOIN(
2. “/” or a delimiter of your choice
3. Select the cells you’d like to merge
4. Job done 😃🌟
Day 26 of 30 - REGEXMATCH
And we have reached the final week of 30 days of #GoogleSheets for #SEO.
We’ve swung all the way from SUMIF, over to VLOOKUP, brightened up our week with some Conditional Formatting and are now in the depths of REGEXMATCH.
1/2
And we have reached the final week of 30 days of #GoogleSheets for #SEO.
We’ve swung all the way from SUMIF, over to VLOOKUP, brightened up our week with some Conditional Formatting and are now in the depths of REGEXMATCH.
1/2
Day 26 of 30 #GoogleSheets for #SEO - REGEXMATCH
1. =IF(REGEXMATCH(
2. Select cell to be checked
3. “resources” or your own query!
4. Close brackets and enter copy to be returned if the statement is TRUE.
2/2
1. =IF(REGEXMATCH(
2. Select cell to be checked
3. “resources” or your own query!
4. Close brackets and enter copy to be returned if the statement is TRUE.
2/2
Day 27 of 30 - Extract a Domain
Extract domains quickly and easily using #GoogleSheets. All you need to do is:
1. =REGEXEXTRACT(
2. Select the URL to extract the domain from
3. Copy and paste this regex: ^(?:.*://)?(?:www\.)?([^:/]*).*$
4. Make sure the regex is wrapped in “”
Extract domains quickly and easily using #GoogleSheets. All you need to do is:
1. =REGEXEXTRACT(
2. Select the URL to extract the domain from
3. Copy and paste this regex: ^(?:.*://)?(?:www\.)?([^:/]*).*$
4. Make sure the regex is wrapped in “”
Day 28 of 30 #GoogleSheets for #SEO - More CONCAT
Back on day 1, we showed you how to use concatenate.
Did you know you can also use it with a combo of text and cells to quickly build titles?
1. ="write something " (note the space!)
2. &A2
🔥🔥🔥
Back on day 1, we showed you how to use concatenate.
Did you know you can also use it with a combo of text and cells to quickly build titles?
1. ="write something " (note the space!)
2. &A2
🔥🔥🔥
Day 29 of 30 - INDEX / MATCH
Combined these two can operate similar to VLOOKUP, but for rows and columns!
1. =INDEX(select all your data
2. ,MATCH(month selector, all your data, 0) - note zero here means exact match!
1/2
Combined these two can operate similar to VLOOKUP, but for rows and columns!
1. =INDEX(select all your data
2. ,MATCH(month selector, all your data, 0) - note zero here means exact match!
1/2
3. Repeat for the conversion page. So here this is MATCH(G3,B2:D2, 0)
The section on the right now highlights January’s blog conversion. To expand this work simply turn the month and conversion page sections into drop-down selectors!
2/2
The section on the right now highlights January’s blog conversion. To expand this work simply turn the month and conversion page sections into drop-down selectors!
2/2
Day 30 out of 30 - QUERY
And so it has arrived, the final day of our #GoogleSheets for #SEO.
Hopefully, you’ve learnt a trick or two along the way, we’ll be publishing this as a post in the near future for you to reference.
And so it has arrived, the final day of our #GoogleSheets for #SEO.
Hopefully, you’ve learnt a trick or two along the way, we’ll be publishing this as a post in the near future for you to reference.
QUERY is great for quickly pulling data into a new tab, maybe for tidying up all your data to be shared with a client.
1. =QUERY(
2. Select the sheet containing your data
3. “SELECT A,B,E”) - choose the columns you want to bring across
4. Done
1. =QUERY(
2. Select the sheet containing your data
3. “SELECT A,B,E”) - choose the columns you want to bring across
4. Done
• • •
Missing some Tweet in this thread? You can try to
force a refresh
