Excel Tips & Tricks For SEO’s

By Jason Hawkins on April 7, 2014

An SEO is only as good as the tools he or she knows how to use and the experience he or she may have with search marketing. I have been doing SEO for about 6 years which is not that long, but I can tell you one thing, learning how to use the many different tools out there like Google keyword planner, Google Analytics, Google Webmaster Tools, Google Adwords, SEMrush, Ahrefs and many others is what has allowed me to become efficient with my time. Being efficient with your time as an SEO is critical because many SEO tasks are tedious and require a lot of time.

export to excel

Almost All Tools Export To Excel

One thing that almost all SEO tools have in common is the ability to export into excel. That being said, I think its extremely important for any SEO to have a firm grip on how to use Excel formulas, functions and any other means of increasing your efficiency. Over the years, when ever I wanted to learn how to save time with Excel, I typically Google’d it and found the answer. One thing I havn’t found is a go to source for SEO’s to turn to when it comes to anything related to excel and SEO, a “holy grail of excel tips for SEO’s if you will”. That is why I created this page.

I encourage you to add comments so that I can shed light on functions or formulas that I did not cover. I intend on growing this post by getting feed back from my readers on what I should add or modify.

Table of Contents

LEN & Word Count
Using Standard Tables
Using Pivot Tables
MID
Data Validation Tecniques

 

 

Simple Concatentation

=(A1&” “&B1&” “&C1) Back to Table of Contents

Simple Concatentation allows your to combine data from multiple cells and can be used in many ways. Some of the most basic ways to use the AND function would be to combine or append city or state names to a list of service related keywords. In my experience I have used it a lot for creating keyword ideas in bulk (or URL ideas), I wrote a blog post on how to use simple concatenation to append city names to a list of non geo-specific keywords. The image below does a good job at explaining how simple concatenation works. You choose which cells you want to merge together and simply add an ampere sign between each value. To add spaces between each value, simply include an ampere sign followed by a space within a set of quotations as shown below.

using_simple_concatenation_for_SEO

For example, a client might want to show up for “roofing contractor [city name]”, the client may cover 100’s of cities and wants to show up for each city. In most cases, the search volume on city specific keywords are not that high especially when you are dealing with low population areas. An easy way to create keyword ideas would be to use Google Trends to identify the most commonly used root terms (non-geo specific terms) and then use the AND function to generate many keywords.

CONCATENATE

=Concatenate (text1,text2…) Back to Table of Contents

There are a number of great ways to use excel for SEO and I’ve gotta say, one of the most commonly written about function that I see on the web is the Concatenate function. If you are working at a ppc management company and you need to create ad copy, Concatenate can be used to efficiently create keyword in bulk the same way that was demonstrated in the video for simple concatenation only you will be calling the function by entering “=Concatenate(text1,text2,etc..)”. There are several ways to use Concatenate for SEO such as creating ad copy ideas, on page ideas, URL ideas etc.

You can create a simple excel table to create many Title Tag ideas if you are building a new site and want to make your product page title optimization process more efficient by using Concatenate. For example if you have a auto parts store, you may want to include the brand, part number and category it belongs to. If you have your data organized into columns by brand, part number and category you can create a concatenate reference that will create title tag ideas for you in bulk.
[sociallocker]
Example:
[BRAND] [PART NAME] | Replacement [PART NAME] for [BRAND] [Category]

So if your spread sheet would end up looking like this:

Samsung Projection Lamp | Replacement Projection Lamp for Flat Screen TV
LG Motherboard | Replacement Motherboard for HD TV
Toshiba Projection Lamp | Replacement Projection Lamp for Flat Screen TV

Assuming your brands are in column A, part names in column B and Category in column C your function should look like this:

=Concatenate(A1&” “,B2&” | “&”Replacement “,B2&” for “,A2&” “,C2)

Fundamentally, this is basically just like using concatenate but you can pick your own way of doing it.
[/sociallocker]

Concatrange – User Defined Function

Back to Table of Contents
Concatrange is a user defined function which allows you to combine data from cells in a column into a single column with a separator. For example, if you have a list of keywords that you are focusing on, you can use this user defined function to create a Regex filter if you were trying to segment your Google Analytics data to show only data pertaining to users that found your site using that specific list of keywords. The function can separate each keyword with a line break and you can then take that data, add a set of parenthesis’ around it and you have a regex filter ready to go.

