Google Sheets Formulas Every SEO Should Know

Nine Google Sheets formulas cover most of the repetitive SEO work that otherwise eats an afternoon: pulling data off pages, matching two keyword lists, cleaning up URLs, and building reports that update themselves. None of these require a paid tool or a script. They’re built into Sheets already, most SEOs just never learned past VLOOKUP.

This isn’t a “50 formulas” list padded out with functions nobody actually reaches for. These are the ones that show up constantly in real audit workbooks, keyword mapping sheets, and monthly reports, the ones worth actually memorising the syntax for.

1. IMPORTXML: pull data straight off a live page

IMPORTXML fetches a URL and extracts specific elements using an XPath query, directly into a cell. Pull a page’s title tag with =IMPORTXML(A2,"//title"), or every H1 on the page with =IMPORTXML(A2,"//h1"). It’s genuinely useful for spot-checking a batch of URLs, competitor title tags, meta descriptions, canonical tags, without opening each page manually.

The catch: it’s slow, it doesn’t handle JavaScript-rendered content, and Google throttles it if you hammer too many requests at once. Use it for sampling twenty or thirty pages, not crawling a whole site. For anything past that scale, a real crawler does the job faster and more reliably.

2. VLOOKUP: match one list against another

The formula every spreadsheet user learns first, and still one of the most used in SEO work. =VLOOKUP(A2, RankingsSheet!A:C, 3, FALSE) looks up the value in A2 against a range on another sheet and pulls back a matching value. Use it to match a keyword list against ranking data, match URLs against their traffic numbers, or pull metrics from an export into your working sheet without manual copy-paste.

One habit worth building early: always use FALSE (or 0) for the last argument unless you specifically need an approximate match. Leaving it blank or TRUE causes VLOOKUP to silently return wrong data when your list isn’t sorted, which is a genuinely common source of quietly incorrect reports.

3. REGEXEXTRACT: pull a specific piece out of messy text

REGEXEXTRACT grabs the first substring matching a regular expression. =REGEXEXTRACT(A2,"\d+") pulls the first number out of a text string, useful for extracting a page number, a product ID, or a numeric value buried in a longer string. =REGEXEXTRACT(A2,"/([^/]+)/$") pulls the last URL slug out of a full path, handy when a report gives you full URLs but you need to match on slug alone.

The Google Sheets SEO formula reference

Nine Formulas Worth Memorising

• IMPORTXML. Pulls specific elements off a live page using an XPath query.
• VLOOKUP. Matches a value against another list and returns a related field.
• REGEXEXTRACT. Pulls a matching substring out of messy text.
• REGEXMATCH. Returns TRUE or FALSE for whether a pattern exists in a string.
• QUERY. Runs SQL-style filtering and aggregation inside a single formula.
• ARRAYFORMULA. Applies one formula down an entire column automatically.
• IFERROR. Replaces a formula error with a clean fallback value.
• SPLIT. Breaks one cell of text into separate columns by a delimiter.
• UNIQUE. Returns the distinct values from a list, dropping duplicates.

4. REGEXMATCH: filter rows by pattern, not exact text

REGEXMATCH returns TRUE or FALSE depending on whether a pattern exists somewhere in the text, which makes it perfect for filters and conditional formatting rather than data extraction. =REGEXMATCH(A2,"category|tag") flags any URL containing either word, useful for quickly separating category and tag archive pages from the rest of a crawl export before you decide what to do with them.

5. QUERY: build a live report instead of a static filter

QUERY runs SQL-like syntax directly inside a cell. =QUERY(A:D,"SELECT A, SUM(C) WHERE B = 'Blog' GROUP BY A ORDER BY SUM(C) DESC",1) groups and sorts data in one formula, updating automatically as the source range changes. This is the formula that turns a static monthly export into a report that rebuilds itself the moment you paste in fresh Search Console or GA4 data, instead of manually re-filtering every month.

6. ARRAYFORMULA: stop dragging formulas down 500 rows

Wrapping a formula in ARRAYFORMULA applies it across an entire range at once instead of needing to be dragged or copy-pasted down every row. =ARRAYFORMULA(IF(A2:A500="","",LEN(A2:A500))) checks the length of every title tag in a column in one shot, useful for flagging title tags that run long. It’s a small habit change that saves real time on any sheet with more than a hundred rows.

7. IFERROR: stop errors from breaking downstream formulas

Wrap anything that might fail, a VLOOKUP with no match, a REGEXEXTRACT with nothing to extract, in IFERROR so it returns something clean instead of a red #N/A that then breaks every formula referencing that cell. =IFERROR(VLOOKUP(A2,Data!A:B,2,FALSE),"Not found") is the standard pattern. This matters more than it sounds like it should: one unhandled error in a shared reporting sheet can cascade into a dozen broken cells downstream, and a client seeing red error cells in a report undermines confidence fast, regardless of how good the underlying analysis is.

