SEO Expert lahore -Consultants|ARIF ANJUM

Microsoft Excel for SEOs

Table of Contents

Excel for SEO is a guide we wrote up to help SEO professionals get meaningful information from mountains of data.

This guide was originally written to be read from start to finish, as some examples are worked on through different lessons. That said, you should be able to jump ahead if you feel like you’ve got the basics down.

Please report any errors or confusing stuff to one of the Twitter accounts on the right and we’ll be sure to fix it right up.

Feel free to download the XLS with the data from the examples.

• Introduction
• Lesson 1 – Basic Tasks
  • CONCATENATE
  • Text To Columns
  • COUNTIF
  • IFERROR
• Tip: Using Tables
• Lesson 2 – More Functions
  • LEN
  • SEARCH/FIND
  • Tip: Nested Formulas
  • LEFT, RIGHT, & MID

• Tip: Time Saving Paste Special
• Lesson 3 – IF, AND, & OR
  • IF
  • OR/AND
• Tip: Killer Keyboard Shortcuts
• Lesson 4 – VLOOKUP, OFFSET, & INDEX/MATCH
  • VLOOKUP
  • OFFSET
  • INDEX/MATCH
• Tip: Excellent Analytics
• Lesson 5 – Pivot Tables

Introduction

SEOs have been getting into our industry from all sorts of past careers — web designers, developers, marketers, business people and those that “just fell into SEO”. Some of these past positions may have required data analysis with Microsoft Excel, but a good many of them did not. Excel was not a big part of my past jobs, and I would guess that many SEOs’ past careers did not require anything more than adequacy in the program.

Over the last few years our field has become even more data-driven than in the past thanks to tools like Open Site Explorer, improvedGoogle Webmaster Tools and Analytics, Majestic SEO, Raven, and many others. Additionally, our clients have become wiser and more SEO capable, having been burned in the past by snake oil SEOs. They want reliable SEO advice with a strong verifiable foundation, and who could blame them?

Many SEOs are now finding themselves faced with the task of doing fairly complex data analysis to improve their search strategies, and Excel adequacy is not quite enough. This was the position I found myself in not too long ago, and while I’m far from the deadliest ninja in the dojo today, I’ve picked up a thing or two from the brilliant minds around me.

With this document I intend to share some of the most valuable aspects of Microsoft Excel for the SEO. It is far from an exhaustive look at everything that can be done with Excel, but hopefully a strong foundation for the SEO’s toolkit. I’ll be including real world SEO tasks, ranging from the relatively simple to rather complex, so I hope there’s something for everyone.

So if pivot tables, IF statements, absolute references and nested functions make you scratch your head, read on, Aspiring Ninja!

Oh! Before we begin, we must give credit where credit is due. This guide could not have been prepared without the help of some ofRichard Baxter‘s awesome blog posts and the official Microsoft Excel Help site.

Lesson 1: Basic Tasks

In this lesson we’ll cover some of the simpler functions available in Excel, and how they’re used in the SEO’s day-to-day tasks. The functions we’ll cover:

• CONCATENATE
• Text to Columns
• COUNTIF
• IFERROR

Concatenate

Microsoft Excel definition: Joins several text strings into one text string.

Syntax: CONCATENATE(text1,text2,…)

Concatenate is a pretty self-explanatory function, but that doesn’t make it any less useful. It is most often used to combine two cells into one, as in:

You may also use the formula to insert text strings before, after, or between other cells. Insert a text string by putting it within quotations, as seen here:

Text to Columns

Microsoft Excel definition: Distribute the contents of one cell across separate columns

Text to Column’s functionality is a bit limited, but I find myself using it almost every time I open Excel. As an SEO, this is the go-to function to separate subfolders or divide up subdomains, root domains, and/or TLD. Unlike CONCATENATE, we won’t need a formula to carry out this function.

Seen here in Windows Excel 2007

Seen here in Mac Excel 2011

For our real-world SEO example, let’s take an OpenSiteExplorer.org Top Pages report and suppose that we want to find which subfolders receive the most links.

The “http://” is not necessary. This can be removed with a find and replace now, or dealt with afterward.

Select “Delimited”

Choose “Other” and type the “/” key

Format cells (not necessary for this example) and select a destination (default destination is usually fine)

Voila!

We can now manipulate this data however we see fit. This is great for eCommerce sites with a nice URL structure of/category/subcategory/product/.

COUNTIF

Microsoft Excel Definition: Counts the number of cells within a range that meet the given criteria.

Syntax: COUNTIF(range,criteria)

COUNTIF is your go-to function for getting a count of the number of instances of a particular string. For instance, anchor text:

Looking to get the count of empty anchor text instances? COUNTIF does the trick.

IFERROR

Microsoft Excel Definition: Returns a value that you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. Use IFERROR to trap and handle errors in a formula.

Syntax: IFERROR(value,value_if_error)

IFERROR is really simple and will become an important piece of most of our formulas as things get more complex. IFERROR is your method to turn those pesky #N/A, #VALUE or #DIV/0 messages into something a bit more presentable.