To create a user defined function, depending on how your Excel application is setup, you may have to add the Developer option to your ribbon by clicking on file, then options, then customize ribbon. All you have to do is add the developer option by clicking the check box as shown below.

Adding_developer_to_your_ribbon_for_Excel_

Once you have the developer menu in your ribbon, you can click on “developer” from your top menu just after the “view” button. If you click that, you will need to then click on “Visual Basic” and there you will need to click “Insert” then “Module. This is where the user defined function needs to be placed. Simply copy the following code into your module:

Function ConCatRange(CellBlock As Range) As String

Dim cell As Range

Dim sBuf As String

For Each cell In CellBlock

If Len(cell.Text) > 0 Then sBuf = sBuf & cell.Text & “, ”

Next

ConCatRange = Left(sBuf, Len(sBuf) – 1)

End Function

Once you have the above code in your module, you can call the ConCatRange function at any time after returning to your work book. I created a blog post that shows how to do this in more detail and I also recorded a video demonstrating how to use this function which you can view below. It explains how to use this function when you want to create a Regex code which happens a lot when you are dealing with large eCommerce websites. As a member of a data driven seo company that specializes in PPC management for E-commerce websites, this trick comes in handy every day it seems. I wrote a great blog post on how to combine keywords with excel separated by a coma or line break which explains how to use this function in finer detail. I encourage you to read that post or to watch the video below.

Vlookup Function

=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
Back to Table of Contents

Using the Vlookup function can be used in many ways to combine data from multiple reports. For example, I like to use the function to pull search volume data from one report and aggregate it into my report from Google Analytics that has visitor performance for  a list of keywords. I wrote post on how to do this on the SEMRush blog which you can read by clicking here.

With the vlookup function, you can also do things like aggregate spend data from a Bing Ads campaign report and add it to your Google Adwords or Analytics report to calculate ROI.  In the video below I am putting together an ROI report for a customer that advertises on Bing and Adwords. The ROAS reports in Google Analytics by default do not pull spend data from Bing so you have to add that data manually, or you can use the Vlookup function 🙂 Please excuse the background noise in advance, our office gets quite busy at times with out happy customers consulting with their project managers!

Find Function

=FIND(find_text,within_text,[start_num])
Back to Table of Contents

Aside from just using Ctrl + F on your keyboard to launch the quick finder, you can use the forumla “Find” to easily organize your keyword lists into common themes for Ad groups or for target groups for an organic campaign. Either way, the formula can definitely come in handy and its a cinch to use.

Below I created a short demonstration video that can be used if you have an eCommerce website and you need to sort your keyword data by brand, category, product ID’s etc… In the video example, I was organizing keywords into common themes by brand. One of the brands they carry is Goodman and another is Aprilaire.

Proper Function

=Proper(text)
Back to Table of Contents

Have a large list of keywords that you want to use but you need to efficiently change the first letter of each work to capital? With the “=Proper” function, you can effortlessly make every first letter of each word in a call capitalized in an entire column. Snap shot below:

Using_the__=Proper__function_for_SEO

If you have a spreadsheet like the one above, you can simply place the proper formula in B2 and then drag the formula down to apply the same formula to each corresponding row.

LEN & Word Count Formulas

Back to Table of Contents
LEN is a great tool use for SEO’s when it comes to counting characters for Adwords character policies or even to plan out your Tweets! If you are working on an excel table, you can simply add the LEN formula to a column next to the column which is going to contain your characters. So if you are going to write ad copy titles (which restrict you to only using 25 characters for Adwords) you can place =LEN(A2) in B2 (assuming you use row headings) to see which of your cells contain more than 25 characters. See below as an example.

Character_counting_with_Len_for_SEO

The same formula can be used with other logical statements to get more out of your excel workbook.

Tables

Back to Table of Contents
Tables are always great to use because you have a lot of flexibility with filters and the ability to manage large data segments too. One neat thing that you can do with standard tables is sort your data in an interactive way like including text filters or number filters. This can be helpful if you want to organize a large number of URL’s according to certain URL parameters for example segmenting URLS that contain a certain Brand in the URL for your eCommerce landing page reports. With a table, you can apply filters to multiple columns at a time and get some really segmented data.

