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
– 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:
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:
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:
And here is the nice pretty chart I just created (dummy data, of course):
– 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.
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.