EXCEL TIPS FOR SEO

Microsoft Excel plays a pivotal role in Search Engine Optimisation (SEO) & other fields of digital marketing like Paid Marketing, Programmatic and data analysis due to its versatile and powerful capabilities.

For SEOs, Excel serves as a valuable tool for organizing and managing data from various sources, such as Rank Trackers, Crawling & Backlink tools, Google Search Console & Website Analytics.

In this blog, we have listed most commonly used excel tips in SEO & please add in comments if you have additional ideas or need any help.

Let’s jump into it –

Tip 1) How to check duplicate URLs in a excel sheet or workbook.

How to use – From the Home Menu >Highlight Cell Rules>Conditional Formatting >Duplicate Values

Path in Excel

Tip 2) How to check Duplicate URLs in a another sheet or different tab

Formula – vLookup

How to use vLookup

=VLOOKUP(A2,Destination!A:A,1,FALSE)

In this example, A2 is URL value that you want to find. 

The second argument is the range of cells, A:A under another tab Destination, in which to search for the value you want to find. In this case, we are essentially asking to search the full column A under the Destination tab. 

The third argument is the column in that range of cells that contains the value that you seek.

The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument.

In other words, leaving the fourth argument blank—or entering TRUE—gives you more flexibility.

For example, in above case we are looking to match if URL’s from ‘source’ tab also exist in ‘destination’ tab

 Apply vLookup in the tab where you want to compare the list of 2 URL’s


How to compare url strings in excel & find near similar duplicates.

For example- In Microsoft excel, we have a list of URL’s are near similar

URL 1 – www.google.com/image/56?w=500
URL 2 – www.google.com/image/50?w=500

Solution – To compare URLs for near similarity or exact duplication in Microsoft Excel, you can use the following approach:
  1. Open your Excel file and locate the column that contains the URLs you want to compare. Let’s assume the URLs are in column A, starting from cell A2.
  2. Insert a new column next to the URLs column. For example, you can use column B.
  3. In cell B2, enter the following formula to extract the base URL from the given URL:
   =LEFT(A2, FIND("?", A2)-1)

This formula extracts the portion of the URL before the “?” character. It will give you the base URL without any query parameters.

  1. Drag the formula down to apply it to all the URLs in column A.
  2. In the next available column (e.g., column C), enter the following formula to check for exact duplicates:
   =IF(COUNTIF($B$2:$B$N, B2)>1, "Duplicate", "")

Replace “N” with the last row number where your URLs end. This formula compares the base URL (from column B) with all the other base URLs in the range B2:B(N). If a duplicate is found, it marks the corresponding cell with “Duplicate”.

  1. Drag the formula down to apply it to all the URLs in column B.
  2. To check for near similarity, you can use Excel’s conditional formatting feature. Select the range of URLs in column A (A2:A(N)), go to the “Home” tab, and choose “Conditional Formatting” > “New Rule”.
  3. In the New Formatting Rule dialog, select “Use a formula to determine which cells to format”.
  4. In the formula field, enter the following formula:
   =COUNTIF($B$2:$B$N, LEFT(A2, FIND("?", A2)-1))>1

This formula checks if the base URL (without query parameters) exists more than once in column B. If it does, it applies the specified formatting.

  1. Choose the desired formatting style for the near similar URLs and click “OK”.

With these steps, you’ll be able to identify exact duplicates in column C and highlight near similar URLs in column A using conditional formatting. Adjust the formulas and ranges as per your specific Excel file setup.


Tip 3) How to add a URL string at the start

For example, sometimes when URL’s downloaded from Google Analytics sometimes don’t have the domain as prefix.


There are 2 ways you can add prefix URL


Solution Type 1 – Use Concatenate Formula


How to use – In the column where you wish to insert URL at the start/prefix, enter formula =CONCATENATE(“www.google.com”,A2) & press enter


Solution Type 2 – Add/prefix URL or Text using Ampersand Operator (&)


How to use – In the column, =”www.google.com”&A2 


Most interestingly, The & operator can also be used to add text in the beginning or end of many cells as shown later in few examples. 


Tip 4) How to check if URL contains lower case vs upper case. In other words, how to find capital letters in a string in excel

Lowercase URL’s vs Uppercase URL’s can sometimes cause issues such as canonicalization issues or incorrect tracking in Website analytics. Inconsistent URL pattern could happen due to uppercase or lowercase. 


For example, few URL’s are upper cases while other URL’s are resolving to lowercase letters, like in this below image /CAT4


So, how to check if your URL list has both uppercase and lowercase URL’s for the same version.

Solution Formula  – EXACT 

How to use – In the next column type, =EXACT(A2,LOWER(A2))


This compares available value in A2 to the lowercase version and returns 

TRUE – if all characters are in lower case

FALSE – Even if a single character is in lower case

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. 



Tip 5)  How to add text at the end of a text string in excel 

For instance, if you have to add tracking or UTM Parameters in the URL’s or insert something at the end of the URL.

Formula – & Operator (like we discuss above which can also be used to insert text at the start)

How to use – Let’s say the task requires following parameters to be added in the URLs

utm_source=google_business_profile&utm_medium=organic&utm_campaign=Fox_Sports

In the column where you wish to create the new URL, type =A2&”UTM_STRING”

How to use screenshot


The & operator can also be used to add text in the beginning or end of many cells. 


Summary
Overall, Microsoft Excel’s versatility, user-friendly interface, and powerful analytical functionalities make it an indispensable tool for SEO’s & digital marketers and data analysts, enabling them to effectively harness and derive valuable insights from the vast amount of data available in today’s digital landscape. 

What is your favourite trick or if you are struggling with a problem, let us know in the comments section? 

Leave a Reply

Your email address will not be published. Required fields are marked *