I like to apply a visit threshold on my column containing visits of greater than “50” so I have a large enough data sample and then I apply threshold filters to my time on site and bounce rate columns so that I have only high quality traffic data as shown in the video below. You can also sort this data with filters on your column that contains eCommerce revenue data which is a great way to find out which pages or keywords are performing better from an eCommerce conversion rate perspective. In the quick video demonstration below, I show some of the filter options that you can use to filter our your data for an eCommerce landing page report that includes conversion rate data.

Pivot Tables

Back to Table of Contents
Pivot tables are excellent to use when dealing with large amounts of keyword data. When using a pivot table for something like analyzing Adwords keyword data, you can take large data samples and break it down into smaller data segments that are easier to deal with. You can easily manipulate the data to include the metrics which matter to you most or to include all metrics from the data you are working with. For example, lets say you exported your campaign data from Google Analytics showing paid keyword data and landing page URL as a secondary dimension. I find this handy when dealing with eCommerce websites because you can take your ecommerce data and analyze each URL to see how the keywords from that landing page or ad group are performing from a revenue perspective.

The video below shows how I like to use pivot tables to organize Google Analytics data. They can be used in many different ways but the video shows the basics of how to use a pivot table so that you can use it in other ways too.

MID

Back to Table of Contents
The MID formula is primarily used to extract data from a string of characters which can be useful for SEO’s that are trying to extract transaction values from confirmation URL’s. In some cases, eCommerce stores provide transaction data in the thank you page URL’s such as sub total and product ID’s. The default formula looks like this:

= MID ( Text , Start_num , Num_chars )

So if you have a URL string that contains any data you are looking to extract, the MID formula can be very helpful. Simply select the cell to analyze for the first step (TEXT), the point at which the formula should begin (Start_num) and last but not least the number of characters preceding the starting point that you want to obtain. If you have in A2 a URL string that looks like this:

/thanks/confirmation/subtotal=101.99

You would use a Mid function in B2 that looks like this to pull the sub total value:

=MID(A2,31,6)

The above code is going to analyze A2, and pull 6 characters after the 30th character. The 30th character is the “=” sign in the URL. So the preceding 6 characters would equal “101.99” which is the sub total.

If you have a really long set of URL’s and you have multiple data points that you want to extract you may want to consider using a FIND statement nested in the MID function which we will cover next.

Nested MID

Back to Table of Contents
Pulling Ecommerce Transaction Data From Dynamically Generated Transaction URL’s is one way I use MID with a nested FIND function. As an SEO that handles many eCommerce SEO and PPC campaigns I often run into shopping sites that are using some sort of archaic CMS that makes it nearly impossible to work with (I know I am not alone here). This is where the MID function with a nested FIND function comes into play.

By using a FIND call in the “find_text” we are telling the formula to locate something first before moving forward, in this case “sub_total=” in cell A2. Once the formula finds “sub_total” then the magic happens.

Using_MID_with_Nested_Find_Fuction

The above screen shot shows the example scenario where the sub total is included in the confirmation URL and we used the MID with a nested FIND function to pull the 5 characters after the preceding 10th character Note that we had to add a “+” sign to tell the formula once you find “sub_total” pull the 6 characters after the “=” sign (which is the 10th character after the “s” in sub_total… Confused yet? 🙂 Hopefully not.

Data Validation Techniques

Back to Table of Contents
One really great way to use this is if you are building a massive amount of Ad Copies and you want to do it efficiently. You can create your Ad copies using the tricks your’e learning how to use then create Ad Copy ideas and validate the out put column to only allow a specified amount of charaters. For example, if you are creating ad titles, you can validate your cell to only allow up to 25 characters so that you don’t exceed the number of characters you are able to use with Adwords before making your bulk upload :). A video demonstration of how we use Data Validation for bulk ad copy creation is shown below.

All in all, the list is pretty short right now but we are still working on adding many more functions and tricks that can be used in excel to help all the SEO’s out there. If you have any questions and want to contact us be sure to give us a call or contact us via our online forms. Please don’t forget to visit our SEO Fort Lauderdale page if you are local and interested in stopping by our office!



About The Author

Jason Hawkins
Jason Hawkins / http://www.themiamiseocompany.com

Jason Hawkins is the CEO & Co-Founder of The Miami SEO Company. He has over ten years of experience in search engine optimization, conversion rate optimization and lead generation. His core responsibilities include identifying ways to increase value of services rendered, training staff on advanced SEO topics, and A/B testing internal processes to consistently improve client return on investment.