Some Useful Excel Templates
Compare two Lists
Sometimes, it may be necessary to compare data between two columns in an Excel spreadsheet. It is particularly important, when analysing backlinks for example, to de-duplicate lists of data into some kind of useful list of domains that is easier to analyse. For instance, if you have two columns of different lengths filled with URL names, you may want to compare these two lists to select all URLs that are common to both lists A and B, values that are in column A but not in column B, and values in column B but not in column A. Knowing how to compare data in Excel is useful for record keeping and list management. For example, comparisons can be used to make sure the same data is not entered twice, or to check that the correct information has been entered for a record. To achieve this, it is possible to employ a variety of methods: use a formula, or write a Visual Basic Macro. Here, I will provide a simple template that uses the former method. The template can be downloaded here. Columns A and B in the spreadsheet correspond to the two lists that have to be compared. Column C displays all values that are common to both columns A and B, column D shows values that exist in column A only, and column E shows values that exist in column B only.
Extracting Domain Names from URLs
Search Engine Optimization (SEO) frequently entails dealing with long lists of URLs. One of the best ways to manage this is with Excel. Sometimes it may be necessary to parse a list of URLs in order to obtain the domain names. This can be quite time-consuming if attempted manually, but with a few Excel formulas it is possible to extract the domain name from a long URL. For example, if you have a list of URLs like this:
it is possible to reduce this list and so that it contains only the domain portions of the URLs as shown below:
An example of how this method can be used is with competitor backlink analysis to quickly identify all of the unique domains that link to particular websites. There are many other potential applications for domain name extraction in areas such as search engine optimization (SEO), publicity, online marketing, analytics and web development, where reporting and comparing data is essential. An Excel template that can perform this task can be downloaded here.
Create a list of 100 random domain names
Sometimes in SEO analysis, it helps to create a list of domains with no obvious correlation to the research you are working on. Using domain lists from the Internet tend to create lists that have a bias in relation to the research. This template is one that uses 100 randomly generated words and then connects random TLD suffixes to them to create a list of domain names which are likely to exist, but with no preconceived notion of quality or context.