Last updated on February 17th, 2022

Excel For Search Engine Optimization

Mastering Excel helps SEO Professionals save time and improve work productivity. Some Inbuilt Excel Functions would be sufficient in auditing your site easily, which can save both time and money.

Why you should learn Excel for SEO

  • To Enhance Your Work Productivity.
  • Help in building a successful SEO campaign
  • Competitive Analysis
  • Helps in Processing Large amounts of data from Google Analytics and Search Console to get meaningful insights that help in creating an effective SEO strategy.
  • Saves time and Money (No need to pay for online or Desktop SEO Site auditing applications.

This Guide will help SEO Beginners how Excel can be used for SEO with examples.

Google Sheets for On-Page Optimization:

Google sheets help SEOs in finding the missing meta tags of all the web pages at once. 

  • Formula to find the title of the web pages using google sheets.
=IMPORTXML("Replace with your Page URL" , "//title")
  • Formula to scrape all links on a web page at once.
=IMPORTXML("Replace with your Page URL", "//@href")
  • Formula to find the meta descriptions on web pages and their description length.
=IMPORTXML(A2, "//meta[@name='description']/@content")

then

=len(A3) to find the length of scraped description.
  • Formula to find the H1 on web pages and their length.
=IMPORTXML(A2, "//h1")

then

=len(A3) to find the length of H1
  • Formula to scrape external links from web pages
=IMPORTXML(A2,"//a[not(contains(@href, 'webmarketersguide.com'))]/@href")
  • Formula to scrape social profiles
=IMPORTXML(A2,"//a[contains(@href, 'linkedin.com/in') or contains(@href, 'twitter.com/') or contains(@href, 'facebook.com/')]/@href")
  • Formula to scrape Email Addresses
=IMPORTXML(A2,"//a[contains(@href, 'mailTo:') or contains(@href, 'mailto:')]/@href")
  • RSS feed URL to scrape “lastBuildDate” from
=IMPORTXML(A2, "//lastBuildDate")
  • Formula to extract different URLs from a list of different URLs.
=REGEXEXTRACT(A1,"^(?:https?:\/\/)?(?:[^@\n][email protected])?(?:www\.)?([^:\/\n]+)")
  • To SPLIT strings into multiple data points

Syntax: =SPLIT(text, delimiter)

=split(A1, ".")
  • Use IMPORTRANGE to Import data from other spreadsheets
=IMPORTRANGE("SPREADSHEET_KEY", "'SheetName'!A2:A")
  • To QUERY data sets using SQL queries 

Syntax: =QUERY(range, sql_query)

=QUERY(DATA!A:B,"select A where B = 'Blog'")
  • Formula to check the canonicalization of a page
=importXML(A1, “//link[@rel=’canonical’]/@href”)
  • Formula to check the meta robots index or no-index of a page
=importXML(A1, “//meta[@name=’robots’]/@content”)
  • Formula to check the language attribute of a page
=importXML(A1, “//link[@rel=’alternate’]/@hreflang”)
  • Easy Google Sheets Formulas to Format Text:

text= Sample text

  • LOWER – change all the characters to lowercase
=LOWER("text") = sample text
  • UPPER – change all the characters to uppercase
=UPPER("text") = SAMPLE TEXT
  • PROPER – Capitalize the first character of each word
=PROPER("text") = Sample Text
  • TRIM – Remove extra(unnecessary spaces within the selected text
=TRIM("text")= sample text
  • LEN – length of a sentence or content
=Len("text") = 10
  • CONCATENATION – To join to strings or values
=concat("seo" + "tools") = seo tools
  • COUNT – To find the count of cells with numbers in them
=Count("range of values")
  • COUNTA  To find the count of cells with numbers and texts in them i.e to find the number of non-empty cells.
=CountA("range of values")
  • COUNTIF – To Count the number of the cells with specific conditions
=COUNTIF("range of Values", "condition to be met")
  • IF – Only do something when specific criteria are met
  • SUM – To find the sum of all the values in a column or a specified range
  • Vlookup – Find data for a value, within a range
  • SUMIF– to add together the values in a specific range based on one specific criterion.
  • SUMIFS– to add together the values in a specific range based on multiple criteria.
  • UNIQUE – to get a list of unique values from a selected list or range.
  • SUBSTITUTE – to replace any character or word of a cell value with another character.
  • SEARCH / ISNUMBER to get the position of a substring within a string. ISNUMBER provides TRUE or FALSE depending on whether the cell is a number.
  • LEFT – to extract a substring from a string starting from the left of a string of text.
=LEFT("Text", 5)

Use of Combination of LEFT and Search will extract anything before or after specified delimiters like (/, .) etc. 

=LEFT(A2, SEARCH(" / ", A2) -1)
  • RIGHT – to extract a substring from a string starting from the right of a string of text.
=RIGHT("Text", 6) 

Author

Pandith,
Is the creative head of the Web Marketers Guide (WMG) and an Experienced Digital Marketing Analyst. Worked as a marketing professional for various start ups and Immigration Industries.

Reading time: 4 minutes