Excel and data analysis are part of day-to-day internet marketing.
Google Analytics, Google Search Console, Ahrefs, Moz the list of data providers goes on and on.
No ad to show here.
Crunching all of this data becomes second nature to SEO professionals. They know the Excel formulas and set up required.
However, if you’re an entrepreneur this is only one of your many jobs. The strength and weakness of Excel is that it’s a huge tool. It can do almost any data analysis you can think of, but only if you know about the relevant function.
Having just published a guide to Excel for SEOs, in this article and a follow up one I’m going to share the two biggest time savers that Excel offers for working with web data.
As ever the 80:20 rule applies. These two functions offer far and away the biggest wins.
CONCATENATE
Introduction
Working with URLs in Excel can be slow and painful until you learn about CONCATENATE. Put simply concatenate allows you to add pieces of text together.
So for example, if you have downloaded a list of URLs from Google Analytics, by default it will not include your domain. It will just give you the page address within your domain.
That’s no problem when you are working on your data. However many tools that you will want to upload your data to (for example, ScreamingFrog) will require the data to include your domain name. Unless you know about CONCATENATE you’re now facing manually entering your domain into every single record.
Similarly if you are migrating to a new site structure or domain you will need to set up an .htaccess file to forward your existing pages. This is usually planned out in Excel. Pulling together the final file requires the URL of the existing page and it’s replacement page to be combined in a specific way so that Google can read it. For a large site using CONCATENTATE will save you hours of repetitive work.
There is a two line example of using CONCATENATE to set up an .htaccess file towards the end of this article.
The Basics
CONCATENTATE is very simple to use. You set it up as follows:
In this case both TEXT1 and TEXT2 are usually references to other cells that contain text. They can also be manually entered text though if you wish. To do this you simply put speech marks around your text.
The easiest way to set up CONCATENTATE when you are new to it is via Excel’s menus. This means that Excel walks you through the process.
The example below is taken from Excel 2010.
- Click the FORMULAS tab in the Ribbon.
- Click TEXT in the Functions List group.
- Click CONCATENATE
This will bring up a new dialogue box. See below. This will ask you to input the text that you want to enter into the formula, or, more likely, identify the cells which contain the text that you want to use.
To identify the cells that you want to insert into the formula you need to click the button at the end of the input box. You then click into the cell that you want the formula to use. The cell reference will be inserted into the formula. See Text 1 in the screen shot below, where the user has clicked cell A1. You will also see that in the spreadsheet the cell A1 now has a circulating dashed line around it to help you find the cell easily.
Alternatively you can simply click into the relevant box and type the text that you would like to add. In Text2 below we have typed Blue. As you can see Excel has automatically added the speech marks for us.
When you click into Text2 Excel also adds the Text3 box in case you want to CONCATENATE more than two pieces of text. There isn’t a practical limit to the number of pieces of text that you can work with using CONCATENATE.
Having completed all of this the output of your formula will look like this.
The reason that there is a space between Red and Blue is that a space was ended after Red in Cell A1. If this wasn’t added the two words would appear without a space between them. Alternatively I could have added a space before Blue in the formula.
A Worked Example
In this example we will use CONCATENTATE to quickly produce the redirects needed in an .htaccess file. In this example we are assuming that you are moving to a new domain.
In order to redirect your existing website to a new domain you need to tell Google where all the old pages have been moved to. This requires that for each existing page you tell it where the new page is in the following format.
Redirect /old_directory/old_page http://www.newsite.com/new_directory/new_page
In order to plan this you would pull a full listing of your existing website and then plan out the URLs of the new site. Having done this you would then use CONCATENTATE to quickly pull together the exact text required as the screen grab below shows.
This example shows how CONCATENATE can also be used to quickly add spaces to strings of text. The data for the old website does not have a space after each URL string. The data for the new website does not have a space before each URL string. Without CONCATENATE these would need adding manually.
Please note this is not intended to be used as a tutorial on creating .htaccess files. Further information on that can be found here.
