8 Excel Formulas Every Digital Marketer Needs

Updated Over a Week Ago


SHARE THIS ARTICLE

Digital Marketer Spreed Sheet

Estimated reading time: 10 minutes

Data and analysis are the basis of every great digital marketer.

A/B testing, analyzing campaign results, and reporting on progress all depend on managing the vast amounts of data available from Google Analytics and similar tools.

Here are the 8 Excel formulas and features that every digital marketer needs to know.

1. LEFT, RIGHT, and MID formulas

These formulas work great when working with text and allow you to extract and transform text in Excel cells. 

LEFT

The LEFT function lets you extract a specified number of characters from the left side of the text.

The function has the following two arguments

LEFT(text, [num_chars])

Text: Refers to the cell containing the text you want to extract the characters from

Num_chars: Refers to the number of characters you want to extract counting from the left

In the example below, the SKU column contains text that combines three components: Product Name, Store Id, and Product Code.

Let’s look at how each component can be extracted and put into a separate column.

For Product Name, the LEFT function is used.

Digital Marketer Left Formula

RIGHT

The RIGHT function extracts the specified number of characters from the right side of the text. The function arguments are similar to that of the LEFT function.

RIGHT(text, [num_chars])

Text: Refers to the cell containing the text you want to extract the characters from

Num_chars: Refers to the number of characters you want to extract counting from the right

This can be used to extract the Store Id.

Right Formuls

MID

The MID function extracts the specified number of characters from the middle of the text. The MID function has three arguments.

MID(text, start_num, num_chars)

Text: Refers to the text you want to extract the characters from

Start_num: Refers to the starting point within the text you want to start extracting text from

Num_chars: Refers to the number of characters you want to extract to the right of the starting point

The Product Code is extracted in the below example using the MID function.

MID Formula

Digital Marketer Uses

This is very useful when working with the page’s titles and descriptions and Google AdWords copy.

2. SUMIF

SUMIF allows you to sum values based on specified criteria. It can also work with multiple criteria.

The SUMIF function works on three simple arguments:

SUMIF(Range, Criteria, Sum_range)

Range: Refers to the range for the specified condition

Criteria: Refers to the condition that you want to use for the calculation

Sum_range: Refers to the range for actual sum operation

In the below example, SUMIF is used to sum the total_sqftof stores situated in the USA.

SUMIF Formula for Digital Marketers

Digital Marketer Uses

SUMIF is very useful when you want to drill down into data. It allows you to get sub-totals by any criteria in your spreadsheet quickly.

3. COUNTIF

COUNTIF is another excellent feature that can speed up your work of processing and analyzing the data. It enables you to calculate the number of occurrences based on specified criteria.

COUNTIF requires two simple arguments.

Countif(Range,Criteria)

Range: Refers to the range that you want to count

Criteria: Refers to the condition that you want to count

In the example below, the number of stores located in the USA is counted using COUNTIF. The number of stores in the USA is calculated by specifying the range as store_countryand criteria as the USA.

COUNTIF Formula

Digital Marketer Uses

COUNTIF counts instances in the same way that SUMIF totals the values of each instance. This is very widely useful. Examples include counting the number of links/clicks that depend on a specific condition.

4. AVERAGEIF

AVERAGEIF finds the average of the values based on a given condition.

The function arguments are very similar to the SUMIF function.

AVERAGEIF (Range, Criteria, Average_range)

Range: Refers to the range for the specified condition

Criteria: Refers to the condition that you want to use for the calculation

Average_range: Refers to the range for the calculation of average

In this example, the average square feet per store in Mexico is calculated using the AVERAGEIF function. We got our desired result by specifying the store_countrycolumn as range, Mexico as criteria, and total_sqftcolumn as average_range.

AVERAGEIF Formula

Digital Marketer Uses

AVERAGEIF follows naturally from using COUNTIF AND SUMIF. Averages depending on conversation rate, ad spend, number of clicks and number of links will always be useful in larger campaigns.

5. CONCATENATE

CONCATENATE function lets you join text strings together in one cell.

=CONCATENATE(text1, text2,[text3])

Text1: this is the first cell containing the text you want to concatenate.

Text2: this is the second cell, and this text will be added to the right of the text from text1