Divide by Zero? No Problem!

Thus concludes Lesson 1 of your Excel for SEO training. Congratulations, this makes you an orange belt. Here’s an orange belt that you can print, cut out, and wear around town.

Keep Your Data Organized With Tables!

Turning your range into a table will give you banded rows for easier readability:

Your data. Table-ified.

Automatically calculated new columns AND structured/named references:

Ooooooh. How intuitive! Now we just hit enter and our column is created and calculated.

Table headers that remain atop data when scrolling:

Reference tables by name in other formulas:

To convert your range to a table use CTRL+T on Mac OS X or CTRL+L on a PC.

Lesson 2: More Functions — Text Manipulation

The functions on which we’ll be focusing in this lesson are useful for dealing with text manipulation. As we’ll see from the examples, there are quite a few scenarios wherein the SEO has to manipulate a text string. Some of the formulas we’ll talk about are pretty simple to grasp individually, but can get a bit confusing when used together. We’ll touch on:

• LEN
• SEARCH/FIND
• LEFT, RIGHT, MID

LEN

Microsoft Excel Definition: Returns the number of characters in a text string.

Syntax: LEN(text)

I doubt this requires much explanation. LEN alone is fairly useless. Sorry LEN.

SEARCH/FIND

Microsoft Excel Definition:
SEARCH — Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).

FIND — Returns the starting position of one text string within another text string. FIND is case-sensitive.

Syntax: SEARCH(find_text,within_text,start_num) and FIND(find_text,within_text,start_num)

There are two differences between SEARCH and FIND. SEARCH is not case-sensitive, FIND is. SEARCH allows the use of wildcards, FIND does not. Under most circumstances, SEARCH is all you need, but it helps to know that FIND is always there if you’ve got to deal with pesky capital letters in URLs or something similar. Another reason to choose FIND is if you’re dealing with URLs that contain parameters. Without properly escaping question marks, they will act as wild cards, which may cause some frustration.

In our example below, we’ve pulled out the character number at which the “/blog/” string begins. Much like LEN, this function is a bit silly on its own, but can be combined with some of our other functions to do some cool things.

Now Class, we remember what we do with those #VALUE!s, don’t we?

That’s right! Wrap an IFERROR around the formula!

Nested Formulas — Don’t Be Scared!

Also of note in the 2nd example above: This is the first time we’ve used what’s called a nested formula. We have these when a function is placed within another function, which can be placed in another function, and another, and so on. The more complex the nested formula becomes, the easier it becomes to break down.

Whether you’re reviewing your own formulas for errors, or looking at someone else’s work, you should start with the middle of a nested formula and work your way out. Additionally, the F9 key is your friend!

Trying to debug a formula that keeps breaking?

To see the nested interior formula’s results, highlight and hit F9

Once you’re satisfied, hit ESC, otherwise the calculated result will remain.

LEFT, RIGHT, MID

Microsoft Excel Definition:
LEFT — Returns the specific number of characters from the start of a text string.

RIGHT — Returns the specific number of characters from the end of a text string.

MID — Returns the characters from the middle of a text string, given a starting position and length.

Syntax:

LEFT(text,num_chars)

RIGHT(text,num_chars)

MID(text,start_num,num_chars)

Both LEFT and RIGHT return the characters from a given position in a text string starting from either side of a string. MID is great for extracting a portion of a text string. I’ve lumped the three together because they are often used in conjunction with each other (along with a few of the earlier functions). Let’s dive into an example:

Bringing It All Together – Example 1

Let’s say we’ve been given a list of URLs, and we want to extract just the domain.

This formula will do the job. Let’s break down this nested formula, and see how it pulls just the domain out of our URL. Starting from the middle we see SEARCH, which uses the syntax:

SEARCH(find_text,within_text,start_num)

In plain terms, this formula finds the first instance of “/” in the cell to the left, starting at the 8th character from the beginning, which is done to start past the double slash in http://. As we see below, the result for the first row of data is 22.

The same formula with the inner function calculated

Now we are left with a simple LEFT formula. The syntax for LEFT is LEFT(text,num_chars).

In plain terms: Give us the first 22 characters starting from the beginning. We now have a nice listing of just root domains.

Our list of root domains. The formula reflects the change to a table format from the simple range used previously.

Example 2

Let’s use SEARCH (with wildcards) and MID together to extract a portion of a URL:

Let’s assume we want to pull the descriptive piece out of each of these URLs for reporting purposes

We’ll definitely be making use of MID, as the text we want is in the MIDdle of our string. We’ll need to determine how many characters make up the “-tXXX.html” bit at the end of each URL. Since the length of this portion of the URL varies, but the format doesn’t (that is, “-t” + “numbers” + “.html”), we can use wildcards to find this character count.

Again, the syntaxes for these 2 functions:

MID(text,start_num,num_chars)

SEARCH(find_text,within_text,start_num)

Let’s break down the formula for the first URL in our list.

