Google Sheets for SEO

Google Sheets was launched under the working name Google Labs Spreadsheets in the summer of 2006, based on the XL2Web project acquired by Google that same year. It is now one of the most powerful tools for processing and organizing data in the form of interactive tables. It is capable of doing everything that may be required from split text to google sheets columns to analyzing the performance indicators of an advertising campaign. Key services such as Google Search Console, Google Keyword Planner, and others support exporting data in Google Sheets format. This feature allows you to make an upload and automatically add it to your Google Drive. You can upload compatible document formats such as xlsx and csv to Google Drive and select the “Open in Google Sheets” option. The working file will have a universal Google Sheets format, with the possibility of subsequent export.

Online spreadsheets have several key advantages over their more-traditional counterparts. They are universal and do not depend on the software installed on a particular computer, which greatly facilitates working with data in distributed teams. Sharing a file is also very simple. All you need to do is send a link in the messenger or add the appropriate access rights to the desired email address. After, a notification email will be sent automatically. Autosave, an online archive of revisions and changes, and the ability to quickly add comments by marking the correct participant will additionally increase productivity.

Basic google sheets formulas

SEARCH

It is important to note that the function will only work on the first matching result. For example, if you want to count the number of letters “e” in the line “regeneration”, this function will not work. This is because its result will return only the position of the first letter “e”, that is, the number 2. However, the function has many uses. For example, if you have several email addresses and you want to add a column that will display exclusively the domain for each mail address.

Based on the data structure, the domain name always comes after the “@” symbol. This means that you need to return the part of the string that is after a certain position in the text. Depending on where the desired piece of text is located, you can set both the beginning and end of the line as the original value using the LEFT or RIGHT functions, respectively. Let’s start by determining the total length of the string using the LEN function. For cell A2, the formula will look like this:

=LEN(A2)

Now, if we take the beginning of the line as the starting position, we need to count the number of characters to the desired position – “@”. To do this, define the position “@” using the SEARCH function. For cell A2, the formula will look like this:

=SEARCH(“@”,A2,1).

Note that if you are looking for text values, you must use quotation marks around them.

You can also search for values in other cells. When doing this, the quotes are not required. Replace the values in column C with the desired character and add the updated search formula to column D:

=SEARCH(C2,A2,1)

This makes the table larger. However, if we want to change the desired value, we will not need to rewrite the formula. Let’s use the RIGHT function to get the desired result. The peculiarity of its use is that the counting will be carried out from the right edge end of the text. Accordingly, you need to return all values before the “@” sign. To do this, you need to subtract the position of the desired character from the total length of the string. The formula in this case will look like this:

=RIGHT(A2,B2-D2)

Naturally, the final version of the formula can fit into one cell by combining all the intermediate steps. Do not forget to take relative values into account. In our case, the final formula will look like this:

=RIGHT(A2,LEN(A2)-SEARCH(“@”,A2,1)).

TODAY

An indispensable function if you want to calculate a certain duration, for example, in days from a known date. Let’s say you are planning an email campaign and want to send a newsletter to everyone who has subscribed for more than a week. The following formula will help you calculate the difference between two dates:

=TODAY()-C2

Please note that you need to subtract from a larger value. In this case, subtract from today’s date.

IF and variations

The standard IF function only covers one condition.

Let’s continue the previous example and add a check for compliance with the required boolean condition for each of the subscribers. If the address has been in the list for more than a week, then the values “yes” will be displayed, if less – the value “no”. The final formula will look like this:

=IF(D2>7,”Yes”,”No”)

In this case, just like SEARCH, you can make the function more dynamic. To be able to change the duration of the check period, add the appropriate column, and enter the required number of days there – 7. After that, add an updated formula of this type to the adjacent column:

=IF(D2>E2,”Yes”,”No”)

Variations of the standard IFS function allow you to test several conditions. The main difference is that if the first Boolean expression specified fails, the function will check on the next one. For example, you need to indicate which of the addressees has been signed for more than a and select ones who have been signed for less than two weeks. The final function for such a check would look like this:

IFS(D2<E2,”No”,D2<14,”Yes”,D2>14,”No”)

