How to Check Keyword Ranking in Google Sheets with Formulas
“Quick Overview” Keeping track of your website’s keyword rankings is an essential aspect of Search Engine Optimization (SEO). It allows you to monitor your website’s performance on search engine result pages and make informed decisions to improve your online visibility. In this blog, we’ll walk you through a step-by-step process to check keyword rank in Google Sheets using a simple powerful formula.
Let’s dive in!
Understanding the Formula
Before we proceed with the step-by-step guide, let’s break down the formula to understand how it works:
=CONCATENATE(“https://www.google.com.au/search?q=“, LOWER(SUBSTITUTE(TRIM(A2), ” “, “+”)), “&near=melbourne”)
This formula combines different functions to create a Google search URL that includes your target keyword and location.
Step 1: Set Up Your Google Sheet
To get started, open a new or existing Google Sheet where you want to track keyword rankings. If you don’t have one yet, create a new sheet and give it an appropriate title.
Step 2: Enter Keywords in Column A
In Column A of your Google Sheet, list the keywords for which you want to check the rankings. Enter each keyword in a separate cell starting from cell A2 (leave cell A1 blank for the title or any other information you may need).
领英推荐
Step 3: Enter the Formula
In an empty cell (for example, cell B2), enter the formula:
=CONCATENATE(“https://www.google.com.au/search?q=”,LOWER(SUBSTITUTE(TRIM(A2),” “,”+”)),”&near=melbourne”)
This formula takes the keyword from cell A2, formats it correctly, and creates a Google search URL. The “&near=melbourne” part of the formula sets the location to Melbourne. If you want to check rankings for a different location, replace “melbourne” with your desired location, or leave it out if you want a general search.
Step 4: Copy the Formula for Other Keywords
Once you have entered the formula in cell B2, you can use the fill handle (a small square at the bottom right corner of the cell) to copy the formula to other cells in Column B. Drag the fill handle down to apply the formula to all the keywords in Column A.
Step 5: Checking Keyword Rankings
As you copy the formula to other cells, each cell in Column B will generate a unique Google search URL based on the corresponding keyword in Column A. Now, click on any URL in Column B, and it will open a new tab displaying the Google search results for that keyword and location. The search results will reveal your website’s ranking for the specific keyword in the Google search engine.
Data-Driven Digital Marketing Strategist with more than a Decade and a Half Years of Experience in Social Media, SEO, SEM, Automation | 20 Million Organic Impressions in 1 one year
1 年This is a very different approach but when the market is flooded with so many tools...is it advisable to do it manually?
Great insights, Jinal Soni! Your article on checking keyword ranking in Google Sheets with formulas is really informative for digital marketers. It's a valuable skill to have in today's SEO landscape. Keep up the good work!