Home | Search Engine Optimization | Five Simple Excel Tricks for Beginners

Five Simple Excel Tricks for Beginners

John Doherty —  April 13, 2011
  • Buffer
  • Vote on Hacker News
  • Sharebar
  • Buffer
  • Vote on Hacker News

Excel is a necessary tool for SEOs that can be leveraged in many different ways to pull out needed data for our everyday tasks. I have recently been working through the excellent SEO Excel handbook put together by Mike from Distilled, because I realized that I need to up my Excel game in order to be better at what I do.

Here are five simple Excel tricks I have discovered that help me do my job better and faster. After all, efficiency is key when doing all things related to SEO on a website, especially as a solitary in-house SEO.

Five Excel Tricks

    Reverse data

    - Have data that imported opposite of what you need? This happens to me with all Adwords keywords I export (could be a noob mistake on my part). There is a simple way to correct this. Create a row beside your data with the numbers 1-the end. assuming your data is a table (see below), you can sort this row from highest to lowest. Boom. Your data is now reversed.

    Reverse Text in Excel

    Sort Small To Large to Reverse

  • Create tables

    - One trick I recently learned is to turn your lists of data into a table as soon as you import the data. Hit CTRL+L and Enter. If you have headings on your rows, be sure to select “My table has headers” before hitting “Enter”. Your worksheet becomes a table. This is useful because when you create a function in one cell on a row, it will be applied down the whole row. This saves click-and-drag time, and is significant when dealing with thousands of rows of data (OSE exports, anyone?).

  • Build lists using Concatenate

    - If you ever create lists in order to make some ego-bait for linkbuilding, coding a list can be one of the most tedious tasks, especially when including 50+ URLs. You can use the concatente (=concatenate(cell,cell,cell)) to create these lists. See below as an example:

    concatenate for SEOs

    Concatenate is a simple, yet powerful tool

  • Find counts to visualize data

    - If you have a long list of keywords present on your page, copy the row. Paste it a few rows over (lets call it Row G) and remove duplicates from that row only. Then in Row F, do a simple (=countif((row of original list),G1). Now you have the count of each of your keywords pointing to yiur page. It will look like this:

    Count Keywords in List

    Use =countif(range,criteria) to find counts of keywords

  • Visualize keyword focus on a page

    - If you have just done the above, you can know visualize the weight of each of your keywords pointing to your page. This can help you know if your page is OVERoptimized for a certain term, in comparison with competitors.

    Highlight the row of keywords and row of the counts (if we’re using the above example, rows F and G). In Word 2007, go to Insert -> Graph. I choose a Pie chart. Assign each keyword to the legend, with an associated color. Now you should have a pie chart. This is helpful for visual people like me. This is how the window looks when setting up your chart:

    Visualize Data in Excel

    Use Insert - (graph of choice) to visualize data

    And here is the nice pretty chart I just created (dummy data, of course):

    Pretty Chart

    We get a nice, good-looking chart.

Do you have other tricks you like to use? Need any of these fleshed out more? Let me know! I’d like to hear what Excel issues other beginners are having. We can work through them together.

John Doherty

Posts

I'm the new (as of October 2013) Online Marketing Manager of Hotpads.com, soon to be based in San Francisco. Previous to Hotpads I worked at Distilled for 2 years as an online marketing consultant. In my spare time I shoot lifestyle photography, explore new and interesting food in New York, ski, rock climb, and update my Twitter and Google+ accounts.