Google Sheets is a game-changer and a well-loved program by many SEOs. Google Sheets is very similar to Microsoft Excel; only it is free to use and cloud-based.
There are several Google Sheets functions every SEO should know. Google Sheets can manipulate data sets (Site Explorer export), scrap data from other websites, and create a semi-automated workflow for SEO tasks.
Learning and maximizing the use of Google Sheets formulas can help you work more efficiently and save time and effort. Below are some of the important Google Sheet formulas that are helpful for SEOs.
8 Google Sheets Formulas That you Must Know
SEOs have so much work to do that cleaning up a cluttered and disorganized spreadsheet wastes time. Not to mention, they will need to create their spreadsheets from scratch as well.
However, with customizable spreadsheet templates, every SEO need not worry about how their spreadsheet will turn out.
There are several complicated sheet formulas that you must remember. However, remembering them all can be challenging. Hence, a customizable spreadsheet template with built-in functions ready for data encoding is a treat for every SEO.
The formulas discussed below are a must-know that will make every SEO work smarter and more efficiently.
1. Import Data and Merging Data Sets Using VLOOKUP Formula
VLOOKUP formula or Vertical Lookup formula is a favorite of many SEOs. It offers several functions, such as the capability to pull data from another spreadsheet, provided that these two spreadsheets share a common value.
Combining two spreadsheets is easier with the VLOOKUP function, for you don’t have to manually copy and paste large data sets.
The VLOOKUP formula is =VLOOKUP(Search_Key,Range,Index,is_sorted). The following elements of the formula are discussed below:
- Search Key. This is the cell or data value that is common with both spreadsheets.
- Range. This is the range of columns that the formula should read.
- Index. This is the column number you pull data range from.
- is_sorted. This is often set to “0” or “False” to return an exact match.
2. Merging Data Cells Using Concatenate
When you are working with URLs and you want to merge data cells, the concatenate formula is beneficial. Some website URLs are not uniformly formatted when you download them.
For instance, you downloaded a list of URLs from Google Analytics. You noticed that some portions of the URL are missing, like a domain name or leading protocol.
You don’t have to correct or update everything single cell manually. Instead, you can use the concatenate function =CONCATENATE(range).
3. Removing Data From Website Using IMPORTXML Formula
The Google Sheets formula IMPORTXML function helps import data from various structured data types like XML, HTML, and RSS. This means you can remove data from a website without leaving Google Sheets.
The IMPORTXML formula is =IMPORTXML(URL,xpath_query).
The IMPORTXML formula is powerful in removing almost all virtual data as long as you know the XPath. Here are some of the data that you can remove or scrap off using the IMPORTXML formula:
- Meta data set or meta description (like title, description, etc) from a URL list
- Email addresses from websites
- Profiles from social media accounts such as Facebook from websites
4. Google Search Console
SEOs know that a Google Search Console helps compare GSC performance metrics of two different periods. Such time periods can be before and after a web release or a website update. The data gathered from these periods are used to track changes in performance metrics.
This handy Google Sheets function allows you to export data via Google Sheets.
5. Determining Category Totals With SUMIF Function
The SUMIF function is useful when adding categories of values according to a particular criterion. The formula for SUMIF is =SUMIF(range,criterion,[sum_range]).
For instance, you want to determine the total views of your entire blog posts to find out which blog category is popular. You can find the result easily using the SUMIF function.
You may apply the same formula to other categories you have assigned on your spreadsheet. With the SUMIF function, you don’t have to make extra calculations to determine which blog post category is the most viewed.
6. Search Volume: Query Function and IMPORTRange
The Query function can be considered a more powerful function than VLOOKUP. With the query function, you can query data using SQL. This makes data retrieval easy, and you can expect high turn-around results.
The formula for the query function is =QUERY(range,sql_query).
It is possible to incorporate the IMPORTRANGE formula with the query function allowing you to query data from other spreadsheets.
7. Improving Engagement Levels and Conversation Rate Using Google Analytics Add On
This advanced Excel formula allows you to gain valuable insights into opportunities that can boost engagement levels and conversion rates on your ranking pages. To maximize this feature, you will need to add the Google Analytics add-on to your Google Sheets account.
After doing so, you will need to perform the Concatenate function to generate the complete URL of each page. Then, you will need to use the VLOOKUP function to include key metrics in your spreadsheet such as Users, bounce rate, etc.
8. Setting Default for Errors Using the IFError Function
It is possible that you can see an error message on your spreadsheet like #ERROR!, #DIV/0!, or #VALUE! when encoding formulas. When you use an unknown range in your formula or divide a cell by zero, these error prompts appear.
These error prompts can make your spreadsheets look cluttered and confusing. You can remedy this problem by using the IFError function. The IFError function replaces the error values with the default values you initially set.
Conclusion
You can use several formulas in Google Sheets to make your SEO work much easier. Whether you know basic or advanced formulas, Google Sheets is an excellent platform for basic SEO tasks and keyword research.
These eight Google Sheets functions every SEO should know are highly useful for your next spreadsheet project. Keep practicing, and you will eventually find yourself enjoying your spreadsheet experience. You can bookmark this page for your future reference.
