SEO: Using Excel for Keyword Analysis

Excel is the SEO tool I use most frequently. Excel helps me organize my thoughts, decide which keywords or pages to focus on, illustrate key concepts, and even draft rudimentary content.

This article lists my favorite Excel formulas, menu items, and keyboard shortcuts for search engine optimization analysis. You can use these tips for far more than SEO, recognizing that some of the features are only available in Excel 2010. Please add your favorites in the comments.

Favorite Excel 2010 Formulas

VLOOKUP. A must have for matching keyword research data to analytics reports to show how many times a keyword is searched for a month in Google compared to how many visits or how much revenue that same keyword earned a site. I usually put the keyword data in one worksheet and the analytics data in another, and then use VLOOKUP to pull the matching keyword research data into my analytics report.

SUMIFS. When I’m looking for opportunities in keyword research, I may want to know the combined number of searches are done for keywords containing certain elements. For example, let’s say I want to know how many searches Google reports a month for keywords that contain both “black” and “dress.” SUMIFS can take doth of these conditions and sum up the number of searches for me. Its little brother, SUMIF, does the same thing with only one condition.

IF ISERROR. This nifty calculation takes those annoying #N/A results that are essentially error messages and turns them into zeroes, blanks or anything else you please. It can be a tricky formula to write outright, so I usually write the formula I wanted in the first place and then go back and paste the IF and ISERROR formulas around my original formula.

CONCATENATE. A brilliantly simple formula, CONCATENATE takes any strings of characters and cells you please and strings them together in a single cell. For example, if you’ve mapped keywords to URLs — here’s my article on keyword mapping — and you want to make a quick start of optimizing the title tags, it’s easy to concatenate the primary keyword, the page name and the brand together with some punctuation to form the start of a title tag. You’ll need to review them to make sure they’re optimal, but it’s a lot easier to tweak something that’s already written than to start writing it from scratch.

Favorite Excel 2010 Menu Items

I’ve moved these menu items into my Quick Access Toolbar for easy one-click access. I save the toolbar for commands that I use frequently that can’t be accessed with keyword shortcuts.

Freeze Panes. A must have for large spreadsheets, this command freezes the cells above and to the left of where your cursor is so that the column and row headings can be seen no matter how far down or to the right you scroll.

Remove Duplicates. I can’t count the amount of time this feature has saved me while researching keywords. With the click of a button, duplicate rows are deleted while the first instance is preserved. This can be a dangerous function, so you might want to play with it a bit before using it on critical data to understand how it works.

Manual Calculation. Tired of watching your document process formulas in “Not Responding” mode? Click the Manual Calculation Option found in the Formulas menu to prevent Excel from trying to recalculate every time a change is made. This one command has saved me weeks of compute time on formula-intensive spreadsheets.

Favorite Excel 2010 Keyboard Shortcuts

While awkward at first, forcing myself to learn these keyword shortcuts has saved me untold hours in Excel. Yes, all of these shortcuts are available in the menus, but it takes more time to execute multiple accurate clicks in the menu than to quickly type three or four keys to accomplish the same thing — once you get used to doing it. Over the course of a day or a week of executing these commands repeatedly to analyze data, those seconds add up to more time to focus on what’s important.

Highlight Range. Ctrl+Shift+Arrow Key. This is the fastest way to highlight all cells in a range when you don’t want to just click on the row or column header. For example, Ctrl+Shift+Down highlights every cell starting where your cursor is and ending with the last contiguous cell in the row. Ctrl+Shift+Down, Right would highlight every cell starting where your cursor is and ending with the last contiguous cell in the row, and then extend the highlighted section as far to the right as the right-most contiguous cell. This command is extremely handy for highlighting large ranges of cells instantly without waiting for the page to scroll until you get to the end of the cells.

Move to End. Ctrl+Arrow Key. This similar command can be used to move the cursor to the beginning or end of the range of cells without highlighting the cells in between. It’s handy in conjunction with the Page Up and Page Down buttons to scan large blocks of cells quickly
Filter: Ctrl+Shift+L. Enables the row highlighted to act as the header row from which to filter and sort the rows below it.

Delete. Alt+E, D. Quickly delete any highlighted cells, rows or columns. Unlike using the delete key or Ctrl+X, Alt+E, D removes the highlighted cells not just their contents.

Insert Cells. Alt+I, E. Inserts a number of blank cells, rows or columns equal to the number highlighted. For example, to quickly insert a blank row, I highlight the row below where I want the new blank row to be inserted and type Alt+I, E.

Paste Special > Values. Alt+E, S, V. I like to think of this as “the flattener.” It pastes the content copied onto your clipboard and pastes the numerical or textual contents without any formulas or formatting. So basically you can copy a range of cells filled with formulas that require lots of processing power to calculate, just copy those cells and Alt+E, S, V right over top the same cells to paste just the results of the calculations. You may want to save the formulas in one row or column in case you need to add more data. I’ve flattened too early many times and had to redo the formulas as I added more rows.

Transpose Rows and Columns. Alt+E, S, E. When you want to swap the axis of the cells you’re working with, simply copy the range and do a quick Alt+E, S, E. The contents of the cells will be pasted exactly as they were, except that the X-axis is now the Y-axis and vice versa.

Escape: Esc. Yes, seriously. When Excel is flashing all sorts of annoying warnings and help menus at you and you just want them to go away without bothering to click on the right button, the escape key usually does the job. Escape is also handy when you’ve started to work in a cell and want to erase the contents of that cell and cease to work in it quickly.

These are some of the commands, formulas and shortcuts I use day in and day out in Excel 2010. Sometimes it’s ugly. Sometimes it’s kludgy. But it always gets the job done.

What are your favorite timesaving Excel shortcuts?