Beginner’s guide to SEO – Part 3: Spreadsheet step guide
This is the final part of the “Beginner’s guide to SEO” series. The series has covered what SEO is and how to use SEO tools like Semrush to obtain and download a list of relevant keywords. In this final part, I’ll show you how to cut down this list even further and start using these words in your title, page and website.
The spreadsheet program I’ll be using is Google Sheets, which is free to use if you have a Google account. If you don’t have access to Google Sheets that’s fine, you can do this process through Microsoft Excel as it provides many similar features.
1. Open up the spreadsheet – Now that we’ve downloaded our spreadsheet, it’s time to open it up and get started. When you open up the spreadsheet it should look like this. You may want to stretch out the keyword section as I have done here to make it easier to read.
2. Adding an additional list to your spreadsheet – Sometimes, the list given by your SEO tool may not carry enough relevant keywords. If that is the case with yours, you can always obtain another list by following steps 5-6 here.
Once you have your list, go back to your original list’s spreadsheet and click the “file” button at the top to get a drop down menu. On this menu click “Import”.
After doing so you should get a menu of the files in your drive. Select the CSV file you downloaded.
This menu should then appear, click the arrow next to “Create a new spreadsheet” and click “Append to current sheet”.
Click the ”Import data” button in the menu and your new list will be added to the spreadsheet.
Additionally, you might want to delete the “keyword” row provided as shown in these images
3. Removing unnecessary keywords – Before getting started, you will need to go through your list of keywords and select the phrases that are relevant to what you are writing. It’s important you do some research around the topic you are writing about so you know what words best relate to what you are writing about. All the phrases with a volume of 0 should be removed since they are rarely ever searched for.
4. Removing blank spaces and duplicate phrases – After deleting all the unnecessary keywords from your list you will have a couple of blank spaces on your spreadsheet. To get rid of these all at once I highlighted all the data first.
After highlighting all the data in the table, I then clicked the “Data” tab at the top and selected “Remove duplicates” from the drop down menu. This not only removes duplicate keywords but also removes the blank spaces in the list.
If you’ve done it correctly, you’ll be presented with a menu similar to this. Select column A only and then press the green “Remove duplicates” button.
In my case there were 12 duplicate words found.
5. Creating a filter – After removing the duplicates from my list, I moved onto creating a filter for my spreadsheet. To do this you simply have to highlight all the data as you did when moving duplicates and click the filter icon at the top. You’ll now see a filter button for each section on the spreadsheet.
6. Filtering your keywords – The next step is to order your keywords from the highest to lowest volume. To do this simply click the 3 lines next to each section and it will give you an option menu for filtering your data.
7. Picking the title – While the title isn’t the only place you can use these keywords, it’s best tp include the most important one in your title. When looking at my list, it shows that “beginners guide to SEO” is the phrase with the highest search volume. Since many people were typing in this particular phrase I decided to go with that as my title.
While in this example I have picked the one with the highest volume, it doesn’t always have to be the one at the very top of the list. You should try to go for a keyword which is relevant to what you are writing: even if that word only has a lower search volume than the top ones, you’ll see more success targeting the people who want to learn about the exact thing you’ve written about!
8. Using keywords in body copy & headers – You’ve got your refined list of keywords which are applicable to your page and website, now it’s time to optimise your site with these keywords so you can appear at the top of search results across many different search engines. Try to avoid what is known as “keyword stuffing” which is the practice of putting many keywords on a webpage to manipulate a site’s ranking in Google search results. Often, the keywords used in keyword stuffing are actually out of context and barely increase search visibility. You should instead focus on using a variety of necessary keywords throughout your main body text.
Here is a nice example of how I applied keywords to the header of this blog.
And that’s it! You’ve now optimised your page and site for visibility on many different search engines. The next stage is analysing your traffic results after a certain period of time. My next series will show you how to do just that – keep your eye on our blog page for future updates.