Tapping into the world of Digital Analytics -> Excel and Sheets for Marketers

8 min readNov 17, 2020

You know about those skills which are so essential today, that you don’t even mention them in your resume when applying for the job, because it’s kind of taken for granted that you need to have it?

Well, today I want to talk about one of them, that I find as someone everyone says they know about, but not everyone actually feels as comfortable to work with. So let’s talk about Excel and Google Sheet skills essential for a marketer.

Microsoft Excel was released in 1985 and has grown to become arguably the most important computer program in workplaces around the world. , you generally would use Excel. In business, literally, any function in any industry can benefit from those with strong Excel knowledge. Excel is a powerful tool that has become entrenched in business processes worldwide — whether for analyzing stocks or issuers, budgeting, or organizing client sales lists.

Google Sheets is becoming more and more like Microsoft Excel. Both of the tools are extremely essential in helping you organizing and working with your data.

Using Excel / Sheets for business has almost no limits for applications. Here are some examples:

When planning a team outing to a baseball game, you can use Excel to track the RSVP list and costs.
Excel creates revenue growth models for new products based on new customer forecasts.
When planning an editorial calendar for a website, you can list out dates and topics in a spreadsheet.
When creating a budget for a small product, you can list expense categories in a spreadsheet, update it monthly and create a chart to show how close the product is to budget across each category.
You can calculate customer discounts based on monthly purchase volume by product.
Users can summarize customer revenue by product to find areas where to build stronger customer relationships.
Use complex calculation methods

Excel and Sheets are not going anywhere, and businesses will continue to use them as a primary tool for diverse functions and applications ranging from IT projects to company picnics.

So what are some of the most useful things you need to know about using Excel and Sheets to drive your Marketing Analysis and insights?

What do you use Excel and what Google Spreadsheets for?

There is no right or wrong when it comes to choosing one or another platform. The question that needs to be answered is what are you building the spreadsheet for?

Some of the good use cases for Google sheets could be:

Analyze data that you can pull from an online source using the platform as a web-scrapper.
When you need to use Add-ons to pull data from different sources, such as retrieving data from Google Analytics using the Search Analytics For Sheets Add-on.
When you need to share your analysis with others or need to work together with my teammates to create an analysis or a dashboard.

I use mostly Google Sheets. However, there is one clear use case for Excel that I cannot escape from. Its power! When I need to manipulate huge datasets with thousands and thousands of rows, build pivot tables, calculated fields, etc. Excel turns out to be a very powerful tool to manipulate data and has way more functions and resources than Google Sheets.

Before you start anything — know your data & what you want to get out of it

Way too often the biggest challenge of properly using Excel or Google Sheets is that we get the very 2 first questions wrong.

The most important thing to understand before even opening the file is: “What am I trying to learn or understand from working with the data today?”. I can’t overstate how important having a clear idea of the problem that you are trying to understand and tackle or the question you are trying to resolve is for being able to properly understand how to harness the power of the tool.

And at the same time, what is essential to understand is what is the data that you have at your disposal and what are the pieces of information that you have at your hand, that are inside of the tool, so that you can best understand how to utilize them in the context of your research.

Tips on managing your data inside of the Excel file / Google Spreadsheet

The challenges I faced that requested me to manipulate and manage data were different, but here are some of the most useful tips and tools you can use to manage my tools.

Image Credits: https://www.arre.co.in/humour/excel-finance-accountants-formulas/

1. Organizing your data

The very first step in managing your data tool is to organize and prepare data you have. A piece of advice I recently received that I would definitely recommend you do as well is to start with duplicating your data.

Duplicate your data: Duplicate your ‘Original’ data, having another tab called ‘ In Progress’. 99% of the times you will mess things up and you can lose valuable data. Before that happens, make sure you have a backup. That will help you ensure no mistake is made that can’t be corrected.

There is a couple of other tools and features you can use to organize, check, and get familiar with your data. You can use these at the start, but also during the process of working with your data.

Formatting: You need to learn how to format dates, numbers, and percentages. Also, a useful tip is to always remove hyperlinks that might be in your dataset, which will prevent you from ending up clicking on them all the time.
Filters and Sorts: A very powerful tool in Excel or Google Sheets is to sort your data using one or more dimensions. You can also filter your data by column(s), so you can find specific information for your analysis.

2. Cleaning your data

Image Credit: http://www.polarisstrategic.com/

When you open your tool and go to the Data tab/section, you will find a lot of very useful tools that can help you clean up and further ensure the integrity of your data. Two features that I find most important for cleaning up your data are the following:

Remove Duplicates: Any dataset can have duplicate values, so it is vital to remove those to not have any redundant information. Excel makes this task very easy by providing you an option to remove duplicates from your columns.
Divide your data into columns: If you have a .csv or you simply want to clean your data, removing all rows that have a specific word between dividers (URLs are a classic example here) you can do this by simply clicking on ‘Text to Columns’ under the ‘Data’ tab.

3. Finding information in your data

Recently Excel asked the followers in the Twitter network what their favorite functions are to help them find useful insights out of the data, and no wonder here are some of the most mentioned ones:

Pivot Tables — they are one of the most powerful and useful features in Excel. With very little effort, you can use a pivot table to build good-looking reports for large data sets. By creating a pivot table for your data, you are basically applying a very powerful filter that can perform calculations. This can give you averages and sums of your numerical data grouped by one or more dimensions. Check out more about how to work with the pivot tables here.
V(/H)LOOKUP — is the function that can be used to search data based on a search key. One is used to do a vertical search (V) and the other is to do a horizontal search (H), which is not so common, but very useful when needed. An example can be searching a specific value in a column and in a row. The only limitation of these formulas is that it only performs a search to the right/down direction on the lookup table. Check out more about this formula here.
INDEX / MATCH — To perform advanced lookups, you’ll need INDEX and MATCH. To perform advanced lookups, you’ll need INDEX and MATCH. Learn more about this formula here.

4. Analyzing the data

Finally, these tools are all about data analysis. Here is the shortlist of the formulas you should definitely be familiar with to manage your marketing report:

SUM(), SUMIF(), SUMIFS(), AVERAGE, AVERAGEIF(), AVERAGEIFS(), COUNT(), COUNTA(), COUNTIF(), COUNTIFS(), IF, IFS: All of these formulas are meant to ease your life when it comes to perform basic conditional operations in EXCEL / GSH.
QUERY(): Very powerful formula that replicates the power of SQL in your spreadsheet.
FREQUENCY(): Use this formula to see how many times certain values repeat in a dataset.
UNIQUE(): Useful formula to get unique values from a column. Tip: Combine this formula with FREQUENCY().
String Formulas, such as LEN(), TRIM(), SUBSTITUTE(), FIND(), LEFT(), RIGHT(): There are a lot of formulas to treat strings in spreadsheets.
IMPORTRANGE(): This formula is unique to Google Sheets. It is extremely useful, as it allows you to connect different spreadsheets into a single one.

Tools I’d recommend you to check out

Here are some tools I found extremely useful to learn more on the topic:

About the Program

About CXL — CXL Institute is a leading training provider on all things data-driven marketing. You can learn more about CXL by visiting this page.

About Digital Analytics mini degree — The Data Analytics mini degree at CXL is a power-packed program to learn in-depth skills for the Google suite of analytics tools to become a data-driven specialist who can set up any needed tracking and turn data into insights and money. You can learn more about the mini degree by visiting this page.