11 Excel Techniques For SEOs

Published on 5/01/21 by

Posted in SEO Tagged

Microsoft Excel is the SEO’s Swiss Army tool. Master it with these 11 must-know SEO Excel tips and techniques.

For anyone that doesn’t work with numbers or data, Microsoft Excel was probably last a part of life in a dingy IT secondary school classroom. This isn’t the case for SEOs, or digital marketers in general. Very quickly after starting a career in SEO, Excel’s strengths become extremely apparent, and it inevitably ends up being an essential tool in daily work tasks.

Why is Excel so essential to SEOs? Simply put, it’s an absolute workhorse. Whether you’re carving up Google Analytics data for client reports or playing with formulas to generate meta data, running a site audit or trimming keyword lists, Excel is up to the job. The depth of Excel’s versatility and efficacy isn’t easily visible on the surface, though, and it can take years of experience to master the functions, formulas, and features that it has to offer.

We’re here to cut that time down by teaching you every foundational technique you might need to use Excel for SEO. This list isn’t an exhaustive representation of Excel’s use cases for an SEO, but it’ll give you all of the basics to go away and figure the rest out by yourself.

VLOOKUP

Function

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.

Syntax

=VLOOKUP(lookup_cell, table_array, col_index_number, range_lookup)

How to use it

When you’re grappling with multiple sets of data in one sheet, trying to link them together in some meaningful way, VLOOKUP is your best friend. This formula is essential in data collation, allowing you to pull values from a table into another dataset, meaning you can create master-view sheets – datasets that compile individual data points from different sources.

The VLOOKUP formula itself explains how the function works fairly simply: you choose a cell that has a value you want to look up, choose a table elsewhere in the same sheet that you want to search for your chosen value, and then pick which column of data you want to retrieve from the row that contains your chosen value in the table.

What does this look like in practical terms? A good example is compiling datasets for site audit purposes. If you’ve run a site crawl and have a list of URLs and associated meta data but want to pull in some user data from Google Analytics or Google Search Console, you’d use a VLOOKUP.

First, you’d make sure that the URL you’re searching for more data from is in Column A and that the dataset you’re looking to pull data from is in an Excel table, with the lookup value (ie. the URL) also in column A. Next, you’d enter the VLOOKUP formula into an empty cell in the sheet containing the site crawl, replacing lookup_cell with the cell that contains the URL you’re looking for, table_array with the name of the table you want to pull data from, col_index_number with the numerical value of the column you want to pull data from (3 for the third column along, etc), and range_lookup with false.

If you’ve done everything correctly, your VLOOKUP cell will now pull the data from the column you’ve chosen and the URL you’re looking for, provided it’s contained within the table you’re searching.

The applications for VLOOKUPs in SEO are boundless and the more you use them, the more you learn how useful they can be. There are plenty of things you can do wrong, but be patient and troubleshoot your datasets and your formula if it isn’t working, and you’ll get there in the end.

CONCATENATE

Function

Concatenates a list or range of text strings.

Syntax

=CONCATENATE(cell1, cell2, cell3)

How to use it

concatenate formula

CONCATENATE is at the core of a lot of the techniques you’ll use as an SEO to manage and manipulate textual data in Excel. It’s a simple formula that allows you to combine the text within several cells into one cell, choosing the order they are strung together in.

It’s particularly useful when you’re trying to build URLs or meta data out of composite parts. For example, if you have a list of blog titles (H1s) that you want to create title tags from, you could concatenate the cells containing the H1s with adjacent cells that contain the remainder of the title tag template.

You can also build speculative longtail keyword lists with CONCATENATE by writing a list of product keyword modifiers (colours or other product attributes) and combining them with keyword roots (product names). You can then check the keyword volume for these speculative terms to see if you’ve hit on anything valuable.

You’ll find many uses for CONCATENATE when playing with textual data, and it’s an essential part of the formulas you can use to create near-automatic meta data generators. The only thing to remember is that CONCATENATE won’t automatically insert spaces between words or characters for you, so you need to manually add spaces into the cells you’re concatenating, where relevant.

