Excel basics for SEO: CONCATENATE, VLOOKUP and pivot tables

Three Excel functions that handle most crawl and rankings exports: CONCATENATE to rebuild URLs, VLOOKUP to merge sources, pivot tables to summarise.

En bref : Three Excel functions cover most SEO data work: CONCATENATE to make exported URLs consistent, VLOOKUP to bring data from different sources onto the same row, and pivot tables, with pivot charts, to group and visualise large datasets.

Originally published in French in June 2018. Read the French version. Screenshots and charts are in French.

This article covers the first part of my talk at SEO Camp Day Lyon 2018. Formulas are given here with their English Excel names; the screenshots come from a French version of Excel.

Excel is an essential tool for anyone working on a computer, and especially for SEOs, who handle a lot of data. Excel can save real time, provided you know the key formulas and, above all, how to use them. The first part of the talk covered the Excel basics: simple, but worth mastering before moving on to more advanced tools.

Excel basics for SEO

CONCATENATE: rebuilding full URLs after an export

In SEO, depending on the tool used to export data, URLs can come in different formats (with or without the domain, in particular), especially in exports from Google Analytics and log files. The CONCATENATE formula reshapes URLs to make them consistent. You can then bring together data from different sources (Google Analytics, Google Search Console, Majestic, Semrush) with a VLOOKUP formula.

In practice, how do you do it?

=CONCATENATE("http://www.example.com",[@[Landing page]])

http://www.example.com: first value of the string.

[@[Landing page]]: cell holding the end of the URL.

Excel: the CONCATENATE formula

VLOOKUP: bringing data together from different sources

Once all the URLs exported from the different sources share the same format, they can be gathered in a single table. This is where VLOOKUP comes in: it fetches data from table B into table A. In one table you can then bring together visits, backlinks, number of rankings and so on. That lets you correlate several tables by putting related data on the same row.

In practice, how do you do it?

=VLOOKUP([@Pages],TableGA[[URL]:[Visits]],2,FALSE)

[@Pages]: the value looked up (here, the full URL).

TableGA[[URL]:[Visits]]: where to search (here, the table of visits exported from Google Analytics).

2: number of the column holding the data to fetch (here, visits are in the second column of the selection).

FALSE: do not allow an approximate match.

Excel: the VLOOKUP formula

Pivot table: grouping the data in a table

A pivot table sorts a lot of data without rebuilding tables by hand each time. The data is summarised so it can be analysed, then turned into a chart that is easier to read. A pivot table also applies filters automatically. It brings together all the information sharing a common denominator, to help you find answers.

Here is a semantic analysis where I gathered keywords about security cameras, sorted by category and sub-category. The pivot table groups the keywords by category and sub-category, and a linked pivot chart makes it concrete.

To start, in Excel, click PivotTable in the Insert tab and choose where to place it. By default, the table is inserted in a new sheet.

Excel: inserting a pivot table

Fields are arranged by drag and drop. Adding several rows builds a nested table with a hierarchy between values; the order of the rows sets that hierarchy. Fields added to Values are the ones displayed in the columns.

Here I put the feature group first and the feature second, so that features are nested inside their groups. Then I put search volume and competition in Values, to weigh search volume against the average competition of each semantic field.

Excel: arranging pivot table fields

You can change the default value type chosen by Excel by clicking Value Field Settings. Here, for example, I replaced the sum of competition with its average, which makes more sense for this data.

You can also get back to the source data at any time by double-clicking a row. For example, to list every keyword in the dashcam topic, just double-click it. The keywords appear in the pivot table and can be hidden again by clicking the minus sign next to the topic.

Excel: pivot table with detailed rows

Pivot chart: illustrating the pivot table for the client

Once the pivot table is done, you can link it to a pivot chart to make the data more visual, especially if it is meant for a client.

Select the pivot table and click Recommended Charts in the Insert tab. Then pick the right chart for the data: for this semantic analysis, I chose a combo chart with a secondary axis for my second metric, competition.

Excel: inserting a pivot chart

The chart is ready, and if the source table changes, the pivot table and pivot chart follow once the data is refreshed.

You get a visual result that lets you draw conclusions straight away. In this semantic analysis, the most interesting topics to work on for SEO are IP cameras and spy cameras. Competition on the second axis puts search volume into perspective.

Excel: pivot chart of a semantic analysis

That covers the Excel basics. For log analysis done with these same tools, read the 6 decisive SEO criteria.

Questions fréquentes

Which Excel functions are most useful for SEO?

Three basics cover most needs: CONCATENATE to rebuild full URLs after an export, VLOOKUP to bring data from different sources into a single table, and pivot tables to sort and summarise a lot of data without rebuilding tables by hand. They are simple, but worth mastering before moving on to more advanced tools.

How do you use VLOOKUP for SEO?

Once URLs from every source share the same format, VLOOKUP fetches data from one table into another, for example visits exported from Google Analytics next to backlinks or rankings. The formula takes the value looked up, the table to search, the number of the column to return, and FALSE to forbid an approximate match.

Why use CONCATENATE on SEO exports?

Depending on the tool, exported URLs come in different formats, with or without the domain, especially in Google Analytics exports and log files. CONCATENATE adds the missing part so that every URL becomes consistent. Data from Google Analytics, Google Search Console, Majestic or Semrush can then be brought together with VLOOKUP.

How does a pivot table help with keyword research?

A pivot table groups keywords by category and sub-category without rebuilding tables by hand, and can weigh search volume against the average competition of each semantic field. Linked to a pivot chart, for instance a combo chart with competition on a secondary axis, it shows at a glance which topics are worth working on.

Leave a Reply

Your email address will not be published. Required fields are marked *