SEO and data analysis: a match made in heaven, right?
A spreadsheet is how you make sense of all that SEO data. From automation to tracking your rankings, a spreadsheet bridges the gap between working with data and taking action.
Of all the SEO tools you use daily, none is more powerful and versatile than a spreadsheet — and you’re about to find out why.
What’s a Spreadsheet?
For all you newbies out there, a spreadsheet is like an organized collection of data in a table format.
It’s a simple computer program or web application that allows you to input, display, and manipulate data in rows and columns. You can create your own spreadsheet from scratch or download premade templates.
Using a spreadsheet, you can easily add, delete and manipulate data quickly and accurately. You can also create formulas to calculate results automatically and save time.
3 Examples of Spreadsheet Programs and Applications
- Microsoft Excel: This is a popular spreadsheet program used by professionals. It comes with Microsoft Office and offers powerful features and a graphical user interface.
- Google Sheets: This is the free version of Excel offered by Google. It has most of the same features as Excel but with limited functionality. It can be accessed online via your Google account.
- Apache OpenOffice Calc: This is an open-source program similar to Excel, with a few additional features. It’s available for Macs, Windows, and Linux systems.
- iWork Numbers: This spreadsheet program is offered by Apple. It’s simple and intuitive to use, with a user-friendly interface. It comes with Apple Office Suite.
In other words, get Microsoft Office if you’re looking for a spreadsheet program to install on your Windows machine. If you’re on a Mac, get iWork Numbers. For Linux users, try Apache OpenOffice Calc. And finally, if you’re looking for a free online spreadsheet program, Google Sheets will do the job.
7 Key Components of a Spreadsheet
Now that you know the three most popular spreadsheet programs, let’s talk about the seven key components of a spreadsheet.
- A Cell: This is the basic unit of a spreadsheet. It’s like a tiny box that holds data.
- A Row: Cells aligned in a horizontal line make up a row. They’re numbered 1, 2, 3, 4…
- A Column: Cells aligned in a vertical line make up a column. They’re labelled A, B, C, D…
- Formulas: These are the instructions, arithmetic operations, or functions you use to manipulate data in a spreadsheet.
A few rules apply when writing spreadsheet formulas:
They must begin with an equal sign (=).
They must use the correct syntax.
Arguments (values used in a formula) must be separated by commas
It can contain any combination of numbers, operators, ranges, functions, cell references, and other formulas.
Example: = SUM (A1: A10)
- Range: This is a group of two or more cells. It specifies the beginning and ending cells of the range you want to work with.
Example: A1: A10 – This range includes all cells between rows 1 and 10 in column A.
A colon separates the start and end of the range.
Note that you can assign a RangeName to a range in Excel. That makes working with it easier as you don’t have to type the whole range whenever you want to refer to it.
- Data Validation: This feature allows you to limit what data can be entered into cells. For example, if your cell contains a list of countries, the data validation will only allow users to select from that list.
- Charts and Graphs: Spreadsheets are great for visualizing data through charts and graphs. You can create bar, line, pie, or scatter charts to show trends or patterns in your data.
7 Commonly Used Functions in a Spreadsheet
- AutoSum: This is a quick way to add all the numbers in a range.
Examples: =SUM (A1: A10)
It doesn’t necessarily have to sum up. You can also manipulate the formula to average, count, or find the maximum or minimum value in a range of cells.
IF Function
This function performs specific actions depending on whether certain criteria are met.
Examples: =IF (A1>10, “Yes”, “No”) – This formula will display Yes if the number in cell A1 is greater than 15 and No if it’s not.
Date Function
This function is used to manipulate dates.
Here are common date formulas you should know:
- TODAY: This function displays the current date.
Examples: =TODAY ()
- NOW: This function displays the current time, including seconds.
Examples: =NOW ()
Days Function
This function helps you add or subtract the number of days from a given date.
Examples: =DAYS (e2, d2) – This formula will calculate the number of days between two dates in cells e1 and d2.
Concatenate Function
This function combines two or more values into one string of text.
Examples: =CONCATENATE (g2, h2, i2) – This formula will combine the contents of cells A1 and A2 into a single string value.
Replace Function
This function replaces one or more characters in a string with another character.
Examples: =REPLACE (A1, 1, 2, “X”) – This formula will replace the first two characters of the string in cell A1 with an X.
Note that the first character in a string is always numbered 1.
So, if you have a string ABCDE in cell A1, the characters are numbered 1 to 5.
A will be character 1, B will be character 2, C will be character 3, and so on.
To replace ABCD with !, the formula will be: =REPLACE (A1, 1, 4, “!”)
Translating it into plain English: “replace characters 1 to 4 of cell A1 with an exclamation mark”.
Hour, Minute, Second
These functions extract the hour, minute, and second from a given time.
Examples:
=HOUR (L2)
=MINUTE (L2)
=SECOND(L2)
DATEDIF
This function calculates the difference between two dates.
Examples: =DATEDIF (A1, A2, “Y”) – This formula will calculate the number of dates between two dates in cells A1 and A2.
Replace the Y with any of the following codes to get the difference in other units:
y – years
m – months
d – days
The excel sheet above shows the difference between two years in months.
These are just a few of the commonly used functions in Excel for SEOs. As you become more familiar with them, you can use them to create powerful spreadsheets that will help improve your SEO productivity.
Note that there are 300+ excel functions that you can use to analyze data in Excel. So, don’t be afraid to experiment and learn more about them.
Now, on to the 21 Excel tips to enhance your SEO productivity…
10 Excel Tips to Enhance your SEO Productivity
Tip #1: Use LENs to Count the Number of Characters in a String
The LENs function is a handy tool for counting the number of characters in a string. This can be helpful when optimizing titles and meta descriptions for SEO.
Example: =LEN (A1) – This formula will count the number of characters in cell A1.
You can then use the IF function to check if the number of characters in a given string exceeds a specified limit. You can mark it as an issue in your SEO audit if it does.
Example: =IF (LEN (A3)> 70, “Exceeds Character Limit!”, “OK”) — This formula will display “Exceeds Character Limit!” if the characters exceed 70 or mark it as OK if the number of characters in cell A1 is less than 70.
Tip #2: Tips #2: Remove Duplicates Function
You’re bound to have duplicate entries when working with massive amounts of data. To get rid of them, we suggest you use the Unique function.
When you copy data from somewhere else, say an SEO tool, and paste it into your spreadsheet, you’ll have a lot of duplicate entries.
While it’s relatively easy to go through the entries manually and remove all the duplicates, it’s time-consuming.
That is where the Remove Duplicates function comes in handy. This function quickly scans through your data and removes any entries that are exactly the same as one another.
=UNIQUE(cell range) — This formula will remove all duplicate entries in the selected cell range.
For example, if you have a list of URLs and some of them are duplicates, =UNIQUE(A1:A10) will remove all the duplicate entries from cells A1 to A10.
To use the UNIQUE formula we just mentioned, start a blank column next to the URL column, then type in =UNIQUE(A1:A10).
That will populate the blank column with only unique URLs.
Tip #3: Find and Replace
This is an excellent tool for quickly replacing text strings with other strings.
For example, if you have a sheet full of URLs starting with “http://www.,” and want to replace them all with “www.,” the Find and Replace function can help.
Simply type in “http://www.” on the Find field and “https://www.” on the Replace field and hit enter.
This will replace all instances of “http://www” with “https://www” on your sheet.
You can quickly update URLs, titles, meta descriptions, etc., using this feature.
To use this function, click ctrl+F and type in the string you want to search for in the Find field, then type in the keyword or word you’d want to replace. Click on the three vertical, dotted lines and, next to “Replace with,” type in the word you’d like to replace with. Then, replace all.
Tip #4: The VLookup Formula
One of the biggest timesavers when dealing with large datasets is the VLookup formula.
This formula takes two sets of data and compares them side by side. It can pull unique values or text from two data ranges, making it perfect for SEO audits.
In other words, it allows you to pull data from one data range and compare it with another data range.
For example, suppose you want to get static data from Google Analytics but also have data from an SEO tool.
You can use the VLookup formula to combine the two data sets and get a more comprehensive report.
Each downloaded data represents a different spreadsheet, and VLookup will be how you turn everything into one master spreadsheet, housing all your data.
To use the VLookup formula, type in =VLOOKUP (value, table, column number, false), where the value is the cell you want to search for in the table.
The table is the range of cells containing all your data, and the column number is which column you want to pull data from.
You also want to enter the lookup value as false or true — false finds exact matches and true looks for partial matches.
For example, =VLOOKUP (A1, A1:C10, 3, false) will look for the value in cell A1 and return the corresponding value from column 3 of your data range (A1:C10).
You can use this formula to quickly combine large datasets, saving you time and effort when conducting SEO audits.
For example, =VLOOKUP (A1, A1:C10, 3, false) will look for the value in cell A1 and return the corresponding value from column 3 of your data range (A1:C10).
Video link: https://wus-streaming-video-rt-microsoft-com.akamaized.net/394d8b7e-5e27-4e65-af86-ef71afe594a7/1f23841c-950a-4414-84b5-c92eac7f_1280x720_2990.mp4
You can use this formula to quickly combine large datasets, saving you time and effort when conducting SEO audits.
Tip #5: Make All Text Lowercase, Uppercase, or Mixed Cases
Doing SEO often requires formatting a large amount of text data.
For example, if you want to update the URLs in an SEO audit report, you might have to go through and manually make sure all the characters are lowercase.
That’s why it can be helpful to use Excel’s Make All Text Lowercase formula.
This formula quickly converts all the text in a given cell or a selection of cells to lowercase.
This is extremely helpful when dealing with large batches of URLs.
Simply go to the column or row where you want the lowercase text and type in =LOWER(cell address) to convert all the text to lowercase.
For example, if you wanted to ensure all the text in Cell B1 was lowercase, you would type in =LOWER(B1).
It’s the same when you want to convert something from lower to upper case and change the formula to UPPER.
So, it becomes=UPPER(A1).
You can also use the =PROPER(cell address) formula to capitalize the first letter of each word in a selection.
This is great for quickly making titles and meta descriptions look more professional.
For example, if you want to change the string “improve your SEO with excel” to “Improve Your SEO with Excel” in Cell A1, you’d type in =PROPER(A1).
Tip #6: Give a Description of an Error
If users interact with your excel spreadsheet, you want to ensure it’s as user-friendly as possible. One way to do this is by describing an error.
For example, if a cell returns an error like “#VALUE!”, then you can give the user an explanation of why that happened and what they should do next.
This could be as simple as “Please enter a valid number” or “Please enter a valid date.”
Use the IFERROR formula to help users identify where they made mistakes when entering data.
Using this formula will allow you to change any errors into your own description without changing the actual value in the cell.
For example, if you want to turn the error in Cell A1 into a description that says “Please enter a valid number,” you would type in =IFERROR(A1, “Please enter a valid number”).
That is especially useful for spreadsheets with formulas or functions prone to return errors.
It’s also great for users who might not be familiar with the more technical aspects of excel.
Tips #7: Merging Multiple Columns of Data (Concatenation)
If you have multiple columns of data and want to merge them into one column, then you can use the CONCATENATE formula.
Say you have separate columns for first name and last name.
You can combine the contents of both into one cell by using the CONCATENATE formula like this:
=CONCATENATE(A1, ” “, B1).
This would take the data from Cell A1 (the first name) and Cell B1 (the last name) and combine them into a single cell with a space in between.
Tip #8: Find/Search Text Within Text
Find: Returns the position of a substring within a string
Example: =FIND(“apple”, A1) – This formula will search for the word “apple” in cell A1 and will return the position of the first letter if it finds it.
Search: Returns the position of one text string within another
Example: =SEARCH(“apple”, A1) – This formula will search for the word “apple” in cell A1 and will return the position of the first letter if it finds it. If it doesn’t, it will return #VALUE.
The difference between “Search” and “Find” is that search is not case-sensitive, and find is.
For example, if you are looking for the word “apple” in cell A1 and it only finds “Apple”, the search will still return the position of “Apple”, while Find won’t.
Find is also useful when looking for a specific character within a string.
For example, if you want to find the position of the first comma in cell A1, use =FIND (“,,” A1), and it’ll return the position of the first comma.
Tip #9: Left, Right, Mid Functions
The LEFT, RIGHT and MID functions allow you to extract parts of a string.
LEFT: Returns the leftmost characters from a text string.
Example: =LEFT (A1,3) – This formula will return the first three characters in cell A1.
RIGHT: Returns the rightmost characters from a text string.
Example: =RIGHT (A1,4) – This formula will return the last four characters in cell A1.
MID: Returns a specific number of characters from a text string starting at the position you specify.
Example: =MID (c20, 3, 26) – This formula will return the characters from the third to the sixteenth position in cell c20.
These functions come in handy when extracting specific information from a string, such as keywords or domain names.
You can also use them to format URLs quickly by removing certain parts of them, such as parameters and query strings.
Using LEFT, RIGHT and MID can help you make quick changes to your data without manually editing each cell.
Tip #10: The Nested Formula of Search, Find, Left, Right, Mid, and Len
This is a very powerful formula that combines all the functions we mentioned before.
The left and Search functions, for example, can be used together to quickly search for and extract the domain name in a URL.
This formula would look like this: =LEFT(A1, Search(“/”,A1,9)) .
This formula takes the leftmost characters in cell A1 and searches for the string’s slash (“/”) position.
The 9 represents the position in which to start the search after the first eight characters (https://) have been excluded. This eliminates the first two slashes from the string and ensures that only the third slash is taken into consideration.
The result of this formula will be the domain name without any parameters or query strings.
Using this technique, you can quickly and easily extract the exact information you need from a string speedily and easily.
Search, and Mid can also be used together to extract the middle parts of a string.
Using the domain names example, we can combine these two formulas to extract the domain path (i.e., the path after the domain name).
You can also combine the mid and search function to extract the domain without the extension.
This formula would look like this: =MID(A1,13,Search(“.”,A1,13)-13).
This formula takes the characters starting from position 13 in cell A1 and searches for the dot position in the string.
The 13 represents the position in which to start the search after the first 12 characters (https://www) have been excluded. The result of this formula will be the domain name without the extension (i.e., google in www.google.com).
Why two 13’s? The first 13 is the start position, and the second 13 is used to subtract from the dot position so that it only returns the text before the dot.
Leaving the -13 out will return the text after the dot as well.
These are just a few time-saving formulas and tricks that can help streamline your workflow and boost productivity.
Happy Excel-ing! 😉
