Improve your Excel Skills to Excel in SEO

Having Knowledge and its Application in real life scenario, are two completely distinctive aspects of Professionalism. A skilled execution ensures better manifestation of knowledge. Similar theory applies to Online Marketing aka Search Engine Optimisation. Acquiring knowledge is as easy as it is difficult to implement without proper skill and expertise, which can be acquired by improving soft skills. Now you must be wondering, what kind of soft skills are required for SEO! 

SEO is not all about submitting your website details to various web directories, writing creative articles/blogs and sharing on content portals, exchanging links in various possible ways. SEO in its true meaning is about developing a complete marketing strategy tailor-made for online users. Online users are entitled to their unique kind of behaviour. Proper assessment and understanding of this unique behaviour of online users is required to make online marketing strategies. So now you must be wondering how online marketing is more of a challenging task than simply doing some data entry type stuffs! Online marketing without proper understanding of customers’ behaviour online is pretty similar to those data entry type SEO stuffs (mentioned above). You can simply turn your regular data entry SEO type into more result-oriented marketing strategy by acquiring and mastering some soft skills. Soft skills required for SEO is nothing but learning the best reporting tool in the world: Microsoft Excel/Open Office Calc. Both Microsoft Excel and Open Office Calc are similar in look and function except certain typographical alternative that we may be consider later on this article to discuss.

Why Microsoft Excel/Open Office Calc?

Excel/Calc as a reporting tool is quite popular and no business report is complete without an Excel/Calc presentation. Its immense popularity as a reporting tool is because of its flexibility in analysing, processing and presenting data. Online marketing requires a lot of data to be analysed, processed and presented that a better marketing vision can be set in order to realise the optimisation strategies. Though it’s difficult to cover all aspects of SEO reporting through Excel/Calc broadly in this article, I’ll give it a try to discuss some more frequently used reporting techniques.

• Excel/Calc Dashboard Reporting

Excel/Calc Dashboard Reporting can be used for a summary of your SEO activities (daily/weekly/monthly). Basically, a dashboard report is a way to visually present critical data in summary form so that you can make quick and effective decisions, in much the same way that a car dashboard works.

How is this Dashboard Made?

1. The chart showing Keyword Ranking with Improvements and Back Link Details, search engine index details and 2 different charts arranged side by side showing Traffic and Sales Patterns based on data analysed as Previous and Present.
2. The Keyword Ranking section is reflecting keywords’ ranking with their present and previous positions as well as respective improvements.  
3. I have used Macro with Developer Tool, VLOOKUP formulas to process the data.

• Spreadsheet Formula to Count Words in Cell

One of the basic differences between Microsoft Excel and the OpenOffice Calc is in the use of macros and formulas. While Calc is less compatible in the use of the VBA (Visual Basic for Applications) language for macros, many problems can be solved by using formulas. On the other hand, Excel is the most efficient application in using both formulas and macros efficiently. You must be wondering, why I’ve chosen this formula for discussion. As an SEO export, you must have felt difficulty in selecting long tail keywords from a gross list of thousands of keywords. This formula ensures that you get the number of words in a list of key phrases in the adjacent cells. 

Formula to Count Words in a Cell for Excel or Calc

With a few minor changes, the following will work equally well in either Excel or Calc spreadsheets. Select adjacent cell of a key phrases of which you want to count the words and write the formula defined below. Change the cell number accordingly.

For Microsoft Excel

=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

For OpenOffice Calc

=IF(LEN(TRIM(A2))=0;0;LEN(TRIM(A2))-LEN(SUBSTITUTE(A2;" ";""))+1)

• Formula to Compile Local and Global Search Volume for Keywords

The VLOOKUP formula in MS Excel can be used to look up a table or array, and then return useful data. The function is easy to use, and needs only four items to be entered. The example used here gives a step-by-step explanation, as well as showing how to fix some of the problems often found with VLOOKUP.

Example:

Let’s take three excel files, such as:

1. Excel File #1 containing keywords list and their local search volumes.
2. Excel File #2 containing same keywords but arranged differently than Excel File #1 and their global volume.
3. Excel File #3 is the processed file where you can arrange the final list of keywords and both of their local and global search volumes are compiled.

Now write the following formula on next to the keywords list in the Excel File #3 as =VLOOKUP(A2,Excel File #1.xlsx!$A$2:$B$673,2,FALSE) ^{[for OpenOffice Calc: =VLOOKUP(A2;Excel File #1.xlsx!$A$2:$B$673;2;FALSE)]} to retrieve local search volumes.

Repeat the same formula on next to the local search volume list in the Excel File #3 to retrieve global search volume for the respective keywords from Excel File #2.
=VLOOKUP(A2,Excel File #2.xlsx!$A$2:$B$673,2,FALSE) ^{[for OpenOffice Calc: =VLOOKUP(A2;Excel File #2.xlsx!$A$2:$B$673;2;FALSE)]}

The file would look like this: 

I would like to conclude this article with these three basic Excel skills. Hope you apply these excel techniques while preparing SEO reports for your clients or selecting prospective keywords for SEO. Visit 247 TopSeller blog or subscribe to blog feed for more such intuitive pragmatic articles in future.

A big, warm and cuddly thanks to you all for reading this article. Everyday your feedbacks and comments help us devising new tricks on online selling & make us better at what we do. We are sure you feel the same about others comments. Do you like the article? Do you find it insightful? What modifications you would have made to it? Go ahead and share your ideas and tips with us. Please leave a comment.