LEN

Function

Return the number of characters in a text string.

Syntax

=LEN(cell)

How to use it

character length formula

LEN is one of the more basic default Excel formulas that you can use as an SEO, but its simplicity doesn’t take away from its utility! Simply enter the formula into any cell of your Excel sheet and insert the cell that you want to measure the length of text within into the parenthesis. The cell containing the LEN formula will now accurately measure the character length of the text in the provided cell, updating automatically as you add or remove text.

You can use LEN to measure the length of title tags and meta descriptions, ensuring that they don’t exceed the recommended maximum character counts to display on SERPs. If you add a filter to a dataset with a LEN column in it, you can also sort largest to smallest to get a prioritised list of title tags or descriptions that need to be trimmed.

Text to columns

Function

Split a single column of text into multiple columns. For example, you can separate a column of full names into separate first and last name columns. You can choose how to split it up: fixed width or split at each comma, period, or other character.

Syntax

N/A

How to use it

When playing with website data in an Excel sheet, you’ll often find that there’s value in creating greater granularity by splitting text into composite parts for deeper analysis or easier sorting. The text to columns feature built into Excel is perfectly suited to help you do that.

Its only function is to split text in cells in a single column into multiple adjacent columns (stretching out to the right of the original column). You can find the text to columns button under the data ribbon, and using it is as simple as highlighting the column you want to split, clicking the text to columns button, and then selecting how you want to separate your text.

Your options are to choose your own character as a delimiter (this could be commas, pipes, forward slashes, or any other character) or to use a fixed width of your choosing. Generally, you’ll find that choosing a character delimiter is preferable, as you have greater control over what columns result from the split.

Text to columns can be used by SEOs to separate URLs into their composite parts (by delimiting with forward slashes), separate title tags into sections (delimiting with your title tag dividers, whether that’s pipes, hyphens, or something else), or even splitting multi-word queries into columns.

Search Analytics For Sheets

Function

Retrieve data and create automatic backups from Google Search Console into Google Sheets.

Syntax

N/A

How to use it

search analytics for sheets sidebar

This isn’t strictly an Excel tip, given that it relies on a feature exclusive to Microsoft Excel’s main competitor, Google Sheets. However, the application of Search Analytics For Sheets carries so much potential for Excel users that it would be remiss not to mention it.

Search Analytics For Sheets is an add-on for Google Sheets that you can download from the G Suite Marketplace, allowing you to retrieve huge amounts of data from Google Search Console and place it directly into a Google Sheet. You can then quite easily export or copy and paste this data into an Excel sheet, where you can work on it in a more comfortable setting.

This add-on is so blindingly useful for SEOs because of the limits on exports that Google Search Console has – only allowing for 1000 rows of data to be exported at a time. That means that, to export more than 1000 rows from a proprietary Google Search Console account, you have to use query or page filters to methodically export your target data in chunks.

Instead, Search Analytics For Sheets gives you access to full data exports directly pulled from your Google Search Console account. When you’re trying to get a full overview of the organic search performance of a large site, this is invaluable.

One particularly valuable use case for Search Analytics For Sheets is creating a sheet that collates top queries by page for an entire website. To do this, open the add-on sidebar in a new Google Sheet choose a site from the Verified Site dropdown, pick your date range, and then group by both page and query.

The resultant sheet that’s created will show every query that the site ranks for, along with the page that ranks for it. Copy and paste this data into an Excel sheet, add a filter, sort by clicks (largest to smallest), then by page (A-Z), remove duplicates in the page column, and you’ll be left with a sheet that shows one instance of each URL (that ranks for at least one organic keyword) on the site and its top keyword by clicks.

Weighted averages

Function

A formula that calculates weighted averages to accurately measure comparative importance of components in a dataset.

Syntax

=SUMPRODUCT(cell1:cell2, cell3:cell4)/cell1:cell2

How to use it