Note that Boolean expressions are tested sequentially. We start with the original criterion again but now we change the expression. Thus, if the value is less than 7 days, the first condition is met and the function is aborted, returning the specified “No” parameter. If the first check succeeds and the value is greater than 7, the second check filters out matching results. If their value is less than 14, it returns the specified Yes value. The third parameter was entered to avoid an error because if the value does not meet any of the entered criteria, the function will assign the value # N/A to the cell.

A special IFNA error check can replace the last step of the previous function. To do this, we will leave two arguments of interest to us, adding a check of the result for errors. The result will look like this:

=IFNA(F2,”Error”)

You can also combine both functions into one:

=IFNA(IFS(D2<E2,”No”,D2<14,”Yes”),”No”)

COUNT and variations

Sometimes you need to find out the number of values in a data array. This function is ideal for this task. Let’s say you need to find out the number of keywords with the content of the desired name or brand. To do this, add the desired value to the search function or use a check column. In the second case, the function will look like this:

=SEARCH(B1,A1,1)

You can use the boolean IF function to make check values more convenient. The SEARCH function will return errors in absence of the desired value, so apply a variation of IFERROR:

=IFERROR(SEARCH(B1,A1,1)>0,”FALSE”)

Since the COUNT function only counts digital values, we convert the desired results (TRUE) to 1, leaving the FALSE values unchanged: =IF(IFERROR(SEARCH(B1,A1,1)>0,”FALSE”)=TRUE,1)

Let’s now add the COUNT function for the results column:

=COUNT(C1:C20)

In order to count the number of values in a range, regardless of their type, we recommend using a variation of the COUNTA function. To take into account only the necessary values, we will slightly change the criteria for checking the logical expression:

=IFERROR(SEARCH(B1,A1,1)>0)

Like the previous case, let’s add the final formula to the desired column: =COUNTA(C1:C20)

The function of the conditional account COUNTIF can also perform the task of checking compliance with the required criteria. To do this, let’s simplify the formula for the initial check for the presence of the required keyword. Use =SEARCH(B1,A1,1)

As you can see, there is no match, the #VALUE! Now let’s use a conditional account COUNTIF: =COUNTIF(C1:C20,”>0″)

Note that the boolean expression must be enclosed in quotation marks because you would do this with a text value in any other function. If the task of the function is to check against one criterion or variable, then quotes can be omitted. For example, to find out the number of all cells that received the value of the same #VALUE! the formula will look like this:

