Editor’s Note: Excel in marketing should be a big priority. Are you an excel wizard? You might be a good fit for Ladder! We’re hiring in New York, Austin, London, and Wroclaw.
Most marketers can’t use Excel. However, using excel in marketing is imperative.
I know it’s not sexy, but the World really is run using Excel spreadsheets. (thanks Microsoft!)
At Ladder, we couldn’t do our jobs without Excel; if you read our post on our hiring, you’ll know you can’t even get a job here without basic knowledge of Pivot Tables and VLookups.
But what happens after someone starts working for us? You don’t need a lot of Excel marketing knowledge to pass the test, and everyone has incomplete knowledge of what’s possible. I’ve seen staff literally wasting days of their time doing unspeakable things in Excel when there was a formula or command that could do it for them instantly.
Even if you once did know the best way to do something, laziness or hazy memory can mean you spend hours doing something inefficiently.
Sure you can Google (if you remember what the technique was called), but half of the content that shows up is spammy, unhelpful, and not usually tailored to your marketing use case.
Our strategists were struggling. So I built a tutorial.
To make your life easier, it’s actually made in Excel:
Rather than give useless generic examples, I used real examples (anonymized data) that I’ve run into over my career; that’s 6 years and $25m of marketing budget you’re tapping into in our Excel tutorial.
Each tab is a new use case, and they get harder as they go from left to right. The first few are super simple but less obvious than you would think; I’ve seen enough people waste plenty of time that I had to include them.
I’ll go through and explain each one, but I recommend you have the sheet open and play along to really ‘get’ it.
Everyone knows how to copy and paste, but a lot of people don’t realise that you can use paste special to paste as text. This strips out any formatting from the source you copied it from and saves you a huge amount of time.
Useful for: copying table data from a web page and pasting without all the links / formatting.
Another useful way to use paste special in Excel; copy cells that have a formula in them, then paste special > values over the same cells. Now they have the same values as the formula worked out, but none of the issues with formulas (slow to reload, can’t easily filter).
Useful for: replacing a temporary formula with the results to improve load times / allow filtering.
What if you want to keep your formatting, but you want to remove hyperlinks in Excel? This is a weird trick and I’m still not sure why it works, but it does. Type the number 1 in a cell, copy that cell, then paste special > multiply over the selection. All the hyperlinks are now gone.
Useful for: copying table data from a web page and keeping formatting but removing links.
Sometimes you need to start a cell with ‘+’ or ‘=’ but pesky Excel thinks it’s a formula. It’s actually easy to avoid this; just add a single quote ‘ to the beginning of the cell and the special characters will be escaped, meaning Excel will know it’s text, not a formula.
Useful for: broad match modified keywords (i.e. +keyword) or building utm parameters (-a1-c2).
Need to flip your data from columns to rows (or vice versa)? Paste special > transpose is a super easy way to do it. No more mind-numbing copying one by one.
Useful for: making vertical reports horizontal, or horizontal reports vertical.
Ever sat and watched over someone’s shoulder as they endlessly scrolled all the way to the end of a data set in Excel? It feels painfully slow once you know this trick. Pressing Command (Control on PCs) and the arrow keys allows you to quickly jump around the data. Combined with shift you can quickly select a whole column or row, or select the enter data set by pressing Command + A.
Useful for: rapidly navigating around data sets and making quick row / column selections.
We’ll move on to pivot tables and other ways to split the data later, but most of the time you can find the information you need with simple filters. Combine multiple filters to really drill down to just the campaigns you want to look at.
Useful for: drilling down to see only campaigns that fit certain criteria.
When you combine filters with highlighting, it gets pretty powerful. You can filter for the campaigns you need, make them a certain color, then sort by color to get them all to the top. You can also use filtering and coloring in Excel to visually segment campaigns by various metrics, for example is the campaign active or not?
Useful for: sorting and visually segmenting bulk sheets of ads.
Everyone knows you can drag down formulas. What most people don’t know is when to use cumulative vs absolute references. The difference; an absolute reference will never change when you drag it down; a relative reference will drop down in line with the formula. If you have a static cell that you want the formula to use (i.e. take the value in cell E5) , use an absolute reference. If you want the formula to drop down along with the rows in the column (i.e. take the value in column E, on the same row as this formula), then use a relative reference. You can do pretty advanced dropdown formulas by playing around with relative, absolute and partial references, for example working out cumulative totals.
Useful for: incorporating constants in formulas or working out cumulative totals.
If formulas are a great way to add logic to your spreadsheet formulas. For example if you wanted to set a floor of $50 to your CPC bid, you could check IF bid < $50, then make it $50, otherwise leave it alone. You can even do nested IFs; an IF statement within an IF statement to get to really complex logic.
Useful for: incorporating logic rules into your formulas, for example to work out your CPC bid.
Tired of seeing nasty #N/A, #DIV/0! or other errors in your worksheet? Use the IFERROR formula to stop them from showing. You can also insert other formulas in IFERROR for more advanced rules. Now you can divide by zero to your heart’s content.
Useful for: hiding errors from your worksheet calculations.
The secret weapon of any marketer. Use this to pull information from one table into another based on a shared lookup value. Super useful for tying different data sources together, for example matching campaign performance from Facebook to product sales from Google Analytics. Data can be on the same sheet or in a different tab.
Useful for: looking up values from one dataset and pulling them into another.
The number one mistake I see with VLookup is when the lookup table has more than one row with the same key. When this happens, you want to use SUMIF, as it totals all relevant keys.
Useful for: looking up values from one dataset and aggregating them based on key.
Index-match is like a hyper efficient VLookup. It’s faster to load because it ONLY looks at the specific columns you need to match to, not the whole table. The additional complexity is worth it when you’re dealing with big datasets and the file keeps crashing.
Useful for: more efficiently looking up data based on a key.
This is simple but super useful. Look up the character count of any cell filled with text. Great for determining if you’re over your character limit when writing ad copy for Google / Facebook. Also useful in conjunction with other formulas (as you’ll see later in the tutorial).
Useful for: calculating length (in characters) of a cell.
Need to replace a word in a cell with another word? Substitute in Excel has your back. This is useful for all kinds of things, but I mostly use it when writing ad copy for lots of different keywords for my Adwords campaigns. For example if you have the keyword you’re targeting in column 1 and the ad copy (with a placeholder keyword) in column 2, you could replace the placeholder keyword with the relevant keyword from column 1.
Hint: try it out in the Excel file if this description is confusing to you!
Useful for: replacing a word in your ad copy with another word.
If you want to get familiar with Excel in marketing, replace a word across your whole spreadsheet, the find-replace wizard is a much easier way to do it. You can also select a specific cell range and only find-replace within that selection.
Useful for: replacing all the occurrences of one word in your sheet with another word.
This formula isn’t too useful on its own, but combined with the next few I’ll show you, it’s a beast! All this does is find the character account at which the string of characters you’re looking for occurs in the cell (if it doesn’t occur, it’ll throw a #VALUE! error). This is a must-know for every excel marketing guru.
Useful for: finding at what character a string occurs (mostly used to support other formulas).
Using the left formula on Excel, you can pull all the characters to the left of a specified string from the cell. This is used very heavily when preparing bulk sheets for ads, or parsing out specific variables in reports based on your naming conventions for ad names / utm parameters.
Useful for: grabbing everything to the left of a string (utm params, url domains, ad names, etc.)
Using the right formula you can pull all the characters to the right of a specified string from the cell. This is the opposite of the left formula, and is often used in conjunction to pull exactly the right information out of each cell.
Useful for: grabbing everything to the right of a string (utm params, url paths, ad names, etc.)
Using the mid formula you can pull all the characters out of the middle of a cell. This is more complicated than the left or right formulas, but way more powerful for those hard to reach strings. This is most often used for pulling out specific parts of a URL (utm_campaign=x). You may use this with the left and right formulas to find the bookends of the string you’re pulling out.
Useful for: grabbing everything in the middle of a string (utm params, url paths, ad names, etc.)
Rather than messing around with complicated Left, Right and Mid formulas, sometimes it’s just easier to use text to columns. This works particularly well with data that’s delimited (split by a specific character like a comma, semicolon or space).
Useful for: splitting out url parameters.
The exact opposite of text to columns: use concat in Excel to combine different cells together. This can be really powerful for a number of reasons, but most often I use it for concatenating URL parameters or combining different keywords / descriptions to build lots of relevant ads all at once in a bulksheet.
Useful for: combining values to build URL tracking parameters, or creating ads.
Need to do a VLookup, but need to look up against more than one variable? It’s easier than you’d think! Just concatenate both variables together to create a ‘key’ and use that to do your lookup.
Useful for: looking up data from another sheet using 2 or more variables.
This is useful when you need to remove the duplicates and aggregate their results on excel in marketing. For example if you have multiple entries for each channel for each day and you just want to aggregate all channels into a daily topline performance report, consolidate does the trick.
Useful for: aggregate duplicate entries to provide a topline performance report.
Did you know you can actually name different cells and ranges? That’s right, they’re called “A1” or “A1:B7” by default, but you can change that name to make your formulas much more readable. Super useful for collaborative reports that more than one person needs to work on.
Useful for: referring to ranges or cells by a name you choose to make formulas readable.
By turning your data set into a table, you get the power of name ranges as well as a few additional powerful features. For one, any formula that references a column will automatically include new rows of data that you later add to the table. Any dropdown formulas will automatically drop down to that new row also. This is great for any VLookup formulas you need to use on data that will be updated or added to in future. It’s one of the most frequent tools we use on excel in marketing.
Useful for: referencing the raw data in a report that gets regularly updated with new rows.
Another useful feature of tables? The ability to add totals (and averages) automatically to the end of each column. They update when you add new rows. Pretty neat!
Useful for: quickly adding totals and average to reports.
In my opinion, this is the most powerful (and potentially the most feared) feature of Excel. The good news is that it’s actually pretty easy to use; there’s a drag and drop report builder for crying out loud! Pivot tables allow you to take data and easily split it by whatever variable you like. Great for breaking down performance by day, week, month, campaign, creative, audience… you name it! With pivot tables you can do any of the analysis we walked through in our Marketing Funnel Analysis guide. Combine pivot tables with a reference table for the raw data, and you have a report that automatically updates with the click of the ‘refresh’ button, every time you add new data. As a marketer, if you could only learn one thing about Excel, learn pivot tables.
Useful for: quickly segmenting your data with an easy to use drag and drop interface.
I won’t talk too much about charts, because there are a lot of options and the chart builder is relatively intuitive. However I will say this; build your chart based on table (or ideally, pivot table) data so that when you add more data, the chart updates automatically. It also helps to increase the font size on the axes to make it more readable, as well as adding a title so you remember what the chart is showing. Making the bars / lines match your client / companies’ brand colors is also an easy way to add that extra bit of polish.
Useful for: visualizing your performance data.
This is a clever trick for avoiding the pitfalls of averaging averages (seriously, read that article so you understand to never do this again, it’s embarrassing). It works by indexing the ‘average’ metric against a continuous metric; in the example above we’re indexing relevance against impressions. Then we divide that total of that column (RELxIMP) by impressions again to get the global average. This is very useful when using a pivot table for averages, because whichever way you split the data you’ll be getting the true average every time, which is important when using Excel in marketing.=.
Useful for: avoiding incorrect results by averaging averages.
Most people don’t know you can easily manipulate dates within Excel. Because the date is actually stored as a number (the number of days since January 1st, 1900), and then just formatted as your local date format, you can easily convert it backwards and forwards or manipulate it to get longer or shorter date names, or just the month, day, year, etc. This is a really useful tool for excel in marketing when combined with pivot tables as you can easily pull a report that shows performance by month, or day of week.
Useful for: manipulating date formatting for creating useful report segments.
If you’ve spent any serious time in Excel, you know how complicated it can get. You try and create a complex formula but it doesn’t work and you can’t figure out why. Your colleagues build these insanely long and complicated formulas and you have no idea how they do it. Well here’s the trick: break it down step by step, then aggregate it. It’s really that simple. Every complex formula is really just a collection of smaller formulas dealing with a single step. Break those baby-steps out into different columns, then when you’re happy with the formula, combine it piece by piece back into a long, monster formula. Do this and you’ll look like an Excel genius in no time.
Useful for: building massive, complex Excel formulas without your brain exploding.
At Ladder, we build software and offer services to help high-potential businesses accelerate their growth. Interested in more than just Excel in marketing?
Sign up to our newsletter to stay up to date with all the latest movements in the field.
More from Ladder Blog
The art world is being disrupted by generative AI, and artists aren’t happy. Generative AI models like DALL-E, Midjourney, and Stable Diffusion were trained by scraping millions of images from the internet, without permission. Artists claim this violates copyright, while the companies training these models argue this falls under fair use.
Read More →In the world of digital marketing and data-driven decision-making, creative testing is a pivotal tool in achieving business growth. Gone are the days of relying on gut feelings or guesswork; now, business decisions are powered by data-validated insights, meticulously collected, analyzed, and validated. This transformative process empowers businesses of all sizes, from established enterprises to budding startups, to thrive in an ever-evolving digital market. This article looks at the practical applications, challenges, and innovative transformations associated with creative testing, offering you valuable insights and actionable strategies to implement in your own digital marketing efforts for achieving growth and success.
Read More →
