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]+@)?(?: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.