=COUNTIF(C1:C20,#VALUE!)

For example, you can also specify a cell instead of a specific value. This will make the formula more dynamic. Note again that this method only checks the value for a complete match with the cell content, replacing the equal sign.

If you want to get the number of values that meet multiple criteria at once, use the derived function COUNTIFS. Note that, unlike IFS, the expression is tested against all criteria, not just one. Only cells that have met all the conditions entered in the formula will be counted: =COUNTIFS(C1:C20, “>0”, C1:C20, “<>#VALUE!”)

For the second condition, the logical operator is not equal – <>.

Another conditioning shield method, COUNTBLANK, returns the number of blanks in a range. If we slightly change the primary check formula, we can apply it as well: =IFERROR(SEARCH(B2,A2,1),””)

Now, if a string does not contain a keyword, it is assigned an empty value that the COUNTBLANK function can account for.

If we want to get the number of other values, COUNTBLANK is also useful. To do this, count the number of rows in the range of interest using the ROWS function and subtract the result of the final check from it:

=ROWS(C1:C20)-COUNTBLANK(C1:C20)

The COUNTUNIQUE variation is also indispensable when working with keywords and analytical unloading. This returns only unique values in a range. For example, if its value is greater than 1 for the column with the keyword that we are looking for, then a mistake was made somewhere.

At the same time, this function will allow you to find out the number of unique key phrases in the original criteria.

In addition, we can calculate the number of unique keywords in all the original phrases we entered. To do this, you need to deconstruct each phrase into separate keywords using the SPLIT function, using a space as an indicator: =SPLIT(A1,” “)

Note that the SPLIT function itself is only contained in the cell where you enter it. The rest are assigned values according to the results of the function. Also for the correct decomposition, cells must be empty, since the function cannot overwrite their contents. Now let’s enter a range with the results of the SPLIT function as the target for the conditional account.

As you can see, we used only 13 unique keywords. Considering that two of them are our test criteria: “brand” and “other thing”, there are actually only 10 unique keywords directly.

This statement can be verified using another derived function, COUNTUNIQUEIFS. An interesting feature is that the first value defines the total range where the function will operate, but subsequent values can refer to individual columns or parts of this range. In our case, the final formula will look like this:

=COUNTUNIQUEIFS(C1:E20,C1:E20,”<>brand”,C1:E20,”<>other”,C1:E20,”<>thing”)

SUMIF

This is a simple and powerful formula that sums arguments over a specified range when a specified condition is met. Let’s say you have a list of visitors who signed up for a newsletter and you would like to know the total revenue from those who actually paid for the subscription.

To do this, add an additional criterion like a column with the cost of a one-time subscription.

Now you can use the matching of the values in column D to the condition as the main argument for the function. The final expression will look like this:

=SUMIF(D2:D18,”Yes”,E2:E18)

Given that the cost of a subscription is a constant, we can calculate the conversion of users at each stage. For example, applying a similar function to column C would generate a potential income if all signers completed the transaction. For this example, the conversion between stages can be calculated by converting the amounts into the number of people due to the division by the cost of one subscription. However, since the cost does not change, it is enough to simply find the ratio of these two numbers and set the format of the number to%.

LEN, RIGHT / LEFT

It returns the length of the specified string in characters, including spaces. Often used when you need to extract a specific part of a text string. For example, let’s try to split existing usernames into two columns with a first and last name.

To do this, add two columns that will contain the desired values.

To return a part of the value of a cell containing a text string, use the RIGHT function. Since the LEN function returns the length of the entire string, using it without modifications will get the same value as in the original cell.

To get the value of the surname, you need to specify the number of characters to be returned. Let’s calculate the position at which the separator between words is located. This is a space in our case. The final formula will look like this: =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

The value of the first name can also be returned by calculating the length of the last name and subtracting it from the original value. This time we will use the LEFT function. The final form of the formula will be as follows: =LEFT(A2,LEN(A2)-LEN(C2))

ARRAY FORMULA

This is another extremely interesting tool that will allow you to use almost any Google Sheets function on an array of data, instead of single values. For example, we want to indicate the potential income from a user depending on their status at each stage of the subscription process. Let’s add columns first.

Now we will use the simplest logical IF statement but instead of checking for each cell, we will create a rule that will apply to the entire range.

For the rule to work on other columns as well, you need to make two changes. First, fix the cell address with the subscription cost, and then set the currency format to the data. The final formula will look like this:

=ARRAYFORMULA(IF(H2:H18=”Yes”,$K$1,0))

Note that for correct operation, the range of values must be empty where the ARRAY FORMULA function will expand the results.

SORT

This is a tool for sorting data in an array. This function returns data in a specific order based on conditions. For example, if we want to sort users based on which of them subscribed, but did not pay for the subscription, we could create a corresponding mailing list. First, we copy the existing headers for the range where the function results will be expanded.

Now, place the SORT function in the first cell of the range, from which the results will be expanded. As an array of data, we will indicate the entire selection of interest to us. As a sorting criterion, we will indicate the 7th column.

The function took up the entire available range and placed users who passed the subscription stage successfully at the top of the list. Now we will add an additional criterion in order to leave those who haven’t paid at the beginning. The final form of the formula will be like this:

=SORT(A2:I18,7,FALSE,9,TRUE)

The results now meet both criteria entered.

UNIQUE

This function allows you to return only unique values from the range of interest, ignoring all repetitions. For example, there are not only first and last names but also dates when users signed up for the newsletter.

The UNIQUE function will allow you to return days when users sign up for the mailing list.

Now we can calculate the number of subscribers on each specific day using a conditional account: =COUNTIF($B$2:$B$18,A22)

SPLIT

This is a simple but indispensable tool for processing bulk data. This formula splits a string into separate values based on the specified delimiter character. The results are expanded to the right of the original cell. One use is to split the exported user’s name into first and last names in separate cells. In our example, together LEFT and RIGHT, you can use the SPLIT function to get the same result.

To do this, add the required formula to the first cell. In this case ,it will be a cell with a name: =SPLIT(A2,” “)

To automate the process even more and not to stretch this formula through all the cells of interest, we will use the ARRAY FORMULA modifier. Let’s add it to the original cell, specifying the desired range of values and space as a separator: =ARRAYFORMULA(SPLIT(A2:A18,” “))

Thus, we indicated only one formula but we got the desired values for all rows in the required range. Any character can be used as a separator. For example, we can similarly decompose any formatted date into separate values of the day, month, and year.

In this case, we will also use ARRAY FORMULA and specify the slash as the required separator character: =ARRAYFORMULA(SPLIT(A22:A30,”/”))

CONCATENATE, AND, JOIN

A lot of care has been taken to get a fraction of an integer value. Whether it be text or numeric, the CONCATENATE and JOIN functions help with other tasks. Their main purpose is to combine the indicated values, within certain rules. For example, CONCATENATE will help when you need to add special delimiters. This is often encountered when working with timestamps. For example, there is the date and the exact time of the user’s subscription.

In order to combine date and time in one cell, a simple addition operation is sufficient.

How to achieve an arbitrary date format, for example, with working/non-working hours, discounts, or holidays? To do this, add the appropriate columns and fill them with the appropriate data. In order not to write down each value separately, we will create a line with possible variants of text marks, as well as time intervals with which we will compare.

Now, let’s use the IFS boolean operator to return a matching tag to each value. In order to check for compliance with several conditions, we use the logical AND operator. The final formula for checking working hours will look like this: =IFS(AND(C2>P6,AC2<Q6),O9)

Let’s talk about what happens when processing this function and why it is convenient to use the AND operator. We used IFS because we plan to add several conditions: checking working hours, lunch, happy hours, etc. The IFS function sequentially tests the entered conditions one at a time and returns the specified value for the first Boolean expression to be evaluated and returns TRUE. This means that in order to check several conditions during the range from the beginning to the end of the working day, you need to plan the logic in such a way that the answer is TRUE or FALSE. The logical expression itself is as simple as possible. In this case, we avoid a lot of sequential checks and replace the first argument of the IFS function with the logical operator AND. Its main purpose is to check all entered criteria and if all conditions are met, the IFS function will return TRUE. In this case, the process looks like this:

However, this function will not work. We will explain why. The point is that we are checking several criteria: the range from 10 to 18, from 13 to 14, and from 14 to 16. As you have probably noticed, two ranges from 13 to 14 and from 14 to 16 are contained in the first as children’s ones. If the function checks sequentially, then the first correct expression will be the result. The first check goes to the mother range, from 10 to 18. If this condition is met, subsequent checks will not occur. To do this, we simply change the order of checks in the function so that they do not intersect and the sequential check of conditions proceeds from narrower criteria to wider ones:

=IFS(AND(C2>P5,C2<Q5),O13,AND(C2>P4,C2<Q4),O10,AND(C2>P6,AC2<Q6),O9)

Now let’s add a check for non-working hours, putting it as the very first one as the narrowest criterion and fixing the cells containing the conditions for comparison and the results:

=IFS(C2>$Q$6,$O$11,C2<$P$6,$O$12,AND(C2>$P$5,C2<$Q$5),$O$13,AND(C2>$P$4,C2<$Q$4),$O$10,AND(C2>$P$6,AC2<$Q$6),$O$9)

Now we have got all the values we want and would like to concatenate them into one string using the CONCATENATE function: =CONCATENATE(TO_TEXT(B2), TO_TEXT(C2),D2)

Note that we also used the functions of parsing formatted date and time into text. This was necessary in order to preserve the original form of the values in the format that was specified for each cell. If we do not use text parsing of values, then for the date in this case we will get “44054” instead of “8/11/2020”, and for the time – “0.465277777777778” instead of “11:10”. It is also important to note that the result does not contain spaces between the values that make up the cell. Let’s add spaces and parentheses to our formula using the & operator:

=CONCATENATE(TO_TEXT(B2)&” “,TO_TEXT(C2)&” “,”(“&D2&”)”)

The alternative JOIN function will avoid the need to add spaces because it supports arbitrary separators for all specified values. Thanks to JOIN, we will be able to avoid the necessity of parsing dates and times. The final formula will look like this:

=JOIN(” “,B2,C2,”(“&D2&”)”)

In our case, this is a much more elegant and simpler solution. Do not forget that the JOIN function supports work with individual values and data arrays. In this case, the expansion of results will occur only in the cell containing the function. Therefore, to process all rows and display the results correctly, it would be best to use ARRAY FORMULA together with JOIN.

SUBSTITUTE

The SUBSTITUTE function allows you to replace a specified character or expression in the target string with the desired variant. For example, you have a set of expressions or brand names and you want to automatically generate links to search for each of these expressions on Google.

If we connect the known part of the link with the expression, we will get an incorrect link. This is because the spaces need to be replaced with the text value “% 20”. Because of this, replacing the desired expression when creating the final link comes in handy: =CONCATENATE($A$8,SUBSTITUTE(A2,” “,”%20”))

As a slightly more complex alternative, you can use a simple concatenation of text variables using the “&” operator. In this case, there is a risk that the link will not be recognized automatically and the formula will return the correct address as text, but not a clickable hyperlink. To do this, you need to use the HYPERLINK function, giving the link address as the first argument, and the anchor text as the second: =HYPERLINK($A$8&SUBSTITUTE(A2,” “,”%20″),”Working Google Search Link”)

REGEXTRACT и IMPORTXML

A function that allows you to extract part of a value based on a regular expression. This can be a handy tool when handling values with a similar structure, such as references. Let’s say there are several page addresses with links to products or stores. You need to highlight the corresponding product or point of sale names in a separate column as text values.

In our case, the task is further complicated by the fact that the link contains only a serial number that identifies the store. It also does not contain a name that could be distinguished. In order to complete the task, you need to load part of the page into the body of the table. Fortunately, the IMPORT XML function can handle this task easily: =ImportXML(A2, “//title/text()”)

Since the result takes up more than one cell, use the TRANSPOSE function if you want to get results in two cells of the same column: =TRANSPOSE(ImportXML(A2, “//title/text()”))

If you want to get the page title value merged into one cell, the JOIN function is ideal. However, it requires two arguments to work correctly, while our function is still only one. A more suitable analog would be to use the TEXTJOIN function, which also supports the use of spaces as a separator and is able to ignore empty values: =TEXTJOIN(” “,TRUE,ImportXML(A2, “//title/text()”))

The CONCATENATE function will give a similar result. In this case, we cannot add a space between the values of the two aligned cells. It is not that important, but it can affect the performance of more complex tasks. Now that we’ve got a list of all the page titles we need, we can parse the results as regular expressions.

In order to get the value of the desired city, you need to specify the correct interval in the regular expression. This must be returned as a value. The final formula will look like this: =REGEXEXTRACT(B2,”in (.*),”)

VLOOKUP и HLOOKUP

Vertical and horizontal search formulas will be indispensable if you want to return a certain number of values from a large data dump without applying filters. For example, if you need to find from which city a certain subscriber is.

To do this, it is enough to enter the known name of the subscriber and calculate the column number containing information about the cities: =VLOOKUP(A22,A2:N18,8,FALSE)

The horizontal search function can be used to display data by user number or relative position in the list. For example this way: =HLOOKUP(“City”,A1:N18,MATCH(A22,A1:A18,0),FALSE)

In this case, the function first finds the relative position of the cell with the desired username from the beginning of the column and navigates to the column with city information. It then returns the value of the relevant cell as a result.

IMPORTRANGE

This function allows you to dynamically load information from other Google Sheets files. It is enough to link to the desired table and the correct range. For example, create an empty file and try to load the obtained information about stores and their locations from the previous example.