Essential Spreadsheet Formulas to Learn for Keyword Processing
We know that search engines play a vital role in today’s digital age. They help direct people to relevant websites in online searches. Because of this, online businesses have to adapt so they’ll appear in the SERPs.
If they do nothing, they’ll lose money to businesses whose websites appear on the first page for a particular keyword search.
SEO or Search Engine Optimization is a valuable skill because it helps businesses make money.
Now, if you’re a Virtual Assistant or an aspiring freelancer who wants to do SEO, you need to be proficient with spreadsheet applications like MS Excel or Google Sheets.
One of the main tasks that SEO professionals do is Keyword(KW) Processing.
Keyword Processing
This is an integral part of launching an effective SEO campaign because these are the keywords your site or pages should contain. Having the relevant keywords help search engines understand what your site is all about.
You do this by identifying the intent behind online searches.
And where to get the keywords?
You can get it from SEO tools like Ahrefs or SEMrush. How you classify the keywords can be broken down into 3 major categories.
3 ways to classify keywords
Informational Keywords
These keywords help people know more about what they’re searching for.
Informational keywords are easy to spot. They usually contain the words diy, where to buy, best, review, vs, etc.
Irrelevant Keywords
These keywords can also be called negative keywords.
They would vary greatly per niche and are used for Google ads.
Relevant Keywords
These are the keywords you can use to help you with your on-page SEO efforts.
You can use most of these keywords for product names or titles, article topics, or for your meta titles or meta descriptions.
The essential spreadsheet formulas
Since KW processing deals with lots of data, the data extracted from SEO tools typically contain hundreds to thousands of rows.
This is why being proficient with spreadsheet applications is a must. Your spreadsheet applications skills will help you finish your tasks on time without compromising quality.
If you feel that your skills need improvement, here are the essential spreadsheet formulas/functions to learn if you want to do SEO and keyword processing.
Regexmatch (note: only works within Google Sheets)
Regexmatch searches for a specified string in a given range and tells you whether it has found that string or not in a particular cell.
Instead of looking upon rows of cells, using this formula helps you automate the process and save lots of time when used correctly.
This is how to use regexmatch.
For this function, “text” refers to the cell that you want regexmatch to look into and the “regular_expression” is the string that you are looking out for.
In the image shown above, the string that we want to look for is “ultra marathon shoes”.
If regexmatch can detect this specific string in the cells in column A, it would return TRUE and if not, it would return FALSE.
Another good thing about regexmatch is that you’re not limited to only a single regular expression. We can also specify multiple regular expressions and the way to do that is to separate each regular expression you specify with a vertical line “|”.
Lastly, the regexmatch function is case-sensitive. So make sure that your regular expressions are written in a way they would appear. For this example, we’ll just deal with lowercase characters since that is the format you get when extracting keywords from SEO tools like Ahrefs or SEMrush.
In the image shown below, the cells that contained the specified regular expressions: ultra marathon shoes, ultramarathon shoes, and marathon running shoes were returned as TRUE.
Textjoin
Textjoin is a function that you can use to help you come up with multiple regular expressions for regexmatch use quickly and easily.
To set up textjoin, the first thing you need to do is to list down all the words or string that you want to use for regexmatch in a single column
The next step would be to input the textjoin formula =textjoin(
And the following parameters for the textjoin function are the following:
To set up the example shown in the image below, the textjoin formula would look like this:
Once you’re done setting up the textjoin formula, the result would look like this:
Conditional Formatting
Dealing with huge amounts of data is never easy. However, by adding some color to it, you can easily spot those cells that need to be looked at closely and that’s what conditional formatting can help you with.
With the help of conditional formatting, you can set rules where the cell’s background or text changes its color automatically when it meets your set criteria.
To get this going (in Google Sheets), go to Format > Conditional formatting
After activating conditional formatting, this will pop up on the right side of your screen so you can set the criteria.
For this example, we’ll be using single color to set up conditional formatting.
The next step would be to set the range. Range refers to the cells which the conditional formatting function would change automatically when it meets your criteria (which we will be cover in the succeeding steps).
To set the range, you need to click on the table icon beside B2 under Apply to range.
Set the range by highlighting the cells whose values you want to change if they meet your set criteria and click OK.
In Format rules, you need to select the condition that would best apply to your situation. For this example, we’ll select Text is exactly
Input the value that will serve as your criteria.
The next step would be to select the change that you want to happen if ever the data found on the range of cells you’ve selected has been met. For this tutorial, we want the cell’s background color to change automatically.
Click Done after selecting your preferred background color.
Filter function
The filter function helps you select the data you want to check closely and activating this function is pretty straightforward.
What you need to do first is to highlight the range of cells that you want to be included by the filter function.
Click the funnel icon.
After turning on the filter function, you can choose the column whose data you want to check up close.
Click the filter icon on the column you want to check.
The filter function can help you sort cells by color,
filter by condition to define what will be shown to you,
or directly select the data you want to see by checking or unchecking the contents found in a particular column of your liking.
Find and replace function
Some of you may not be familiar with this feature. The find and replace function helps you quickly change any data without relying on the filter function.
The first step is to highlight the range of cells you want to manipulate.
Press CTRL + F to activate the find function and click the 3 vertically arranged dots for More options.
Specify the data you want to change. We’ll replace the word shoes with sneakers.
Type in sneakers to specify your replacement word.
Click the Replace all button once everything is set.
After clicking the Replace all button, you can see that the entries containing shoes in column A have been replaced with sneakers.
Split Text to Columns
Another function that can help you with keyword processing is Split Text to Columns.
This function can expand the contents found in a cell into different columns based on your given criteria(separator). This can help count how many times a keyword or certain word appears in your data set.
In SEO, how many times a word appears can also be referred to as keyword density.
As with most of the functions mentioned above, the first step involves highlighting the data or range of cells you want to be changed.
Go to Data > Split text to columns.
Click the separator box. From there, you can choose how you want the data in the cell to be expanded. Let’s say that we want to expand the data by word, so we’ll choose Space.
After you have indicated your separator, the data in the cell will be expanded automatically.
If statements
The If function helps you perform a logical test or series of tests and will give you the results you want according to your set parameters.
In a real case scenario, we can use the if statement to help us automatically identify the results of regexmatch during keyword processing.
In the example shown below, we would like to know if the keywords in the data set are irrelevant, informational, relevant, or none of the above.
Please note that if the value under each of the respective columns is TRUE, it means that the keyword has met the criteria set in the regexmatch function to determine if the keyword is irrelevant, informational, or relevant.
The if statement should be constructed this way.
After constructing the formula correctly, Google Sheets will ask you if you want to apply the formula to the other cells (auto fill).
Click the check button or hit CTRL + ENTER to autofill.
The if statement reads the logical operations from left to right. It means that if the value for each of the columns is TRUE, it would tell us that the keyword is irrelevant.
The keyword is considered relevant only when the relevant column is TRUE. If all of the columns are FALSE, then the cell should be blank.
This is to help us identify the keywords that need a closer look and classifying them as irrelevant, informational, or relevant needs to be done manually.
CountIF
The CountIF function counts the number of times the value or contents of a cell meets your specified criteria.
In this example, we want to know how many keywords were classified as irrelevant, informational, or relevant.
The parameters of the CountIF formula are range and criterion. Range refers to the cells you want to count and criterion refers to the value you want to be counted.
To count how many keywords were classified as irrelevant, the formula would look like this.
To count how many keywords were classified as informational and relevant, just copy and paste the formula in the cells found below or you can also take advantage of the auto-fill feature.
Please take note that the $ symbol before the cell’s row letter and column number means that the range for the CountIF function was made into absolute reference.
This is to make sure that the range for all the criteria used with CountIF remains the same.
SumIF
The SumIF is somehow similar to CountIF but this time, it sums the value of your specified data range.
This is useful when you’re looking at numbers. When doing SEO work, this function helps when looking at the keyword’s monthly search volume.
The parameters for SumIF are range, criterion, and sum range.
Please note that the monthly search volume figures in this example are fictitious and are only meant to help describe how the SumIF function works.
Closing
These essential spreadsheet formulas or functions help us perform keyword processing much faster without sacrificing the quality of our work.
Learning them can be a challenge, but once you get to understand how they work, dealing with huge amounts of data just became a little bit easier and more enjoyable too.
For business owners, hiring a Virtual Assistant with this level of proficiency in using spreadsheet applications means that they are worth every dollar you are paying them to do tasks for you.
So are you specifically looking for one right now?