TL;DR
You can build a functional SEO keyword tracking spreadsheet in about two hours using Google Sheets and free data from Google Search Console. The finished tracker logs weekly rankings, flags position changes automatically, and gives you a single tab that shows which keywords are climbing and which ones need attention. You need no paid tools to get started, though Ahrefs or Semrush data can slot in later.
What You Need Before You Start
- a Google account with access to Google Sheets (free)
- Google Search Console verified for your domain — at least 30 days of data makes the baseline meaningful
- a list of 20-100 target keywords you already care about (a CSV from any rank tracker works, or pull them straight from Search Console)
- optional: an Ahrefs or Semrush account for keyword difficulty and volume data (free trials work fine for a one-time export)
- optional: Google Chrome with the Looker Studio extension if you want live charts later
- basic spreadsheet skills —
IF,VLOOKUP, and conditional formatting are the only functions you need to know
Step 1: Create Your Spreadsheet Skeleton
Open a blank Google Sheets file and rename it something like SEO Rank Tracker — [Domain] — 2026. Create three tabs at the bottom: Raw Data, Tracker, and Dashboard.
On the Tracker tab, set up these columns in row 1:
A: Keyword
B: URL (target page)
C: Keyword Group
D: Search Volume
E: KD (keyword difficulty)
F: Week 1 Position
G: Week 2 Position
H: Week 3 Position
I: Week 4 Position
J: Change (latest vs. Week 1)
K: Status
L: Notes
Bold row 1 and freeze it (View → Freeze → 1 row). Set column A width to 280px so long-tail keywords don’t get cut off.
You should now see a clean header row with all your tracking columns visible without scrolling horizontally.
Step 2: Pull Keyword Data From Google Search Console
Go to Search Console, select your property, and open Performance → Search results. Set the date range to the last 28 days. Click the Queries tab, then export to CSV (the download icon in the top right).
Open the CSV. You want three columns: query, clicks, and impressions. Paste these into your Raw Data tab starting at A1. Rename the headers to keyword, clicks, and impressions.
Back on your Tracker tab, paste your keyword list into column A starting at A2. Use VLOOKUP to pull search volume estimates from the Raw Data tab as a proxy for demand:
=IFERROR(VLOOKUP(A2,'Raw Data'!A:B,2,0),"–")
This is only an approximation — Search Console clicks are not search volume — but it tells you which keywords are actually driving traffic right now versus theoretical targets. You should now see click counts populating column D for any keyword that appeared in Search Console.
Step 3: Add Your Baseline Position Data
Your first weekly position column (F) is your baseline. If you pulled ranking data from Ahrefs, Semrush, or a tool like Mangools, export the position report and paste positions into column F manually, matching each keyword row.
If you have no rank tool yet, use Search Console’s average position as a starting point. Add a helper column to Raw Data that maps keyword to average position:
=IFERROR(VLOOKUP(A2,'Raw Data'!A:C,3,0),"–")
Note that Search Console average position is an aggregate across all queries containing your keyword, so it skews generous. Flag these rows in column L with a note like SC avg — not exact so future-you remembers the data source.
You should now see your Tracker tab with keywords, target URLs, and a Week 1 position column filled in.
Step 4: Build Your Keyword Grouping System
Column C (Keyword Group) is what separates a useful tracker from a noisy one. Without groups, you end up with 80 rows of disconnected keywords and no sense of which topic cluster is gaining ground.
Create a consistent tagging system based on your site structure. Common approaches:
- by funnel stage:
ToFu,MoFu,BoFu - by content type:
pillar,blog,product,local - by topic cluster:
analytics,reporting,dashboards, etc.
Use a dropdown validation to keep tagging consistent. Click column C, go to Data → Data validation → Dropdown (from a range), and point it to a small reference list you put somewhere in the Raw Data tab.
Grouping unlocks filtering. With groups in place, you can quickly filter to just your pillar content or just your bottom-funnel pages and see how those clusters are trending week over week.
You should now see every keyword tagged and the column C dropdown working correctly.
Step 5: Build the Rank Change Formula
Column J (Change) is the column you will check every Monday morning. It compares your most recent week’s position to the baseline in column F.
Assuming Week 4 is your most recent data in column I, use this formula:
=IF(AND(F2<>"–",I2<>"–"), F2-I2, "–")
Subtracting current from baseline means a positive number equals improvement (moved up in rankings) and a negative number means you dropped. That matches the mental model most SEOs use.
For column K (Status), use a nested IF to auto-label each keyword:
=IF(J2="–","no data",IF(J2>=5,"rising",IF(J2<=-5,"dropping",IF(J2>0,"improving","stable"))))
This gives you five clean labels: rising, dropping, improving, stable, no data. You can filter by these labels every week to prioritise which pages need attention.
You should now see column J showing numeric changes and column K showing plain-text status labels for every keyword with two weeks of data.
Step 6: Apply Conditional Formatting for Visual Signals
Select column K, go to Format → Conditional formatting, and add rules:
| Condition | Background | Text |
|---|---|---|
Text is exactly rising |
#d9ead3 (light green) |
dark green |
Text is exactly dropping |
#fce8e6 (light red) |
dark red |
Text is exactly improving |
#e8f5e9 |
default |
Text is exactly stable |
#f9f9f9 |
default |
Do the same for column J: use a custom formula =$J2>4 for green fill and =$J2<-4 for red fill.
Also apply a colour scale to columns F through I so darker shading represents higher rankings (lower position numbers). Select those columns, choose Format → Conditional formatting → Color scale, and set the min point to color #ffffff and max point to a medium blue.
You should now see a tracker where problem keywords are immediately obvious in red and winners stand out in green without reading a single number.
Step 7: Add Weekly Volume and Impressions Tracking
Clicks and impressions from Search Console tell you whether rankings are translating to actual traffic. Add two more columns after K:
M: Impressions (latest 28d)
N: Clicks (latest 28d)
Paste these from your monthly Search Console export. Update them every time you add a new position column.
To spot click-through rate problems, add column O:
=IF(AND(M2>0,N2>0), N2/M2, "–")
Format column O as percentage. A keyword sitting in position 3 with a 1% CTR probably has a title tag problem or is losing clicks to featured snippets. The CTR column surfaces that faster than any rank movement alone.
You should now see a tracker that connects ranking position to actual traffic signals in one view.
Step 8: Build a Summary Dashboard Tab
Switch to the Dashboard tab. This is where you get the one-screen view of your SEO health.
Add four summary boxes at the top using COUNTIF against the Tracker:
=COUNTIF(Tracker!K:K,"rising") — keywords rising
=COUNTIF(Tracker!K:K,"dropping") — keywords dropping
=COUNTIF(Tracker!K:K,"stable") — stable keywords
=COUNTA(Tracker!A:A)-1 — total keywords tracked
Below those, use FILTER to pull your top 10 rising keywords:
=SORT(FILTER(Tracker!A:J, Tracker!J:J>=5), 10, FALSE)
And your top 10 dropping keywords:
=SORT(FILTER(Tracker!A:J, Tracker!J:J<=-5), 10, TRUE)
This gives you an at-a-glance dashboard that tells you the whole story in under 30 seconds. You do not need to scroll 80 rows of data every Monday.
You should now see a Dashboard tab with live counts and filtered tables that update automatically when you add new weekly data.
Step 9: Set Up a Weekly Data Update Routine
The tracker is only useful if you actually update it. Build a repeatable routine that takes under 20 minutes each week.
Each Monday morning:
1. export a fresh position report from your rank tool (or a new Search Console CSV)
2. paste new positions into the next available week column (G, H, I, and so on)
3. update the J formula range to point at the newest column
4. update columns M and N with latest impressions and clicks
5. review the Dashboard tab for the top 10 risers and droppers
6. add a note in column L for any keyword with a notable change (new link built, page updated, etc.)
Keep the column naming consistent: label each week column with the Monday date (2026-05-18, 2026-05-25, etc.) so you can always trace a movement back to a specific week.
You should now have a living document rather than a static snapshot.
Common Mistakes To Avoid
- Tracking too many keywords from day one. A 500-row tracker with no grouping becomes unusable within two weeks. Start with 20-50 keywords that are genuinely tied to revenue or conversion goals.
- Mixing data sources in the same position column. Search Console average position and a rank tool’s exact position are not the same thing. If you pull from different sources in different weeks, your change column becomes meaningless.
- Forgetting to lock the baseline column. If you update column F after the tracker is running, you destroy all your historical change data. Once Week 1 is set, protect that column (right-click the column → Protect range).
- Ignoring CTR alongside position. A keyword that jumped from position 8 to position 4 but saw CTR drop is telling you something — a featured snippet or ad unit probably appeared above you. Position alone misses this.
- Not noting the cause of rank changes. A 10-position drop means nothing without context. Log page updates, link campaigns, and algorithm update dates in column L so you can connect movements to actions.
- Using a single-tab design. Dumping everything on one tab makes filtering and formula-writing painful. The three-tab structure (Raw Data, Tracker, Dashboard) keeps concerns separate and makes the sheet much faster to navigate.
When To Level Up
This spreadsheet approach works well up to around 100-200 keywords across a single domain. Past that threshold, the manual data paste every week becomes a real time cost and formula performance starts to lag.
The bigger signal that you have outgrown the spreadsheet is when you start tracking multiple domains, need daily rank updates instead of weekly ones, or want to share live reports with clients who should not have edit access to your raw data.
At that point, dedicated rank tracking tools like Ahrefs or Semrush handle the data collection automatically, store years of history cleanly, and let you build client-facing reports. Looker Studio connected to Search Console via a data connector is a free middle step if the budget is the constraint.
For a full breakdown of tools that replace this workflow at scale, browse the growth tools category where we compare rank trackers side by side. You might also want to read how to choose an SEO rank tracking tool for small teams before committing to a paid platform.
Frequently Asked Questions
How often should I update ranking data in my spreadsheet?
Weekly is the right cadence for most projects — search rankings shift gradually and daily noise creates false alarm signals. If you are running a time-sensitive campaign or recovering from a penalty, update twice a week and note the dates clearly.
Can I automate the data import into Google Sheets?
Yes. Search Console has a Google Sheets add-on called Search Analytics for Sheets that pulls query data directly into a tab on a schedule. For paid tools, Ahrefs and Semrush both offer API access, though connecting them to Sheets requires a short Python or Apps Script script.
What is a good number of keywords to start tracking?
Start with 30-50. Pick 10 high-priority commercial keywords, 10-15 informational keywords tied to your main content, and 5-10 branded or navigational queries. This gives you a balanced view without making the tracker unwieldy.
My rankings look different in Search Console versus my rank tool. Which one is right?
Both are right, just measuring different things. Search Console shows average position across all users and devices for all queries that triggered your page. Rank tools check a single, specific query from a fixed location and device. Use rank tool data for tracking exact keyword positions and Search Console data for traffic and CTR analysis.
How do I handle keywords where my site does not rank yet?
Enter them in the tracker with – in the position columns and tag them no data in the status column. Review them quarterly. If they still show no data after six months and you have published content targeting them, that is a signal to review the content or build more supporting links.
Bottom Line
Building a keyword tracking spreadsheet comes down to four things: a clean column structure, consistent data sources, formulas that surface rank changes automatically, and a weekly update habit. Start with your top 50 keywords, pull baseline positions from Search Console or a rank tool, and use the change and status columns to focus your attention on what is actually moving. The conditional formatting and dashboard tab turn 80 rows of numbers into a decision-making tool you can act on in five minutes each week. When the manual process starts costing more time than it saves, that is the natural moment to invest in a purpose-built platform. Until then, this spreadsheet does the job. For more tools and workflows that complement this setup, visit the growth category.