Skip to main content

Google sheet formula for SEO

Source Metadata​

  • Tested: No
  • Category: SEO
  • Created time: February 6, 2025 3:35 PM

list me google sheet formula that help for seo. elaborate please.

Heres a comprehensive guide on Google Sheets formulas for SEO, including input formulas, expected output, and detailed use cases.

Table of Contents

  1. Extracting Information from URLs
    • Extract Domain Name
    • Extract Subdomains
    • Extract Page Paths
    • Check HTTP vs. HTTPS
  2. Content Analysis
    • Count Words in a Cell
    • Check Title & Meta Description Length
    • Identify Missing Meta Descriptions
    • Detect Duplicate Content
  3. Link Analysis
    • Identify Internal vs External Links
    • Find Broken Links
    • Check Redirected URLs
  4. SEO Data Extraction (Web Scraping)
    • Scrape Meta Title
    • Scrape Meta Description
    • Extract H1 Tag
    • Get Word Count of an Article
  5. Keyword & Ranking Analysis
    • Check Keyword Presence in Titles
    • Track Search Rankings from Google Search Console
    • Generate SEO-Friendly URL Slugs
  6. Technical SEO
    • Find Mobile-Friendliness of a Page
    • Generate XML Sitemaps

1. Extracting Information from URLs

1.1 Extract Domain Name​

Formula​

=REGEXEXTRACT(A2, "^(?:https?:\/\/)?(?:www\.)?([^\/]+)")

Input​

A (URL)

https://www.example.com/page

http://sub.example.org/path

Output​

B (Extracted Domain)

example.com

sub.example.org

Use Case​

This formula is useful for backlink analysis, competitor research, and cleaning up datasets.

1.2 Extract Subdomains​

Formula​

=REGEXEXTRACT(A2, "^(?:https?:\/\/)?([^\/]+)")

Output​

A (URL)B (Extracted Subdomain)
https://blog.example.com/pageblog.example.com

Use Case​

It helps differentiate between main domains and subdomains, which is crucial in technical SEO.

1.3 Extract Page Path (Remove Domain Name)​

Formula​

=REGEXEXTRACT(A2, "https?:\/\/[^\/]+(\/.*)")

Output​

A (URL)B (Extracted Path)
https://example.com/blog/post/blog/post

Use Case​

Useful for analyzing site structure and categorizing content.

1.4 Check HTTP vs HTTPS​

Formula​

=IF(LEFT(A2,5)="https", "Secure (HTTPS)", "Not Secure (HTTP)")

Output​

A (URL)B (Security Status)
https://example.comSecure (HTTPS)
http://example.comNot Secure (HTTP)

Use Case​

Ensures that all URLs use HTTPS, which is an important SEO ranking factor.

2. Content Analysis

2.1 Count Words in a Cell​

Formula​

=IF(A2<>"", COUNTA(SPLIT(A2, " ")), 0)

Output​

A (Meta Description)B (Word Count)
SEO tips and tricks for beginners6

Use Case​

Checks if meta descriptions and titles meet recommended length guidelines.

2.2 Check Title & Meta Description Length​

Formula​

=LEN(A2)

Output​

A (Meta Description)B (Character Count)
Learn SEO Basics17

Use Case​

Ensures that titles (50-60 characters) and meta descriptions (150-160 characters) are not truncated.

2.3 Identify Missing Meta Descriptions​

Formula​

=IF(A2="", "Missing", "OK")

Output​

A (Meta Description)B (Status)
(empty)Missing
Learn SEO BasicsOK

Use Case​

Useful for SEO audits to check for missing meta descriptions.

3. Link Analysis

Formula​

=IF(ISNUMBER(SEARCH("example.com", A2)), "Internal", "External")

Output​

A (URL)B (Link Type)
https://example.com/pageInternal
https://other.com/blogExternal

Use Case​

Helps in link-building strategy by differentiating internal vs external links.

Formula​

=IF(A2<>"", IFERROR(IMPORTXML(A2, "//title"), "Broken Link"), "No URL")

Output​

A (URL)B (Status)
https://example.comOK
https://deadlink.comBroken Link

Use Case​

Finds 404 errors in SEO audits.

4. SEO Data Extraction (Web Scraping)

4.1 Scrape Meta Title​

Formula​

=IMPORTXML(A2, "//title")

Use Case​

Extracts meta titles from competitor sites.

4.2 Scrape Meta Description​

Formula​

=IMPORTXML(A2, "//meta[@name='description']/@content")

Use Case​

Analyzes meta descriptions of top-ranking pages.

5. Keyword & Ranking Analysis

5.1 Check Keyword Presence in Titles​

Formula​

=IF(ISNUMBER(SEARCH(B2, A2)), "Yes", "No")

Output​

A (Title)B (Keyword)C (Keyword Present)
Learn SEO BasicsSEOYes

Use Case​

Checks if important keywords appear in SEO titles.

6. Technical SEO

6.1 Generate XML Sitemaps​

Formula​

="<?xml version='1.0' encoding='UTF-8'?>"
& "<urlset xmlns='http://www.sitemaps.org/schemas/sitemap/0.9'>"
& JOIN("", ARRAYFORMULA("<url><loc>" & A2:A & "</loc></url>"))
& "</urlset>"

Use Case​

Automatically generates XML sitemaps for Google Search Console.

Conclusion​

These Google Sheets formulas help automate SEO tasks, analyze content, track keyword rankings, and audit technical SEO issues efficiently.

Would you like me to create a Google Sheets template with these formulas pre-configured?