Cell A2: http://www.example.com/lamp-maintenance-t83.html

=MID(A2,SEARCH(“/”,A2,8),SEARCH(“-t*.html”,A2)-SEARCH(“/”,A2,8))

=MID(A2,23, SEARCH(“-t*.html”,A2)-23)

We’ve calculated the first instance of a “/” after the 8th character. This gives us our start_num values. We’re also using the * wildcard to help us get the character count of the right-most chunk of text.

=MID(A2,23,SEARCH(“-t*.html”,A2)-23)

=MID(A2,23,40-23)

We can easily calculate the number of characters for our MID once we know where our non-descriptive characters begin.

=MID(A2,23,17)

/lamp-maintenance

Hooray!

Example 2.5

Let’s make a small adjustment to our original URL to demonstrate how we can use LEN in this formula.

Cell A2: http://www.example.com/t1521-lamp-maintenance.html

=MID(A2,SEARCH(“-”,A2)+1,LEN(A2)-SEARCH(“-”,A2)-5)

=MID(A2,29+1,50-29-5)

/lamp-maintenance

The additional +1 and -5 are necessary to make minor adjustments to the final outcome. Without them, our final result would have been “-lamp-maintenance.html”.

This completes lesson 2. If you’ve made it this far, you’re fit to carry these around. Be careful though, you’ll take someone’s eye out!

Save Some Time With This Pro Tip!

Sometimes you want to grab a range of calculated data and place it elsewhere. However, if that data relies on a formula in place it will fall apart once pasted. In this case, you’d like to copy just the values created by these formulae. The slow way is to copy your data, and paste as values using Excel’s ‘Paste Special’.

Here’s an even quicker way:

Highlight your range of data and hover over the black line until you see the above cursor.

Now, right click and drag to your desired destination. You can actually hover away from the source, then back over it to replace your formulas, or place it elsewhere.

Release your right click, select ‘Copy Here as Values Only’ and you’re done!

Lesson 3: IF, OR/AND

Let’s slow things down a bit and learn some new helper functions. These guys aren’t going to uncover any gold data nuggets on their own, but they can help to organize and present data in your tables, pivot tables and charts.

Let’s start with a list of internal URLs, perhaps from the top content report in Google Analytics. We’d like to find out which type of page is seeing the most traffic: the homepage (/), the blog home (/blog), blog posts (/blog/{post}), category page (/blog/{category}/), a services page (there are 4), or other.

Our raw data

Ultimately, there are probably a ton of different ways to go about getting this information, but almost every method will involve IF.

IF

Microsoft Excel Definition: Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

Syntax: IF(logical_test,value_if_true,value_if_false)

IF by itself is quite simple.

It can get a bit confusing when nested (see below) and when combined with other functions (later).

The value_if_false in each of these IF statements has been replaced with another IF statement.

Back to our Example

Using a nested IF, we should be able to create a “Page Type” column in our spreadsheet and apply it to our URLs. We’ll take it one step at a time.

Let’s determine if the URL is for the blog or the home page, since those two URLs are quite simple to identify and only occur once. Since the URL for the homepage is “/” and the blog home is “/blog/”, this formula will work:

Note:The table in the following formulas has been named TC, which is reflected in the prefix before the cell references

=IF(TC[[#This Row],[Page]]=”/”,”Home”,IF(TC[[#This Row],[Page]]=”/blog/”,”Blog Home”,”Other”))

Now let’s find some way to classify a URL as a blog post or a blog category page. All of the blog post URLs on the Distilled domain follow this format:

/blog/{category-name}/{optional-sub-category}/{post-name}/

And all of the blog categories follow this format:

/blog/category/{category-name}/{optional-sub-category}/

So we’ll need to add to our growing IF formula. In plain English, our formula must check to see if the URL starts with /blog/category/, in which case it is a blog category page. If not, we’ll get less specific and check to see if it starts with /blog/, in which case it is a blog post. Our forumula now looks like this:

=IF(TC[[#This Row],[Page]]="/","Home",IF(TC[[#This Row],[Page]]="/blog/","Blog Home",IF(ISNUMBER(SEARCH("/blog/category/",TC[[#This Row],[Page]])),"Blog Category",IF(ISNUMBER(SEARCH("/blog/",TC[[#This Row],[Page]])),"Blog Post","Other"))))

In the above, after Excel has checked that the given URL isn’t the home page or blog home, it does a SEARCH for “/blog/category/”. We then make use of the ISNUMBER function to see if that inner SEARCH function is returning a digit or a #VALUE (#VALUE being the result if the string is not found). ISNUMBER simply checks to see if the given value is a digit, and returns TRUE or FALSE.

Almost there!

For our final piece of the equation we want to classify 4 of our URLs as “Services”. Those 4 URLs are /pay-per-click.html, /online-reputation.html, /search-engine-optimisation.html, and /web-design.html. If we can do this properly, every other URL that hasn’t been classified will become “Other” and our work will be done!

We’ll be adding the OR function to our formula now, so a brief intro, including AND: