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.
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.
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.
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.
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_country and criteria as the USA.
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.
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 for joining separate text strings into one quickly.
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 in China.
Digital Marketing Uses
LOOKUPs are 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
By clicking OK, the chart below is created.
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:
- Making interactive dashboards to allow management to monitor KPIs.
- 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.
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.
Step 3: The pivot table will be created on a separate spreadsheet after clicking the OK button.
Digital Marketer Uses
As with the above example, Pivot Tables and their extensions, Calculated Fields and Pivot Chart, are very broadly applicable.
- PivotTables can be used to summarize any large or complex dataset to analyze it.
- You can also perform calculations within the pivot tables to create Calculated Fields.
- With PivotChart, you can easily convert your PivotTables into visuals.
- 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.
Related Articles
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?