Search performance data is notoriously difficult to get clear and accurate information from, given the huge number of variables that it comes along with. Tracking aggregated rises and falls in average position across several queries from Google Search Console is particularly fraught with risk, as you can quite easily get the wrong picture of whether absolute performance has improved or not by forgetting to consider search volume.

Weighted averages are the solution to this pitfall, calculating the comparative importance of individual keywords on overall performance by factoring their search volume into the equation. By using the SUMPRODUCT formula, you can easily calculate weighted averages to compare, giving you the best chance of getting an accurate picture of the direction a set of keywords are travelling in.

Meta data generators

Function

A series of formulas that allow for template based rapid meta data generation, with alterable values.

Syntax

=CONCATENATE(pre_keyword_cell,keyword_cell,post_keyword_cell)

=CONCATENATE(@INDEX(pre_keyword_cell_1:pre_keyword_cell_2,RANDBETWEEN(1,COUNTA(pre_keyword_cell_1:pre_keyword_cell_2))),keyword_cell,post_keyword_cell)

How to use it

Ever tried to create meta data for thousands of pages at once? The first thing you probably thought is, ‘there’s got to be a way to automate this’. Well, as it turns out, there is! If you’re happy to get down and dirty with complex arrangements of individual Excel formulas, you can create practically automatic meta data generators. This trick is applicable to both title tags and meta descriptions, but the methodology is slightly different for each.

For mass title tag generation, you essentially just need to concatenate three distinct values – a pre-keyword value, a keyword, and a post-keyword value. For example, if you have a list of products and their associated primary keyword which you want to generate title tags for, you’d need to decide what the ideal title tag template is and what information should come before and after the keyword.

In the case of used cars, you would probably want the template to be something like: ‘Used Car Model Name For Sale | Brand Name’. Using that example, ‘Used’ would be the pre-keyword value, the car model name would be the keyword, and ‘For Sale | Brand Name’ would be the post keyword value.

Things get slightly more complicated when looking at meta descriptions, particularly if you want to mix things up a little bit by creating variations on a theme with alternate pre- and post-keyword values. However, the basic rules are the same – you need pre-keyword values, keywords, and post-keyword values, arranged in a sheet that allows for concatenation. The main difference is that where you have multiple choices for pre- and post-keyword values, you will need to make use of the RANDBETWEEN formula in your meta data generator.

This formula instructs the CONCATENATE formula to choose a single value from a range of pre-set values to pull into the final cell, and the range can be as large as you want. For instance, if you want to add variation to the opening phrase for a collection of product page meta descriptions you’d list several values (maybe ‘Buy a’, ‘Choose a’, ‘Order a’, etc) in a column and then use the RANDBETWEEN formula to set rules for Excel to choose one of the values at random.

Meta data generators sound more complex than they actually are, and once the basic formula is set up, you can play around with them at will to master creating consistent meta data en masse.

Conditional formatting

Function

Easily spot trends and patterns in your data using bars, colours, and icons to visually highlight important values.

Syntax

N/A

How to use it

conditional formatting colour scale

Conditional formatting is a feature that’s prominently built into Excel, appearing under the main Home ribbon. It’s a tool to help make your sheets more visually interesting and useful, by adding colour to selected cells under specific instructions that you can program.

There are a vast number of ways you can use conditional formatting as an SEO to make your Excel sheets more useful, but the two sets of rules you’ll probably be most interested in are highlight cells and colour scales.

The set of rules under highlight cells includes several simple rules such as highlight cells greater than or less than a certain value, but it’s the slightly less simplistic rules that SEOs can get more value from. The highlight duplicate values rule is particularly useful, doing exactly what it says on the tin and highlighting duplicate textual or numerical values in a dataset with a colour chosen by you. You can also use the highlight cells rule to flag up text that contains certain values, such as a specific word or phrase.

Colour scales has a more varied set of use cases, from creating colour gradients to get a visual representation of a range of data (to visually rank CTR for specific queries, for example) to prioritising keywords based on search volume.

