June 21st, 2011
Tips, Tools and Tutorials
So many times, I’ve found myself trying to do something with Excel that I knew had to have an easier or quicker way to do it. As a mentor once told me about managing time and workflow, “it’s not about working harder, its about working smarter”. If there’s one thing that I love about Excel it’s that there are countless ways to save time by letting Excel do all the thinking and calculation. Here are five of my favorite SEO Excel tricks that have come in handy for me many a time.
Conditional Formatting + Custom Sort
Scenario: You’ve got tons of data from your Analytics report and you want to view the traffic going to your blog posts at the top. The problem is, your traffic is sorted by highest pageview and you have some pages that come before /blog/ alphabetically (rendering an alphabetical sort useless).
Let’s say this is my list of URLs (see below). I want to prioritize my attention on the blog posts by sorting them so they’ll be on top of the list, but since I’ve got other pages that come before my blog’s subdirectory, I can’t just list the pages alphabetically by URL.
Step 1: Add conditional formatting to cells containing “/blog” by going to Conditional Formatting (on the Home tab) > Highlight Cells Rules > Text that Contains…
Step 2: In the Text that Contains window, specify /blog/ and select the default formatting of Light Red Fill with Dark Red Text
Step 3: Now that you have your blog posts highlighted, select the whole range (A2:C16) and go to Sort & Filter > Custom Sort.
Step 4: In the Sort by drop down, select the column containing the conditional formatting (column A). However, in the Sort On drop down menu, change Values to Font Color. When you do that, another drop down will appear to the right. Click on the Order drop down and select the Dark Red color. Make sure that the order is set to On Top and hit OK.
Now all of your blog posts are at the top.
Step 5: But you can, of course, take that a step further by adding another level in your custom sort to sort by the Largest Value of conversions by clicking the Add Level button at the top left of the Sort window and specifying the Sort By drop down for the conversions column (column C), keep the Sort On at Values, and select Largest to Smallest order.
Voila! Now you’ve got your highest converting blog posts sorted in descending order. With this data, I can find out what my most successful blog posts are (in terms of leading to conversions – whatever they may be), and adjust my SEO content writing topics accordingly.
Fill Series + Custom Sort (Largest to Smallest)
Scenario: You’ve got a list of URLs you need to swap the order of (the top URL moved to the bottom, etc.) and you have too many rows to cut and paste by hand.
It would be a complete waste of time trying to cut and paste rows in order to swap the order of your data, but there are times where you might need to. In this case, you would just create a “dummy column” and create a series of numbers in ascending order.
Step 1: Start by typing the number 1 into the first cell and using the fill handle (that little black box at the bottom of the cell) and drag it down to the bottom of your list.
Step 2: Select Fill Series to create your ascending list of numbers.
Step 3: Now that you’ve got a list of numbers, you can then Custom Sort your data by the values in the dummy column. If you want to reverse the order, select the whole range, go to Sort & Filter > Custom Sort
Step 4: In the Sort by column, specify the dummy column, and select Largest to Smallest in the order drop down menu. Ta da! Now you’ve got your rows swapped.
Array Function (unique values/URLs)
Scenario: You’ve got a list of keywords that are driving conversions to your site and their corresponding URLs but you want to find out how many unique URLs there are. You can’t use a regular COUNT function because there might be duplicate URLs since more than one keyword target can be driving conversions to the same page.
I’ve got a list of URLs and the keywords that have brought in traffic but the problem is that I want to know how many unique URLs were actually driving traffic to my site. You can highlight the duplicates using Conditional Formatting (like in the example below), but that takes time.
However, you can use an array formula to calculate the unique values in a range of data to count for me.
I use this formula, but don’t forget to hit CTRL+SHIFT+ENTER after you’ve entered the formula. (The data is located within A2:A14)
You’ll see that because it is an array formula, there are now curvy brackets at the end of the formula (if you hit CTRL+SHIFT+ENTER).
Text To Columns
Scenario: You were in a rush and copied the meta keywords from your homepage into your Excel workbook but you want each keyword to be separated into multiple columns.
I just got done copying over the meta keywords that I placed on my pages, but I want to double check their search volumes, but I’m too lazy to copy and paste every keyword individually. The good news is that I can use Excel to do this for me.
Step 1: Select the range or column of data that you want to split up, go to the Text to Columns button on the Data tab.
Step 2: Check the Delimited radio button and hit Next.
Step 3: Make sure that the check box for Comma is selected (and no other symbol). Hit Next again.
Step 4: Though you’ll probably want to skip to Finish, it is important to note that you will be specifying the destination of the parsed data, so make sure you’ve got the right location listed. Now that you’ve separated the keywords, go ahead and copy the formatting over to each column.
Scenario: While trying to manage a PPC campaign, you’ve listed several keywords that you think would be good keyword targets using Google’s AdWords Keyword tool, but you find that you’ve managed to listed duplicate keywords and you don’t want to take the time to highlight the duplicates with Conditional Formatting and delete from there.
I just ran a few queries on the Google AdWords keyword tool and since it came up with some great suggested keywords related to my query, I think I want to look at that data. The problem is that I’ve got a few duplicates. I’ve highlighted the duplicates to show them, but I really don’t want to have to go through and delete each row by hand.
Step 1: Select the entire range of data. Go to the Remove Duplicates button the Data tab.
Step 2: Check the box for Keyword (and uncheck any other column) and hit OK.
Now I’ve eliminated those extra keywords without having to touch the Delete key.
Scenario: You’ve got lots of keywords that have been driving traffic to your site over the course of several months but you want to see how much fluctuation has occurred. You have been collecting the data for months but you don’t have the time or space on your spreadsheet to create a chart for each keyword, and the idea of creating pivot charts is a bit daunting – not to mention time consuming. All you want is a quick and dirty look at the traffic of each keyword without taking up much space.
I know that I’ve had some fluctuation of visitors on a monthly basis for each keyword but I really don’t want to make a chart for each keyword and its plural variation. Thanks to Office 2010, the Sparklines feature allows me to make mini-charts within an individual cell. This will save time and space on my spreadsheet while also providing a visual representation of the traffic changes.
Step 1: In the cell next to the first keyword’s data, go to the Insert tab and select Line in the Sparklines section.
Step 2: Specify the range of data (in this case, its B2:E2) and keep it relative (no absolute references – so don’t list $B$2:$E$2 if you want to copy this down the column)
Step 3: Now that I’ve got my Sparkline, I can make changes to the format. I can swap it out for a column chart instead, or change the style. If I need to edit the data to add another month’s data, I can do that with the Edit Data button on the far left.
Step 4: Now that I’ve made all of my formatting adjustments, I can just copy and paste the sparkline down the column for the other keywords.
There are no comments yet.