[text3]: This argument is optional. CONCATENATE can work with multiple cells. If you would like to work with three or more cells, you can continue to add references to your formula.

It is simple to use and becomes a handy tool to join separate text strings into one quickly.

Digital Marketing CONCATENATE Formula

Digital Marketer Uses

As with LEFT, RIGHT, and MID CONCATENATE is useful when working with product names, titles, meta descriptions, and the like. For example, if a complete product ID is its product category and name, then CONCATENATE will allow you to generate a complete product ID very quickly.

6. LOOKUP FUNCTIONS

LOOKUP functions allow you to search through a row/column for a specific piece of data and then look up data from the row or column that that data resides in.

XLOOKUP is the improved version of previous lookup formulas like VLOOKUP and HLOOKUP. It is more flexible than VLOOKUP and HLOOKUP but is currently available in Excel 365 and higher versions.

All LOOKUP functions work in the same way though

LOOKUP(lookup_value,lookup_array,return_array)

Lookup_value: The value you want your function to search for.

Lookup_array: The range of cells to search

Return_array: The range of cells to return the found values to

Look at the simple example of using XLOOKUP to find the value of Total Sales for the Outlet location inChina.

XLOOKUP Formula to Enhance Digital Marketing

Digital Marketing Uses

LOOKUPsare incredibly useful when working with large datasets, especially if you want to combine data held in two sheets. For example, you have data from one source relating to keywords in one sheet and data from another source in another spreadsheet. Using a LOOKUP function, you can easily draw relevant data together to compare it in one sheet easily.

7. Charts

Charts and visuals are great ways to distribute information.

Excel offers you many options to transform plain numerical values into impactful visualizations.

You can create a basic chart in a couple of simple steps. In the below example; we have created a bar chart showing the total_sqftby store_country

Step 1: Select the ranges that you want to include in your chart; here, store_countryand total_sqftwas selected

Step 2: Click on the Insert tab and then click on Recommended Charts

Recommended Chart 1

By clicking OK, the chart below is created.

Recommended Chart 2

Excel has various charting options that include bar charts (both vertical and horizontal), pie charts, combination charts, and others.

Digital Marketer Uses

Charts are most valuable when communicating with large numbers of people who are not that intimately involved with the data daily. Examples include:

  1. Making interactive dashboards to allow management to monitor KPIs.
  2. Demonstrating long-term trends in two or more variables.

8. Pivot Tables

Pivot Tables allow you to summarize data in just a few clicks. They allow you to see sub-totals and totals for your data across multiple dimensions.

Here is an example of a large dataset consisting of many rows and columns. We can use pivot tables for summarising this data.

Dataset 1

Before using PivotTable, the data should be in an Excel table. To learn more about tables, see Excel tables.

To insert a Pivot Table;

Step 1: From the Insert tab, click on the PivotTable icon.

Step 2: A window will pop up to define the range or table for the pivot table and another option of inserting the pivot table in the existing or another sheet.

Pivot Table Data Selection

Step 3: The pivot table will be created on a separate spreadsheet after clicking the OK button.

Simple Pivot Table

Digital Marketer Uses

As with the above example, Pivot Tables and their extensions Calculated Fields and Pivot Chart are very broadly applicable.

  1. PivotTables can be used to summarize any large or complex dataset to analyze it.
  2. You can also perform calculations within the pivot tables to create Calculated Fields.
  3. With PivotChart, you can easily convert your PivotTables into visuals.
  4. Using slicers and filters in Pivot Tables, you can make interactive reports.

Closing Thoughts

A large part of digital marketing is data analysis and reporting.

Mastering Excel’s most powerful tools and functions will save you time and give you more time to focus on creative tasks.

As a Digital Marketer Which Of These Formulas Do You Use?

If you have ideas about Excel and digital marketing that might be helpful to readers, share them in the comments section below. Thanks!

Would you like to contribute a post?

Ben Richardson on FacebookBen Richardson on LinkedinBen Richardson on Twitter
Ben Richardson
Ben Richardson
Ben Richardson runs Acuity Training, a leading Excel training company in the UK.
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Learn How Top Leaders Get Results... With Methods Proven Effective By Over 10,000 Leaders!

>