SEO-experts often use spreadsheets to organize data. Excel/Google Sheets help aggregate information about targeted queries, export URLs with technical issues, organize link analysis results and perform other routine tasks. Spreadsheets are the daily helpers of every SEO specialist. So, let's learn how to take full advantage of Excel and Google Sheets. Read below about the most interesting formulas that can facilitate the SEO promotion of your website.
VLOOKUP
Use VLOOKUP formula when you want to find data volumes in a table or range by row. Not clear yet? No worries, let us explain everything with examples.
Let's say you've collected a list of search queries and want to see how many Clicks, Impressions, CTR in the Google Search Console your keywords get (and whether, they get at all).
To do this, use the formula:
=VLOOKUP(A2,Sheet2!A:F,2,FALSE
Still don't get it? No worries, let's dive deep into the details.
So, let's look at the formula one more time:
=VLOOKUP(A2,Sheet2!A:F,2,FALSE).
A2 — is the search range. In the Sheet1 tab and in the Sheet2 tab “Query” columns are the same, in those columns the program looks for a keyword to show the statistics
Sheet2! — this is the tab of the table in which we look for the data (with GSC exporting)
A:F — this is the range of columns in Sheet2!, where the search takes place
2 — this is the serial number of the column in Sheet2 !, where the search takes place (Query — the first column by serial number, Clicks — the second, Impressions — the third, and so on).
FALSE (you can change it for a “0” (zero)) means that only exact matches are allowed.
Unless the function find a specific keyword in Query (such as the “internet marketing company” keyword in the table above), it will show an N/A error.
If you enter an indicator (TRUE), it means that you allow the search for inaccurate and approximate matches. For instance, if the formula with (TRUE) attribute finds "..." in column A, it will try to find the keyword that is as similar as possible and give the number of Clicks for that most similar keyword. Here you can read about other tools for analytics and technical optimization that could be useful for SEO-experts.
IMPORTRANGE
=IMPORTRANGE is like =VLOOKUP but for the other spreadsheet.
The syntax is:
=IMPORTRANGE("https://URLofThePage"; "TheNaneOfSheetInASpreadsheetForInpotr!B64")
The example above shows how you can transfer data from one table to the other automatically. For instance, such formulas allow you to create summary tables easily and quickly.
SPLIT
This formula displays text separated by specific characters in different columns. For example, you have collected poor quality URLs via Ahrefs which link to your website, and you want to add these domains to the Disavow tool.
We use the formula =SPLIT (A2, "/") — the column number and the symbol by which we separate the domains.
To learn more about effective link building strategies read our article.
CONCATENATE
Want to add the prefix “domain:” as it recommended by Google with the aim to remove the links from the other site to your site via disavow tool?
Easy!
We use the formula =CONCATENATE ("domain:", C2), in quotation marks we write WHAT we want to attach to a column.
By the way, if you change the elements in the formula, like C2, "domain:", the prefix will be added to the end of the line — “ichip.rudomain:”.
SUMIF
The SUMIF formula is used to sum the values in a table by a specific attribute.
For example, you have different blog categories and the most popular articles in those categories. You want to find out how many clicks each category brings in total for all articles.
Write down the formula: =SUMIF(A2: A17, "PPC", C2: C17), here we ask the program to check if the word “PPC” is in the range of the Blog Category column and ask to summarize all clicks on the PPC-related blog post. Now we see that blog posts about paid advertising bring us the most clicks from the SERP, so we need to pay maximum attention to optimizing these blog articles.
Bonus: the TOP resources where you can learn more
Thank you for reading to the end! We hope that the formulas will be useful upgrade your daily routine at work. Here are the best resources for you to help dig deeper into Google Sheets and Excel formulas and practice your knowledge:
Free video course about formulas from Google specialists:
https://www.coursera.org/learn/getting—started—with—google—sheets.- Google Spreadsheets Training and Help:
https://support.google.com/a/users/answer/9282959?hl=en_US
