How to be an Excel Ninja (and how it helps your SEO)

First an admission. I used to be an Excel-monkey for a living. They called it ‘strategy consulting’ but really (at least in the early days) I was driving Excel for hours every day. It was more fun than it sounds - possibly because I enjoyed the bits of actual consulting that went on around it. A bit like a mis-spent youth, this kind of thing gives you hidden skills. Unfortunately they’re not as much use at parties as being able to play pool one-handed behind your back. Fortunately they are more useful for SEO.

Read on for:

  • A power-user Excel tip
  • Some ideas on ways to use Excel to improve your search marketing
  • Sign-up details for a free call to help you perfect your Excel ninja skills

During the last conference call we ran, I used Pivot Tables to demonstrate how to slice and dice data in a certain way. A number of people contacted me to ask for more details on how I did that and asking for another call to show some advanced Excel tricks and how you can use them to make yourself a better SEO.

As a result, I have scheduled our next free conference call for 4.30pm UK time on Wednesday 30th September (that’s 8.30am PST / 11.30am EST - sorry West Coasters - grab a coffee and come join me early!). This one is entitled How to be an Excel Ninja. I’m planning for it to be a little shorter than the last one - probably about 30 minutes followed by questions.

If you’d like to hear when we run events and calls, you can sign up here.

The questions are a big part of the reason for doing this live rather than pre-recorded. As the call progresses, if there is anything unclear or where you would like further information, you can ask a question via the conference call software or twitter @willcritchlow with this call’s hashtag: #excelninja. Also if you have questions in advance or anything you’d particularly like me to cover you can leave a comment below.

How Excel can help your search marketing

You know me and Distilled through internet marketing. I want to make sure that these calls stay on topic so the plan is to use search marketing examples to demonstrate the various Excel Ninja tricks. I plan to use some examples from SEOmoz tools - particularly Linkscape.

I wanted to make sure that this write-up is useful even in advance of the call. So I’m including two things - the first is a list of ways you can use Excel (assuming you already know some of the tricks) in SEO - hopefully enough to stimulate more ideas and thoughts. The second is a preview of the kind of Excel power user tip I’m going to be sharing on the call. First the uses of Excel:

  • Cross-reference analytics and inbound link data
  • Find hidden opportunities from comparison of PPC and organic traffic
  • Deep-dive analysis of inbound links - discover patterns in the use of image links / poor anchor text
  • Comparison of competitor link profiles - advanced versions of SEOmoz’s link intersect tool
  • Keyword analysis (phrase length, pattern matching etc.)
  • Quick and dirty logfile analysis

A power-user tip

As your Excel formulae get more complicated, you will probably find that from time to time you create a formula that doesn’t work. Either it gives you the dreaded #NA or (sometimes worse) it just gives you the wrong answer. When this happens you’ll want to debug it. Debugging (like change control) is not something that Excel excels at, but there are some tricks you can use to make your life easier.

If you have a formula like this that isn’t returning the result you want (I have kept the formula simple to just demonstrate my point):

Excel functions

Then you can highlight a sub-formula (i.e. any part of this formula that could stand alone as its own formula in a separate cell):

Excel highlighted formular

and press F9 to replace the formula with whatever it evaluates to:

Excel F9 highlighting

This is way quicker than copying and pasting formulae about the place just to debug them. The only thing you need to be careful of is that after doing it, you press escape rather than enter so that you don’t permanently replace the formula.

Hopefully at least some of you (a) didn’t know you could do that and (b) can see how it would be useful. If that’s you, sign up to get more of the same on the call.

I’m going to cover how to do this and more including:

  • The magic of pivot tables
  • How to debug and maintain complicated Excel
  • Index, match, cell - advanced functions and how they help you
  • How to keep really big Excel models manageable
  • Useful SEO tips and tricks Excel makes easy

If you’d like to be on the call or get the recording afterwards, just sign up above.

What is Excel bad at?

The major let downs of Excel in my opinion are the lack of version control (which makes it very easy to break your own models and those of other people) and the relatively poor maintenance tools. Although I will cover some debugging and diagnosis tricks on the call, in my opinion as soon as you have multiple people using a model or you are relying on it regularly, there will be better ways of building systems to support your processes!

I read a theory somewhere that most people have an application in which they’re most comfortable - and that they end up seeing as the hammer to every task’s nail. This apparently applies not only to the emacs / vi fans whose applications really can do anything but is also the reason you see people composing documents in Powerpoint, writing presentations in Word, maintaining todo lists in Excel etc. For me, I think that’s probably Excel. I try, however, to use it only for rapid prototyping rather than relying on it in in major processes. The rapid prototyping and discovery can be incredibly rapid, however, so I hope you’ll join me on the call to see how becoming an Excel Ninja will make you a better search marketer.

Looking forward to speaking to you!

Will Critchlow

Will Critchlow

Will founded Distilled with Duncan in 2005. Since then, he has consulted with some of the world’s largest organisations and most famous websites, spoken at most major industry events and regularly appeared in local and national press. Will is part...   read more

Get blog posts via email

6 Comments

  1. Excellent topic Will,

    I'm quite an excel power user myself (although I didnt know about the F9 trick and always used the function button to debug).

    Perhaps we'll get people to such a level whereby they are actually interested in my vba apps ;)

    Matthew

    reply >
  2. Doh - already subscribed.

    I'll be there Will, for I too love Excel more than any man should.

    reply >
  3. I am spreading the F9 tip far and wide. Awesome

    reply >
  4. I'd subscribe but the form doesnt work in chrome... so switching to firefox and subscribing :)

    reply >
  5. I love the F9 trick you showed in your post. I used it today at work and even got some points with the boss as he is a fellow excel junkie.

    reply >
  6. Nice little overview of Excel. Wish I had sat in on the call. I guess I'd better sign up!

    reply >

Leave a Reply

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

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>