If there is one tool that a professional SEO or PPC manager will use frequently it’s Microsoft Excel. Becoming proficient in the more advanced aspects of Excel using formulas and functions can make a meaningful difference to being able to successfully mine SEO and traffic data to discover useful nuggets of information that can be acted upon.
Here are 5 great ways for SEOs to use Excel. See here for an article on other Excel functions.
1. Make Things “Proper” with this Function
Contents
The Proper function is not used much even by Excel experts and many others users do not know that the function exists at all. The Proper function is used for bringing together any batch of text and making the first letter of each word a capital letter instead of a lower-case letter. A good way to remember this is that it is like the capitalisation of a proper noun for people, places or things in the English language.
Where can this function be useful for an SEO practitioner? Title tags can be loaded into the cell and formatted in an attractive manner using the Proper function. If you are using PPC with SEO tactics, then you can also capitalise the first letter of each word in the ad title with this tool.
Reports from SEMRush and other SEO tools often extract keyword search results with lower-case keyword listings, so the ability to re-capitalise this list of titles can be a real time saver.
The syntax is:
Formula: =PROPER(cell)
2. Using the Randomising Function
The RAND function is capable of creating a random number which can be a big help when needing to build random lists of things like URLs. Developing lists in a random order is a useful way to help the process of link building appear more natural because there is less logic and standardisation to the process being used.
One way to do this with a column of links or other information is to enter =RAND() for each row in a single column to create a series of random numbers. Once this has been done, you’ll need to fix the numbers so they are not re-generated each time as new random numbers. To do this, you need to highlight this list of randomly generated numbers and copy them all into a new column using the “Paste as Values” command.
The list can then be sorted in the order of the random numbers which essentially randomised the list itself. It is a roundabout way of accomplishing the random list task, but it works.
The syntax is:
Formula: =RAND(cell)
3. Concatenate Cells
Concatenation can be used to merge strings together by combining one cell with another cell. With SEO, this may be used to combine names and address, website names and website URLs, and other pairs of information into a single cell for easier export and formatting later.
The syntax is:
Formula: =CONCATENATE(A1,B1) will concatenate the contents of cell A1 and B1 together.
4. LEFT, MID, and RIGHT
These little wonders can make light work of moving inside the contents of a cell and taking what you want from it.
LEFT will return a certain number of characters starting from the left side of the cell. RIGHT will return a certain number of characters starting from the right side of the cell. MID will return a certain number of characters as a starting point, and then count a series of characters from that starting point.
The syntax is:
=LEFT(B1,6) will return the first seven characters in the string from cell B1.
=RIGHT(B1,10) were returned the rightmost 10 characters in the string from cell B1.
=MID(B1,10,5) will return the characters in the string starting at position 10 and taking the next 5 characters from that point.
5. Counting Instances
When you need to count the number of instances of a certain string rather than sum up totals, then use COUNTIF.
The syntax is:
=COUNTIF(B2:B6,”invalid-link”)
There are many different ways to use Excel to improve the efficiency and speed of SEO activities. Learning to use Excel better will help to service clients faster and provide better analytical information.
About Brandon Leibowitz
