Read on Twitter
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(" https://support.google.com/docs/table/25273" , "table" , 1)
=IMPORTHTML(" https://support.google.com/docs/table/25273" , "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 https://www.distilled.net/excel-for-seo/#vlookup 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 https://www.distilled.net/excel-for-seo/#vlookup for a more detailed tutorial.
3/3
