The Best Excel Formulas for Digital Marketing

The Most Useful Excel Functions for a Digital Marketer

As a digital marketer, there are a lot of metrics and data to analyse. Generally this is done in Excel, and can be sped us massively with some useful formulas. This is by no means an exhaustive list, but formulas that I find will usually get the job done day to day. Drop me an email if there are any things you need to calculate that you can’t find here and I’ll give you a hand! I also have another sheet with all the most useful digital marketing metrics

Numerical Analysis

Countif

COUNTIF =COUNTIF(range to analyze, reference cell)

Counts how many times a value appears in a range of data. Useful for counting number of keywords or ads in an ad group

Sumif

SUMIF =SUMIF(range to analyze, reference cell, column to sum)

Useful for adding up the number of results for a specific value. Can be useful if you copy the table column, remove duplicates and then SUMIF to count the totals. Often can be more easily achieved with a pivot table.

Vlookup

VLOOKUP  =VLOOKUP(reference cell, range to look across, number of columns across, 0)

One of the most useful of all the Excel functions. This looks up data for your cell across equivalent cells so you can easily extract data. Most useful for copying across URL from one ad group to a new set of keywords or similar. Can also be used just to check if values from one table appear in another table

Find and Replace

Super useful for all kinds of things. * represents unlimited wildcards, ? represents one. Shortcut: ⌘+shift+h. Make sure that this has worked as you expected afterwards because there’s nothing worse than to find out you did a faulty find and replace and then did an hours work after, making the undo function not an option

Date Tools

Weeknum

WEEKNUM =WEEKNUM(date cell to analyze)

Returns the week of the year that a date is in. Useful to graph data by week rather than by day

Weekday

WEEKDAY =WEEKDAY(date cell to analyze)

Returns the day of the week that a date is. Useful to graph data by day of week rather than by day

 Text Editing Tools

Len

LEN =LEN(cell)

Counts the number of characters in a cell. Very useful for counting ad copy things

Proper 

PROPER =PROPER(cell to analyze)

Capitalizes the cell’s content. Watch out for ‘s as it will capitalise them too

Left/Right

LEFT/RIGHT =LEFT(cell to analyze, number of characters)

Returns a certain number of characters from the left or right of another cell. Can be combined with the LEN function to remove a certain number of characters from the left of right of a cell

Lower

LOWER =LOWER(cell to analyze)

Makes the cell’s content all lower-case

Trim

TRIM =TRIM(cell to analyze)

Removes any spaces that may be at the beginning or end the selected cell

Ampersand

& Symbol = cell&”text”&thing&”text”

Adds cells or things together. Use the quotation marks if you want to append text onto things.

Pivot Tables

Pivot tables are awesome because you can really quickly and easily build whatever table you want and switch around all the rows and columns REALLY easily. This is pretty handy, and allows you to manipulate data to analyze it in many ways.

To make a pivot table, first select all the data you want to pivot (this is generally just all the data you have). At the top left of your screen click into the Insert tab and then the Pivot Table button. Press enter (or OK) to build the pivot table in a new tab. Now all you have to do is choose what data you want to display and how!

You should see something that looks like this:

Selecting Fields

The field names are created from the columns of the data you selected initially. You can drag these into whatever boxes you like, but bear in mind, everything will look weird if you drag columns that contain numbers into the Columns or Rows boxes.

NOTE: don’t worry too much about the Filters and Columns boxes. They aren’t much needed apart from for special tasks.

The two boxes you will generally use are Rows and Values. Drag whatever numbers type data (GA calls these Metrics) that you wish to view into the Values column. Drag whatever name type data (Dimensions in GA) into the Rows box.

IMPORTANT: never drag percentages or rates into the Values box. Things such as CTR, CPC, CVR, etc. need to be calculated. The pivot table adds values up, so if it adds any of these things it will be meaningless. You can change it to make averages, but as well all know, this would also be incorrect

Calculated Fields

To calculate things such as CPC, CTR etc. while you are in the pivot table, look at the top toolbar and click the Fields, Items and Sets button and select Calculated Field. You then just need to give it a name, and tell the pivot how to calculate your value. Just write it like you would in Excel normally, something like “= Cost/ Clicks”. If it tells you something with this name already exists, name it something slightly different so you know which is you calculated field.

If you realise you pivot table doesn’t have the column you want, you can always go to your original data source and insert a column in it (such as adding day of the week or week of the year). Once you have done this, you need to click into the Data tab and click refresh all. Then you should see your new data in the pivot. If you can’t see it, when you are in the pivot table, click Change Data Source and ensure that all your data is selected.