For anyone who works in Digital Marketing, Excel is an important tool that you will use weekly, if not daily. There are so many tips and tricks that can help make you quicker and faster at your job. Whether you’re doing keyword research or need help working with lots of data, I’ve rounded up 6 of my favourite Excel techniques guaranteed to improve efficiency.
Concatenate
“Concatenate” means to connect things together in a series, and that’s exactly what we can use it for in Excel – joining together multiple cells of data to create a single string.
This is helpful when you’re conducting keyword research and want to build a list of keywords that share a single element, for example, “hairdressers in Leeds”, “hairdressers in Manchester”, and “hairdressers in London”. Rather than needing to type each of these out individually, or using Find + Replace, the Concatenate (CONCAT) function helps you do this at scale.
The core formula for Concatenate is:
=CONCAT(First Value,Second Value,Third Value)
If we have three values such as A, B and C, then the formula will output a single string of ABC. It’s usually necessary to take the formula one step further by adding a blank space between each value, such as:
=CONCAT(First Value,” “,Second Value,” “,Third Value)
This would result in ABC becoming A B C. You might now be able to see how we could use this for building a large list of keywords from the following data:
By adding a CONCAT formula into the blank cells and dragging down, we can quickly generate a list of 27 keywords:
Note: When doing autofill such as in the above, it’s important to “lock on” to certain cells so that the formula doesn’t break, otherwise you may see something like this!
VLOOKUP
VLOOKUP is one of the most dreaded Excel formulas but by far one of the most useful. It lets you look up specific values in tables without needing to follow a convoluted process using the Sort or Find functionality.
Let’s say you have an export of 1,000 URLs worth of data and need to extract a specific value against just a handful of these URLs – it would be a pretty gruelling process to trawl through the list and find them all. VLOOKUP allows you to do that in a fraction of the time.
The core formula is:
=VLOOKUP(value1,range,column#,FALSE)
Here we are asking Excel to “look up” value1, in a specific range and extract a value from column#. The final element FALSE says that we want an exact, rather than approximate, match. Note: value1 must reside in the left-most column of range for it to work.
Putting this into practice, we might have exported a list of top landing pages from Google Analytics and want to pull out the bounce rate for a specific set of pages in this list. We would have our original export from GA:
And then create a separate table with the specific URLs we want the data for:
The VLOOKUP formula would go in the first blank cell in this table, and look something like this:
=VLOOKUP([url],[TableX],5,FALSE)
With the resulting output being:
Text to Columns
The Text to Columns functionality in Excel is almost the opposite of Concatenate – it allows you to split a string into multiple cells by using a specific character divider or width setting.
This comes in handy for splitting out URLs by their directories so that you can use this information for keyword research based on the pages that sit on a website. For example, we can turn this single string:
Into these separate cells:
This is achieved by using the forward slash (/) as the divider. First, highlight the cell(s) that you wish to divide and select Text to Columns on the ribbon (under the Data tab). Select “Delimited” (or you can use “Fixed Width” to determine various specific points within the string that you’d like to use as the divide points):
Enter the forward slash as the divider and you’ll see a preview of the output. If this is what you’re looking for, simply hit Finish to get your split cells!
Removing Duplicates
There are a lot of ways to remove duplicate values from a column, but this one is a real timesaver when dealing with large amounts of data. This is particularly useful if you’ve built a keyword list using a third-party tool like Semrush or Ahrefs and want to get rid of a lot of duplicated keywords.
Here’s a standard Semrush export showing a list of keywords that a site ranks for (Argos.co.uk). The site ranks for “ninja foodi” in positions 3 and 4, probably due to an indented SERP result:
This export is 10,000 rows long, so it would take a while to go down the list and remove each one manually! I could use conditional formatting to highlight duplicates (which I’ve done here) and then sort by colour, but again it’s more steps than is necessary.
For this purpose, the Remove Duplicates function is perfect. It’s located on the top ribbon under Data, and depending on your version of Excel, could be quite hard to find.
It’s circled in red here:
You’ll then be given the option to remove duplicates from just one column, or expand it to the rest of your dataset:
Usually, just ticking the primary column can be sufficient here, but in some cases, you may want to match against multiple duplicate values.
Simply hit “OK” and Excel will find all the rows with duplicates in the columns that you’ve selected and remove them instantly, telling you how many have been removed and how many remain:
Text formatting
This one can save loads of time if you have cells of data in mixed casing and you want to get them into a consistent format.
Going back to an earlier example, let’s say our keyword research had been output in the following format, which really doesn’t look good when presenting it to other people:
We can immediately put this into one consistent format, either all lower case, all upper case or all proper case (which capitalises the first letter of each word).
The core formula for each of these is:
=LOWER(value)
=UPPER(value)
=PROPER(value)
For example, I can transform all of these cells into a consistent capitalised format using this formula and then fill down the rows:
To get this output:
Pivot tables
Last but certainly not least is pivot tables. This is probably one of the most underutilised features of Excel because it can look really complicated at first glance, but the power of data analysis that comes from them is really impressive.
A pivot table allows you to group and view large datasets in a more accessible way, aggregating common values to provide a summary of all the data, broken down however you specify.
For example, you can quickly calculate the total search volume or number of keywords assigned to a specific category, or the number of links pointing to a specific page type. It all depends on how you format your data to begin with, ensuring to create dimensions for you to aggregate against.
I’ll use a simple dataset for the purpose of this post, but it should give you an idea of how you could build upon this and utilise the full power of creating a pivot table:
In this example, it’s fairly easy to calculate the number of keywords and total volume per category, but we’re only dealing with eight keywords. If this list was in the hundreds, then the job would become much more complicated, and this is where a pivot table would come in handy.
To create a pivot table, just click anywhere within your dataset and then Insert > Pivot Table. Or, if you trust Excel to interpret your data correctly (and it normally does), then you could use Recommended Pivot Table.
You can then start to create your table by mapping out the desired rows and values that you’d like to display. In our example we’re going to use Category as the row, then the number of keywords and total search volume as the values:
The output looks like this and gives you a digestible way to present this data back:
Some of these techniques just need practice so you become more familiar with them, and realise their full potential. But, hopefully, this post has given you some ways to make your day-to-day more efficient! Head over to our blog to read more of our digital marketing insight, or get in touch if you’d like to find out more about how our SEO services.
