Digital Analytics

Excel and Sheets For Marketers | CXL Course Review

Essential skills every marketer needs to know

Welcome to week 7 of my journey to Digital Analytics CXL institute Minidegree. This lesson is about using Excel and Sheets as tools for digital analytics. The goal is to learn how to find actionable marketing insights using Excel and Google Sheets.

Excel and Sheets are very valuable tools for various marketing tasks. Which one will you choose is completely up to you (and/or the organization you’re working for, or with). If you need help with deciding, try finding your answer in this article: Google Sheets Vs. Excel, or search Google for another opinion.

“Excel and Sheets for Marketers” course include 17 lessons with a total duration of close to four hours. Once again, Fred Pike is the instructor. You might remember him from the Google Analytics Audit lesson. And once again, he did an amazing job at transferring his knowledge.

I gave my best to cover all the necessary parts, trying not to overwrite at the same time. This is why I chose video instead of words wherever I found it to be a better solution.

Summary

If you don’t have much time or want to read a particular segment of this lesson, search for your topic of interest in this list.

Unfortunately, it’s not possible to link into specific parts of Medium Article, as far as I know. If you do know how to do it, please let me know in the comment section.

• SUM — Variations
• COUNT — Variations
• Tables and Calculated Columns
• Pivot Tables
• Pivot Tables — Excel
• Pivot Tables — Sheets
• Power Tips for Pivot Tables — Excel
• Remove Duplicates
• VLOOKUP
• Index and Match
• XLOOKUP — Excel
• String Functions
• Error Trapping
• Additional Resources
• Conclusion

Prior to starting the lesson, download data from Google Search Console (GSC). You can either use your account, or you can download the data Fred Pike used in his course:

SUM Variations

The SUM function allows you to add up the values in a range of cells.

SUM Commands

• SUM sums everything
• SUMIF sums based on one condition
• SUMIFS sums based on multiple conditions

SUMIF

This function will sum only if a different condition is met.

SUMIF(range, criteria, [sum_range])

• Range: Where is the condition you’re looking for
• Criteria: What are you looking for
• Sum_range: what are you going to sum?

SUMIFS

SUMIFS is very similar to the COUNTIF feature, but rather than counting the specified data, it sums it up.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Note what is different:

• Sum range is first
• Criteria_range1: Where is the first condition you’re looking for
• Criteria1: What are you looking for

COUNT Variations

COUNT counts the number of numeric items

COUNTA counts the number of text items

COUNTIF counts based on one condition

COUNTIFS counts based on multiple conditions

Example:

You have downloaded data from Google Search Console, including queries, page names, clicks, impressions, and country. To see how many queries came from the USA, you’ll use the COUNTIF function.

Tables and Calculated Columns

Table formatting will take your data range and turn it into an interactive database. This will make it easier to create actionable decisions based on that data.

To set up a table, click on any cell in your dataset, and then select Home>Styles>Format as Table. From there, you can personalize your settings until you create a clean and interactive worksheet.

One of the beauties of a table in Excel is the ability to create one formula in an adjacent cell, and have the table copy the formula to extend down the full length of the table and incorporate that new column into the table.

Pivot tables

Simply put, a pivot table is an interactive table that makes evaluating large datasets easier.

Example:

In a pivot table of Google Search Console data, you want to display the clicks. You can use the SUM or AVERAGE function to accurately display click data.

You can add new fields into pivot tables, such as a calculated click-through rate (CTR).

To set up a pivot table, begin by selecting your data and turning it into a table. Then select Pivot Table. Open up a new worksheet and use your pivot table builder to sort your data as desired.

Mynda Treacy made an awesome 13-minutes-long video explaining pivot tables:

Pivot Tables — Excel

Learn how to create a Pivot Chart controlled by a Slicer:

Pivot Tables — Sheets

If you prefer Google Sheets, this tutorial will show you how to make pivot tables in Sheets:

Power Tips for Pivot Tables — Excel

Pivot charts react just as the pivot table does. As you change a filter in the chart, the table will change as well.

Tables and Named Ranges

Give your data table a specific name so you can reference it. When you first create the table, Excel will suggest a name (Table 2, Table 23, Table 32, etc.). You can accept that name or you could change it to a specific name.

What if you’ve created the pivot table and didn’t change the name? Go to Formula and then to the Name Manager — you can make a name change there.

Note:

You cannot put in a dash nor space in the table name. If you want to have a break between words, you have to use the underscore.

Refresh the Data Source

To be able to use the Refresh option in the first place, you have to turn the data source into a table first. If you create a table, you can add a new field easily.

There are several ways to refresh the pivot table:

Turn off “Autofit Columns”

Pivot Charts

Remove duplicates

Good to know:

To use the “Data / Remove Duplicates” option, the data in the column doesn’t have to be sorted.

VLOOKUP

The VLOOKUP function stands for vertical lookup. It allows you to search your table for a certain value and then output its associated value.

If, for example, you are using VLOOKUP with a URL as your lookup value, “Exact Match” is the only way you’ll find the corresponding information for that specific URL — e.g. the page title or the pageviews.

To use VLOOKUP, add a column to your spreadsheet where you will display the found data. Select the first blank cell in this column and click Insert>Function, and then type in VLOOKUP. Once selected, a dialog field will appear allowing you to define four values for your lookup.

