Ditch the hassle: Excel secrets to master keyphrase research

Thorough keyphrase research is a cornerstone of good SEO and often takes a lot of time to produce and organise. At 3WhiteHats, we use Microsoft Excel to sort and group keyphrases, aiming to make it as easy as possible for our clients to digest.

Here are a few tips to save you time, minimise mistakes and aid presentation.

Proper

The proper function capitalises the first letter of each word in a cell and helps make lists of keyphrases look more streamlined. First select the cell next to the keyphrase and type ‘=proper(‘, select the cell you want to reformat, close the bracket and press enter. Then, copy the cell and paste as values:

Top tip: Acronyms like ‘UK’ are overlooked using this method and keyphrases like ‘cheap laptops uk’ will be changed to ‘Cheap Laptops Uk’ which doesn’t look too good. If you know that several of the same acronyms feature in your keyphrase research, use the find and replace feature (see below) to save yourself some time.

Sort group (keyboard shortcuts)

The sort feature allows the user to give order to lists of text and numbers – this is perfect for ordering keyphrases by their search volume. Whilst use of this feature is likely commonplace, did you know there are keyboard shortcuts to speed this up?

To sort largest to smallest, simply select the data starting from the column you want to order by, right click on it and press the letter ‘o’ twice:

Top tip: This can also be used to sort numbers from lowest to highest – just select the data starting in the column you want to sort by, right click, press the letter ‘o’, then the letter ‘s’. You can also use this feature to sort A-Z if using text.

Advanced Find & Replace

Find and Replace allows you to locate cells that meet a certain criteria and change their contents or formatting to something else. This is super useful in keyphrase research when you want to find similar keyphrases (e.g. those that contain a certain word) so that you can group them.

For example, in this export you might want to find all keyphrases that contains the text ‘laptops’ so you can group them. Pressing ‘Ctrl + F’ brings up the find and replace option, click the Replace tab, click Options and enter the same keyphrase twice:

After that, click Format Next to replace with word, and pick a colour to fill the replaced cell. When that’s done, hit Replace All, hit Ok, then use the sort and filter tool to sort by the colour of your replaced keyphrases.

Note: This process can also be completed with the ‘Text filters’ option within the Filter menu. This gives less flexibility when copying and pasting out the keyphrases, as the cells that have been filtered will also be pasted.

Top tip: When finding and replacing smaller words like ‘UK’ be careful as these can be parts of other words (e.g. ‘Fluke’). Try to spot similarities with these words, for example, ‘UK’ always has a space before it, therefore you always want to find and replace ‘ UK’ with ‘ UK’.

Add/remove rows and columns (keyboard shortcuts)

Often, you’ll need to add some space between keyphrase groups. Similarly, you’ll spot a keyphrase that isn’t relevant to that campaign (“how did that get there?!”), and need it to be deleted. Similarly, these both take several clicks and are much quicker when using keyboard shortcuts.

Press Ctrl & ‘+’ to insert rows, if using the ‘+’ sign on your number pad. As an alternative, you can also press Ctrl & Shift & ‘+’ if pressing the key near the zero key on your main keyboard. This can be pressed as many times as required:

Removing is as simple as clicking the required row and pressing ‘Ctrl & -’ (either the key by the zero key or on the number pad) as many times as necessary:

Finding duplicates using conditional formatting

Good keyphrase research relies on using many sources and tools to examine which terms should be targeted. As a result of this, the presence of duplicates is not uncommon.

As long as the keyphrases are all in the same sheet, conditional formatting is a very useful tool for spotting and removing duplicate keyphrases easily. Simply select all of the keyphrases in the sheet, then head to Conditional Formatting > Highlight Cells Rules > Duplicate Values. Et Voilà, your duplicates will be obvious and simple to remove:

When you’ve deleted them, head to Conditional Formatting > Manage Rules, select the rule and hit Delete Rule.

Top tip: Always use this tip at the last minute before sending to the client.

Also, beware of the shortfalls of this feature; if what is in the cell is not exactly the same, it will not highlight as a duplicate. For example ‘tablet laptop’ will not be marked the same if there is a trailing space, ‘tablet laptop ’.

Hopefully there’ll be something in here to help speed up your next batch of keyphrase research. Feel free to let us know your own top Excel tips.

Author Jon Baglow

Posted October 13, 2017