SEO specialists often use spreadsheets to organize data. Excel or Google Sheets help to consolidate information about the semantic core (read how to collect SEM quickly), download URLs with technical problems, systematize the results of link profile analysis, and perform other routine tasks. In short, spreadsheets are the daily assistants of an SEO specialist. So why not learn to fully utilize the potential of Excel and Google Sheets? Let's learn! Read below about the most interesting formulas that can facilitate organic website promotion.
VLOOKUP/VPR
Use VLOOKUP when you need to find the amount of data in a table or range by row. Still not clear? Relax, now we'll explain everything with an example.
Let's say you've put together a good semantic core and want to see how many Clicks, Impressions, CTR in Google Search Console your keywords get (and whether they will get any at all).
To do this, we use the formula:
=VLOOKUP(A2,Sheet2!A:F,2,FALSE)
Still unclear? Relax, let's break it down now.
So, the formula again:
=VLOOKUP(A2,Sheet2!A:F,2,FALSE).
- A2 is the search range. In both Sheet 1 and Sheet 2, we have the Query column repeated, and the program “looks” for the key by which we need to show statistics.
- Sheet2! is the table tab in which we search for data (from GSC unloading).
- A:F is the range of columns in Sheet2!, where the search is performed.
- 2 is the serial number of the column in Sheet2!, where the search is performed (Query is the first column by serial number, Clicks is the second, Impressions is the third, and so on).
- FALSE (or can be replaced with 0 (zero) — means that only exact match search is allowed.
If the function does NOT find a specific key in Query (such as, for example, the internet marketing company key in the table above), it will show the error N/A.
If the value (TRUE) is entered, this means that you allow the search for an approximate match, i.e. in the case of an internet marketing company the function will try to find the key that is as similar as possible and will return the number of Clicks for this most similar key.
IMPORTRANGE
=IMPORTRANGE is like =VLOOKUP only from another table.
The syntax is as follows:
=IMPORTRANGE("https://PageURL"; "TableTabNameForImport!B64")
The example above shows how you can transfer data from one table to another through automation. Such formulas allow you to conveniently and quickly create, for example, pivot tables.
SPLIT
This formula outputs text separated by certain characters into different columns. Let's say you've collected low-quality URLs through Ahrefs that link to you and you want to send the domains for removal via Disavow tool. We apply the formula =SPLIT(A2,"/") — the column number and the symbol by which we separate the domains.
CONCATENATE
Want to add the domain prefix: as recommended by Google? Easy!
We use the formula =CONCATENATE("domain:",C2) — in quotes, we write WHAT we want to join to the column.
By the way, if you swap the elements of the formula instead of ("domain:",C2), but (C2, "domain:") , the prefix will be added to the end of the line (ichip.rudomain:)
SUMIF
The SUMIF formula is used to sum values in a table by a certain criterion.
For example, you have different blog categories and top articles in these categories. You want to find out how many clicks each category brings in total for all articles. We write the formula: =SUMIF(A2:A17, "PPC", C2:C17), that is, please see if the word PPC is in the range of the Blog Category column and please sum up all clicks on the blog post PPC. Now we see that it is blog posts about paid advertising that bring us the most traffic to the site, which means we need to pay maximum attention to optimizing these blog articles.
Bonus — TOP resources where you can learn more
Thank you for reading to the end! We hope the formulas will be useful. Below are the best resources to help you dig deeper into Google Sheets and Excel formulas and practice what you've learned:
Free video course on formulas from Google experts: https://www.coursera.org/learn/getting-started-with-google-sheets (English, with Russian subtitles).
Google Sheets training and help: https://support.google.com/a/users/answer/9282959?hl=ru (Russian, English)