You can also use the data bars feature built into the conditional formatting dropdown to get a visual representation of the relative scales of numerical data, with scaled bars partially filling the cells containing the relevant data.

Text case moderators

Function

A set of formulas that allow you to clean up meta data or achieve the right format for your needs.

Syntax

=CLEAN(cell)

=PROPER(cell)

=LOWER(cell)

=UPPER(cell)

How to use it

Text case moderators aren’t actually categorised together within Excel, but they all serve such similar functions that it makes sense for them to be. These formulas are ideal for tidying up textual information in your cells, either by removing unnecessary characters or correcting case formatting. The main ones you’ll probably use are CLEAN, PROPER, LOWER, and UPPER.

CLEAN will do just what it says it will – clean up text in a given cell by removing all non-printable characters leaving plain text.

PROPER reformats text in a given cell into proper formatting, with capital letters beginning each word but every other letter in lower case. This formula is often used for correcting capitalisation issues in title tags, creating consistency across a site. However, be wary that it will remove capitalisation from acronyms, leaving only the first letter capitalised.

LOWER blanket reformats text in a given cell into lower case. This is sometimes a prerequisite for importing data into third party tools.

UPPER is the opposite of LOWER, blanket reformatting all text in a given cell into upper case. We don’t know why you’d want to use this one, to be honest.

Remove duplicates

Function

Delete duplicate rows from a sheet. You can pick which columns should be checked for duplicate information.

Syntax

N/A

How to use it

remove duplicates tool in excel

Remove duplicates is an extremely useful function for SEOs looking to trim lists or combine datasets. It allows for automatic removal of double appearances of values in separate cells (like the same URL appearing twice in a column).

You use the remove duplicates function on a dataset by highlighting the whole dataset, clicking the remove duplicates button under the data ribbon, ticking the box of the column you want to identify and remove duplicates from, and clicking OK. The duplicate values will be removed immediately, from the top down. That means that you should first organise your data so that the instance of the duplicate value that you want to keep is the first appearance in the column.

In large datasets with multiple columns, you might find yourself having to play around with the ordering of the data using filters before you can remove the right duplicates. Try using the double filter trick (applying a filter in one column before applying a filter in another to nest one ordering within another, like number of impressions within number of clicks) to make sure that you’re preserving the instance of your duplicate that you want to keep.

Find and replace

Function

Search for text you’d like to change and replace it with something else.

Syntax

N/A

How to use it

find and replace tool in excel

Find and replace is a fairly simple function to wrap your head around, but the depth of its use cases might have passed you by in the past. Essentially, the find and replace function is just like the find function that you’ll be used to using by pressing Ctrl+F on your keyboard, but instead of just finding values, it allows you to edit them en masse too.

That opens up some interesting uses for SEOs, such as replacing all instances of ‘http:’ with ‘https:’ to create a redirect map for a HTTPS site migration, removing all trace of file types at the end of URLs, and even adding in host names to URLs that came in the form of just slugs (like Google Analytics data does)

Mastering Excel As An SEO

The 11 tips we’ve outlined above only scratch the surface of what Excel is capable of. There are hundreds of other niche functions that you can use to make your life easier, from pivot tables to cell merges.

Learning to master Excel’s use cases as an SEO is just like learning anything else – with enough reading, practice, and application, you’ll get better and better over time. Our advice is to play with Excel constantly, trying out different formulas on different datasets to see how you can manipulate your data in new and interesting ways.

Related posts

Published on: 12/02/20 by

How To Hunt For Featured Snippets

No-click SERPs are an SEO’s horror story, and featured snippets are the main villain. Since they were introduced as ‘quick answers’ in 2013, featured snippets have spread, not unlike a virus, across Google’s search engine…

Read More »

Published on: 29/01/20 by

The Fundamentals Of Blog Post SEO

Increase blog traffic with 21 easily actionable SEO tips to make each post on your blog more organically successful. No matter how great you think a blog post is, Google has the liberty to disagree.…

Read More »