Google Sheets Functions For Digital Marketing

In this tutorial, we will take a look at quick simple videos to help you power up your Google Sheets knowledge.

🔥 PRO TIP: You can see all Google Sheet functions here or in a Google Sheet using: =IMPORTHTML("support.google.com/docs/table/25273" , "table" , 1)

Google Sheets Shortcuts #

Shortcuts allow you to work quicker and more efficiently – especially when working on larger datasets.

• Copy:  CTRL + C (Command + C on mac)
• Paste:  CTRL + V (Command + V on mac)
• Cut: CTRL + X (Command + X on mac)
• Select All: CTRL + A (Command + A on mac)
• Find: CTRL + F (Command + F on mac)
• Find & Replace: CTRL + H (Command + Shift + H on mac)
• Select Entire Column: CTRL + Space (Control + Space on mac)
• Select Entire Row: Shift + Space (Shift + Space on mac)
• Copy Formula Down Column: Select the cell with the completed formula, hover over the small square at the lower right-hand corner of the cell and then click and drag down over the cells you want the formula to be applied to.

Magic Trick #

Fastest Way to Open An Empty Spreadsheets.

1. Enter sheets.new in your Google Chrome Browser

2. 🔥 Hit enter!

1 of 30 – CONCATENATE #

Need to merge 2 or more cells together? Use CONCATENATE functionality.

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.

2 of 30 – 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 /

3 of 30 – 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)

4 of 30 – LEN #

Character limits are a pain! Check your copy length in bulk.

1. Type =LEN

2. Select the cell you’d like to check

3. Magic! Drag down over the other cells

5 of 30 – 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

6 of 30 – Trim #

Get rid of rogue spaces in your cells by using TRIM functionality.

A common use case is to remove additional spaces at the end of URLs. These spaces often break future formulas such as VLOOKUP.

1. Type =TRIM

2. Select the cell & hit enter

3. Drag over other rows

7 of 30 – IMPORTRANGE #

Need to grab data from another sheet? You could use copy and paste, , but maybe that data is updated frequently!

Using this formula the data will then automatically update across all linked sheets.

1. Type =IMPORTRANGE

2. Add URL of the sheet to be copied

3. Add the tab name and cell range

8 of 30 – LEFT, RIGHT, MID #

Extract all the character either side of a specific character.

1. Type =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

9 of 30 – COUNTIF #

Quickly calculate how many keywords are ranking above, below or equal to a certain position / number.

1. Type =COUNTIF

2. Select the cells to be counted

3. Enter the criteria e.g “<=5”

10 of 30 – SUMIF #

Quickly tally up sessions or conversions in a subfolder.

1. Type =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

11 of 30 – PROPER/UPPER/LOWER #

Quickly change the case of all your copy using any of the above.

1. Type =PROPER

2. Select the cell to be adjusted

3. Drag down the rest of the column!

12 of 30 – IF #

Writing SEO titles or social copy & fed up of having to check if you have broken your character limit?

1. Type =IF

2. Add Logical expression

3. Add copy for if this TRUE or FALSE

 =IF(logical_expression, value_if_true, value_if_false)

In this case, we are looking for the result “TRUE” if our title tags are over 60 characters.

13 of 30 – SUBSTITUTE #

Building page subtitles for loads of different products?

1. Type =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”)

14 of 30 – 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)

4. Repeat steps 2-3 with any other conditions

15 of 30 – SEARCH / ISNUMBER #

Want to check if keywords contain two feature words? Like “Birthday” and “Card”

The SEARCH function returns the position of a substring within a string.

Combined with ISNUMBER (which provides TRUE or FALSE depending on whether the cell is a number) we can easily see if cells contain particular keywords.

1. Type =AND(ISNUMBER(SEARCH(

2. Type the feature word wrapped in “”

3. Select the cell you want to check (A2)

4. Close off with 2 ))

5. Add another ISNUMBER(SEARCH( and repeat!

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

17 of 30 – Sumifs #

Sum cells based on multiple criteria. Ideal for totalling traffic based on device and subfolder.

1. Type =sumifs

2. Select values to be totalled

3. Highlight the Device column & set criteria as “Desktop”

4. Highlight Landing Page URLs & set as “*products*” (a little bit of regex here)

18 of 30 – IMPORTXML #

Extract all the links (or anything else) on a page into #GoogleSheets!

1. Type =IMPORTXML

2. Insert URL wrapped with “ “

3. Use the XPath query “//@href”

19 of 30 -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. Type =REGEXREPLACE

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.

20 of 30 – REGEXEXTRACT #

Quickly pull categories from URLs using REGEXEXTRACT.

1. Type =REGEXREPLACE

2. Select the cell to be rewritten

3. “football|golf” – | means “or” in regex

4. Drag down

21 of 30 – 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. Type =SPLIT

2. Select the cell to be split

3. Set the character used to decide where to split e.g. /

22 of 30 – SEARCH #

Combining ISNUMBER and SEARCH can help quickly group keywords.

1. Type =ISNUMBER(

2. SEARCH(“keyword”

3. The cell you’d like to check

4. Close off brackets

23 of 30 – VLOOKUP #

This is a formula worth mastering.

Ideal for search for a certain value in a column

1. Type =VLOOKUP

2. Select the cell you’d like to find information for

   In the example, this is (A2)

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

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.

24 of 30 – IFERROR #

Combine IFERROR with VLOOKUP to tidy all the #N/A values.

1. =IFERROR(

2. Complete your VLOOKUP (see formula 23!)

3. Close brackets and add ,“No Data”

4. Close brackets again!

25 of 30 – JOIN #

Ideal for quickly build URLs using JOIN or for piecing together some keyword research.

1. Type =JOIN(

2. “/” or a delimiter of your choice

3. Select the cells you’d like to merge

26 of 30 – REGEXMATCH #

…

1. Type =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.

In this example, we are using the REGEX MATCH formula nested within an IF formula to return the value “Resources Page” if the URL contains the text “resources.”

27 of 30 – REGEXEXTRACT #

Ideal for extract domains quickly and easily

1. Type =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 “”

28 of 30 – More CONCAT #

Back on 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

29 of 30 – INDEX / MATCH #

Combined INDEX and MATCH can operate similar to VLOOKUP, but for rows and columns!

This works because the MATCH formula returns the position of value in a given range and INDEX returns a specific value in a specific range.

Formulas:

• =INDEX(array, row_num, [column_num])

• =MATCH (lookup_value, lookup_array, [match_type]

The example below demonstrates how to pull out the number of conversions on blog pages for January.

1. =INDEX(select all your data

2. ,MATCH(month selector, all your data, 0)

   note zero here means exact match!

3. Repeat for the conversion page.

   In the example below this is MATCH(G3,B2:D2, 0)

30 of 30 – QUERY #

QUERY is great for quickly pulling data into a new tab

Ideal for tidying up all your data to be shared with a client.

1. Type =QUERY(

2. Select the sheet containing your data

3. “SELECT A,B,E”) – choose the columns you want to bring across

Conclusions

Hopefully, you’ve learnt a trick or two along the way

---

Reference #

• Google – Keyboard shortcuts for Google Sheets: Link
• Google – Google Sheets function list: Link
• @Brainlabs – Tweeter Thread: Link
• Brainlabs – Guide to Google Sheets / Excel for SEOs and Digital Marketers: Link

---