Basics of VLOOKUP and HLOOKUP explained:

INDEX and MATCH

VLOOKUP requires the match to be in the very first column. INDEX and MATCH are specifically used to get around this VLOOKUP limitation.

There are (at least) 3 reasons why Excel experts substitute VLOOKUP with INDEX and MATCH:

1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions — left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want to be returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.

TL;DR: INDEX and MATCH is more flexible than VLOOKUP.

XLOOKUP — Excel

This function does not exist in Sheets. It was rolled out in August of 2019.

XLOOKUP can search both vertically and horizontally (so, it replaces both VLOOKUP and HLOOKUP). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:

XLOOKUP(lookup_value,lookup_array,return_array)

• lookup_value: What you are looking for
• lookup_array: Where to find it
• return_array: What to return

Advanced XLOOKUP variations

To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.

XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

match_mode allows you to set the type of match you’d like to perform.

Use zero to perform an exact match (this is the default).

Use 1 or -1 to allow a match against the nearest smaller or larger item when there is no exact match.

Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters.

search_mode lets you configure the type and direction of search.

Use 1 or -1 to search from first-to-last or last-to-first.

Use 2 or -2 to do a binary search on sorted data.

Why should you use XLOOKUP instead of VLOOKUP?

VLOOKUP has several well-known limitations which XLOOKUP overcomes:

1. Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll get the wrong answer.
2. Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. So, if you insert or delete a column you must change the column number inside the VLOOKUP.
3. Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left. This means that you sometimes have to rearrange data.
4. Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data.
5. Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.
6. References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

String Functions

String functions are functions that allow you to manipulate blocks of text in different ways. Let’s explain the most valuable of them.

LEN

LEN function is a text function that returns the length of a string/ text.

=LEN(B3) will calculate the given text or string in cell B3

=SUM(LEN(B3),LEN(C3),LEN(D3)) will calculate the total number of characters in different cells (B3, C3, and D3 in the given example)

=LEN(TRIM(B3)) will count characters in excel, excluding leading and trailing spaces

=LEN(SUBSTITUTE(B3,” “,””)) will count the number of characters in a cell, excluding all spaces

SUBSTITUTE

The SUBSTITUTE function is used to replace a given text with another text in a given cell. It has four parameters:

• text, old_text, new_text are compulsory parameters, and
• instance_num is optional. It specifies the occurrence of old_text. if you specify the instance only, that instance will be replaced by a substitute function; otherwise, all instances are replaced by it.

=SUBSTITUTE(B3,”_,” “1) will replace the first instance of “_” with space

=SUBSTITUTE(B3,”_,” “) will replace all instances of “_” with space

Note:

1. The SUBSTITUTE function is the case-sensitive function!
2. SUBSTITUTE function does not support wildcard characters (i.e. “?”,“*”)

FIND

The FIND function in excel is used to find the location of a character or a substring in a text string. It returns the position of the 1st occurrence of find_text in within_text, and has three parameters:

• find_text: The text to find.
• within_text: The text string to be searched within
• start_num: (optional) It specifies from which character the search shall begin. The default is one.

Note:

• As the SUBSTITUTE function, the FIND function is also case sensitive and does not allow wildcard characters
• If the find_text contains more than one character, the position of the 1st character of the 1st match in within_text is returned.
• If find_text is an empty string “”, the FIND function will return one.
• If the Excel FIND function cannot find find_text in within_text, it gives #VALUE! error
• If the start_num is zero, negative, or greater than within_text, the FIND function returns #VALUE! error.

SEARCH

The SEARCH function is a text function, which is used to find the location of a substring in a string/text. It has three parameters: find_text and within_text are compulsory parameters and start_num is optional.

• find_text: refers to the substring or character which you want to search within a string or the text you want to find out.
• within_text: where your substring is located or where you perform the find_text.
• start_num: from where you want to start the SEARCH within the text in excel. if omitted, then SEARCH considers it as 1 and starts searching from the first character.

FIND and SEARCH explained:

MID

MID Function is used to extract a small part of the string from the input string or return a required number of characters from text or string. The MID formula in excel has three compulsory parameters:

• text: a text from which you want to extract the substring
• start_num: the starting position of the substring
• num_chars: the numbers of characters of the substring

Error Trapping

If you lock a whole spreadsheet, users will be able to see the data in the spreadsheet but not be able to enter additional data. You can unlock sections of the spreadsheet to allow data input.

Named ranges, which are one of the distinguishing marks of the spreadsheet-power-user, can be used all over the place, not just in pivot tables. VLOOKUP, XLOOKUP, and multiple other applications come to mind. The one thing they can’t have is spaces — or dashes — in their name.

Additional Resources

• Ben Collins — Google Sheets developer blog
• Google sheets help center
• Excel help & learning
• Analytics Edge
• G Suite marketplace

Conclusion

You can’t have good analytical skills and be bad at Excel and/or Sheets. If marketing is part of your life for some time, many of the functions explained here are well-known. But, even if you haven’t opened either Excel or Sheets not once in your life (which I find hard to believe, TBH), at the end of this course you’ll have decent Excel and Sheets skills.