Being a word nerd, you probably work with Word or Google Docs more so than MS Excel and Google Sheets.
Excel is like a foreign language to you- you know the very basics (and maybe a swear word or two) but the rest? Nope!
I’ve been working with Excel for over 20 years and it was the reason I got promoted in my very first career job, when I used Excel formulas and pivot tables to take a 6-week task and turn it into something that only took 2 hours and saved my employer thousands of dollars. Luckily I didn’t work myself out of a job!
When doing keyword research for my clients, I use Excel’s handy functions to cut down on some of the tedious tasks involved in choosing the final list of keywords.
Here are 7 time-saving Excel tips for keyword research that’ll have you back working on your writing project in no time.
Here’s a quick summary, if you already know your way around Excel but just want to see how to save time when doing keyword research:
7 time-saving Excel tips for keyword research
1. Use “remove duplicate” function to remove duplicate keywords
2. Use filter function to show keywords with a common word, to make keyword mapping faster
3. Use sort function to sort competition score, from lowest to highest, to easily see low competition keywords
4. Add a column to the left of the keyword column, for typing in the page/post name that the keyword belongs to.
5. Add another column to the left of the “page name” column. Number each of the pages/posts.
6. Use the sort function to sort the list by “page number”, which will group all the keywords that belong on the same page together.
7. Use the sort function again, to sort by keyword so that all keywords are shown for each page in alphabetical order.
Psst! Wish you could save time and stress trying to figure out where to put all your keywords without having to Google it every time? Get my free On-page SEO Checklist!
1. Use “remove duplicate” function to remove duplicate keywords
When doing keyword research, I never worry about whether I’ve already saved a keyword to my list or not because I know that after I download the list into Excel, I can use the handy “Remove Duplicates” function which will get rid of any duplicate keywords in my list.
To find this function, go to “Data”, then on the right hand side you’ll see a section called “Data Tools”. Select “remove duplicates” and then untick all of the boxes except for “keywords”. This means the tool will go through the column named “keywords” and delete the entire row for any keywords that are duplicates.
2. Use filter function to show keywords with a common word, to make keyword mapping faster
Certain keywords can be grouped together in Excel to make it easier for you to map them to the right page or post. I use the filter function and choose the option to only show keywords that feature a certain word.
For example, when I’m doing keyword research for a copywriter and one of their services is email copywriting, I’ll filter the keywords to only show those that contain the word “email”. I can then map these to the email copywriting page. Done! Easier than scrolling through the entire spreadsheet looking for the words.
The filter function can be found in the “Data” tab, in a section called “sort and filter” (look for funnel icon!). Then click the drop down arrow in the keyword column and scroll down to “text filters” and “contains”, and then add the word you want to filter to show. You can also do this for “does not contain” if you want to see keywords that don’t contain a certain word.
3. Use sort function to sort competition score, from lowest to highest, to easily see low competition keywords
When I’m working on a huge keyword research project with many keywords, sometimes I like to colour code them according to their competition score to make it easier for clients to see which keywords are easier to rank on page 1 in Google for.
To do this easily, I sort the column with the competition score from lowest score to highest.
Go to the “Data” tab, then “Sort & Filter” and select “Sort” (it’s next to the filter function).
Then under “column”, sort by “score” or whatever the column name is where you have your competition score.
4. Add a column to the left of the keyword column, for typing in the page/post name that the keyword belongs to.
A simple tip but so effective when keyword mapping. I create a column next to the keyword which is where I type the page/post that the keyword belongs to.
Click anywhere in the “keyword” column, then right-click your mouse and select “insert”, then “entire column”.
Done! Now you can start mapping your keywords.
5. Add another column to the left of the “page name” column. Number each of the pages/posts.
Using the “insert column” function I described above, insert another column where you will number each of the pages/posts you’ve mapped your keywords to. Why do I do this? Read the next point.
6. Use the sort function to sort the list by “page number”, which will group all the keywords that belong on the same page together.
After I’ve numbered all the keywords according to which page/post they’re mapped to, I then use the sort function to group all the keywords together by page/post number, so that the keywords show in the spreadsheet in order of the sitemap planned for the website. For example, I always show the home page keywords as page 1.
This is so much easier than cutting and pasting rows!
Go to the “Data” tab, then click on “Sort” in the Sort & Filter area. For “Column”, choose to sort by the name of the column where the page number is.
7. Use the sort function again, to sort by keyword so that all keywords are shown for each page in alphabetical order.
Point 6 and 7 can actually be done together, if you want to sort by both page number and keyword, so that the keywords are shown in order of pages as they will appear on the website AND the keywords are shown in alphabetical order to help you easily find keywords.
In the sort function, you can choose two levels of sorting. Firstly, for column, select “page number” as described in point 6. Then at the top of the command box there’s an option to “add level”, then for column choose “keyword”. This tells Excel to sort by both page number and keyword.
One final word
Learning how to use Excel’s features will save a lot of time and stress trying to figure out the quickest way to sort and map all your keywords, so you can get back to writing copy faster.
If you’re ready to save even more time, why not outsource keyword research to me? Get back to writing copy and I’ll send you a completed spreadsheet in your inbox, ready for you to weave the keywords into your client’s copy. Find out more about my keyword research process here or download my rate card.
Latest posts by Nat Alleblas (see all)
- 42 Helpful Content Ideas For Service Businesses - August 19, 2022
- 7 time-saving Excel tips for keyword research - August 18, 2022
- Should you outsource SEO or do it yourself? - August 7, 2022