8. SPLIT: break a URL or a string into usable pieces

SPLIT divides one cell into multiple columns based on a delimiter. =SPLIT(A2,"/") breaks a full URL path into its individual segments, useful for analysing site structure by folder depth without writing a script. Combine it with REGEXEXTRACT when the delimiter isn’t consistent enough for a straight split.

9. UNIQUE: deduplicate a list without a manual pass

UNIQUE returns the distinct values from a range, dropping duplicates automatically. =UNIQUE(A2:A1000) is the fastest way to get a clean list of unique URLs, unique keywords, or unique referring domains out of a raw export before doing anything else with it. It updates live too, so a growing export doesn’t need re-deduplicating by hand every time new rows get added.

How do these actually fit together in a real workflow?

Rarely in isolation. A typical content audit sheet chains several of these: UNIQUE to get a clean URL list from a raw crawl export, ARRAYFORMULA plus REGEXEXTRACT to pull the URL slug from every row at once, VLOOKUP wrapped in IFERROR to match each URL against its traffic and ranking data, then QUERY at the bottom to summarise the whole thing by folder or content type. None of these formulas is impressive alone. Chained together, they replace what would otherwise be an hour of manual cross-referencing with a sheet that updates itself every time you paste in fresh data.

The habit that actually saves time long-term isn’t memorising every formula in this list, it’s building two or three of these into a reusable template sheet once, so every future audit starts from a working structure instead of a blank spreadsheet.

What breaks most often when you’re copying these across a sheet?

Absolute versus relative references, almost every time. Drag a VLOOKUP down five hundred rows without locking the lookup range with dollar signs, Data!$A$2:$B$1000 instead of Data!A2:B1000, and row 500’s formula is searching a range that’s shifted 498 rows away from where your actual data lives. It still returns a result some of the time, which is worse than an obvious error, because a wrong-but-plausible number is much easier to publish in a report without noticing.

The second most common failure is a data type mismatch nobody checks for: a column of numbers that’s secretly stored as text (common after pasting from a CSV export) makes SUM, QUERY and sorting all misbehave silently. If a formula that should work is returning zero or an empty result for no obvious reason, check whether the source column is actually formatted as a number before assuming the formula itself is wrong.

Is it worth learning Apps Script instead of stacking formulas?

Eventually, yes, for specific recurring tasks, but not as a replacement for these. Apps Script is worth reaching for when the same multi-step process runs weekly or monthly and would benefit from full automation, pulling a Search Console export and reformatting it every Monday morning, for instance. For one-off analysis, exploring a fresh dataset, building a client audit you’ll only run once, formulas are faster to write and easier for anyone else on the team to understand and modify later without reading code. Most of our own workflows use both: formulas for the thinking, Apps Script for the parts that just need to repeat on a schedule without anyone touching them.

Frequently asked questions

Is IMPORTXML reliable for large-scale scraping?

No, and it’s not meant to be. IMPORTXML times out on large batches, breaks on JavaScript-rendered content, and Google throttles heavy use. It’s genuinely useful for pulling data from twenty or thirty URLs at a time, not for crawling a 10,000-page site, that’s what a dedicated crawler like Screaming Frog is for.

Why does REGEXMATCH return TRUE or FALSE instead of the matched text?

Because that’s specifically what it’s built for: checking whether a pattern exists, for filters and conditional formatting. If you want the actual matched substring, REGEXEXTRACT is the right function. They solve two different problems that happen to both use regular expressions.

Do I need to know regular expressions to use these formulas well?

Basic regex helps a lot, but you don’t need to be fluent. A handful of patterns, matching digits, matching everything after a slash, matching a specific word, cover most SEO spreadsheet work. Learn those five or six patterns properly rather than trying to memorise regex broadly.

Why use QUERY instead of just filtering the data manually?

Because QUERY updates automatically when the source data changes, where a manual filter has to be reapplied every time. For a report you’re rebuilding monthly from fresh Search Console exports, a QUERY formula turns a fifteen-minute manual task into something that updates itself the moment you paste in new data.

Sources

• REGEXEXTRACT function, Google Docs Editors Help
• IMPORTXML function, Google Docs Editors Help
• Google Search Console: The Reports That Actually Matter
• Building an SEO Audit Workbook in Google Sheets
• Automating Reports With Google Sheets and Apps Script
• Regex for SEOs: Search Console, GA4 and Screaming Frog