Reporting metrics is an essential step when crafting a marketing strategy. However, this task can get challenging when your data’s all over the place. You’ll need organized...
Reporting metrics is an essential step when crafting a marketing strategy. However, this task can get challenging when your data’s all over the place. You’ll need organized metrics when reporting organic results, and this brings us to the importance of using spreadsheets.
Spreadsheets are useful, accessible tools for sorting SEO data. In particular, Microsoft Excel is one of the most common types of spreadsheets used by SEO specialists. Packed with several features in its arsenal, Excel lets you arrange metrics in different ways. But the various tools available could also leave you feeling stuck, unsure of what to use and how to use them.
Luckily, you can use Excel add-ins for smoother calculations and sorting. But if you’re entering the data manually, you can make use of Excel’s helpful formulas to perform basic SEO tasks, including data analysis.
Organizing your data might seem a little tricky at first, but you’re familiar with how everything works, using Excel will definitely make your life easier.
So are you ready to learn some Excel tips and tricks? Read on to discover formulas, functions, and hacks for SEO.
Before diving into Excel’s advanced functions and tools, let’s familiarize ourselves with useful formulas first. Here are some formulas and functions you need to know.
First up on our list is the LOOKUP formula. With this formula, you won’t need to sift through chunks of data for specific metrics. Just type a LOOKUP syntax anywhere within your spreadsheet, then let it find the data you need.
But what happens when you type the LOOKUP formula in a cell?
Basically, it searches for any value in your spreadsheet’s leftmost column and returns a value in the same row from your specified column.
This formula has two variations: VLOOKUP and HLOOKUP. Like we’ve mentioned above, VLOOKUP focuses on values in the leftmost column. Meanwhile, HLOOKUP searches for any value within the rows of your spreadsheet.
To use any of these functions, type any of the following formulas:
Excel’s SUM function is simple: it adds all the numbers on your spreadsheet. The SUM syntax accepts numbers, cell references, and cell ranges. Plus, you can combine these values within the SUM syntax.
To use the SUM function in your spreadsheet, type =SUM(number1,[number2],…). Make sure you’re following this syntax format when using the SUM function. Otherwise, you might encounter errors.
You can also use a SUM syntax like =SUM(A1:A3,B1:B3) to add values in specific ranges. If you’re inserting or removing rows, the ranges in the syntax will change.
Next up is the IF function, which lets you make logical comparisons between a value and what you expect. This means that an IF statement can have two results: your comparison may either be TRUE or FALSE. For example, if your spreadsheet’s first cell has a value of 1 and it’s true, Excel will input the resulting value in another cell.
To use the IF function, type =IF(logical_test,value_if_true,value_if_false) on Excel.
Now, you might ask, “How can I use the IF formula in Excel for SEO metrics?”
Here’s one tip: create keyword categories using the IF formula. Let’s say you’ve downloaded a spreadsheet with your target keywords from Google Ads or another tool. With the IF formula, you can identify specific search queries under any category.
Want to know how many times a specific keyword appears on your spreadsheet? Excel’s COUNTIF formula will give you the answer. It counts the number of cells within a range where the keyword appears.
To use COUNTIF, type =COUNTIF(range,criteria).
FIND is another basic Excel function you should master if you’re changing a text string. But how does it work?
The FIND function looks for a text string within another string, then returns the value of that string’s starting position. For example, you can rely on the FIND function when dealing with capital letters in URLs.
Keep in mind, however, that the FIND function counts single and double-byte characters as one. Moreover, it’s a case-sensitive formula and doesn’t accept wildcards. Its syntax looks like this: =FIND(find_text,within_text,start_num)
Alternatively, you may want to consider using the SEARCH function. This formula returns the value for a character where a specific text string was initially located. It also reads characters or text from left to right. But just remember that SEARCH accepts wildcards and isn’t case-sensitive.
To use the SEARCH function, type =SEARCH(find_text,within_text,start_num).
If you’re organizing rows of data or automating URL creation, the CONCATENATE function is an excellent feature to use. In a nutshell, it combines several text strings into a full string. You can use CONCATENATE to join two text strings together or place the strings anywhere within cells.
Need to use the CONCATENATE formula? Type =CONCATENATE(text1,text2,…) on your Excel spreadsheet and you’re good to go.
Now that we’ve shown you some of Excel’s basic functions, we’ll move on to more specific formulas. This time, we’ll talk about Excel spreadsheet formulas for text and characters. Let’s dive into these functions below.
This formula tells you how many characters a text string has. If you want to know how long your page titles and meta descriptions are, use the LEN function. Start counting those characters by entering =LEN(text) on your desired cell on Excel.
Does your spreadsheet include uppercase or lowercase text? Let Excel’s PROPER function help you make the necessary changes and convert your text into the right case. To do this, type =PROPER(text) to capitalize the first letters of your text.
Both of these commands are straightforward: UPPER changes your text into all caps, while LOWER converts them into lowercase. To use UPPER or LOWER, type any of the following formulas in Excel:
The LEFT function counts characters from the start of a text string and gives you the number of characters. Meanwhile, the RIGHT function counts characters from the end of a text string and returns the number of characters.
LEFT and RIGHT functions let you obtain the specific number of characters based on the given position of the text string you provide. This means you can use them for counting page title characters, for instance. Plus, you can truncate URLs when you use the LEFT and RIGHT formulas.
To use the LEFT or RIGHT function, enter any of the following formulas on your Excel spreadsheet:
If you’re importing data from an SEO tool to Excel, you might notice details that you need to edit or change. But how can you modify those details without sifting through a bulk of data? The answer lies in Excel’s SUBSTITUTE formula.
In a nutshell, the SUBSTITUTE function replaces text within a specific cell into new text. Select the cell that you want to edit, then type =SUBSTITUTE(text,old_text,new_text).
Keep in mind that you may encounter trouble using this formula if your spreadsheet doesn’t present search volumes as actual numbers. After all, Excel will fail to sort your list if you quantify search volume using numbers such as “10k” or “1M”.
Instead, you’ll need to spell out the actual numbers to help Excel sort the volumes properly. In that case, use any of the following formulas:
You can also enter =SUBSTITUTE(text,old_text,new_text,instance_num) in Excel which works in the same way. The only difference is that it lets you replace “old text” with “new text.” The instance_num function lets you specify which occurrences you want to replace your “old text” with “new text.” Otherwise, you’ll end up replacing all “old text” in your spreadsheet with the “new text.”
Like we’ve mentioned at the beginning of this guide, organized data is a must when reporting organic results. With that said, Excel has several tools and functions that’ll help you sort your metrics.
Text to Columns isn’t a formula. Rather, it’s a tool that organizes specific cell data into separate columns. It’s useful for dividing a website’s subfolders, subdomains, and root domains. And if you’re working with top-level domains (TLDs), Text to Columns can help you separate them.
You can access the Text to Columns tool from Excel’s Data tab. To split your data using Text to Columns, follow these steps:
The latest version of Excel includes an IFS function, which checks whether a statement meets one or more conditions. Then, IFS returns a value that corresponds to the FIRST true condition.
For instance, you might want to classify keyword ranks as low, medium, or high. Just enter the syntax below:
=IFS(cell>=[low-number-threshold],“Low”,AND(cell<=MIN([low-number-threshold]),cell>MAX([high-number-threshold]), “Medium”,cell<=[high-number-threshold],“High”)
To use the IFS function for other tasks, type this syntax: IFS([Something is True1,Value if True1,Something is True2,Value if True2,Something is True3,Value if True3]).
The OFFSET function takes a specified number of rows and columns from a cell or a cell range, and shows those references in another cell. By using OFFSET, you can return a single cell or a cell range and put it in a specific cell.
To use the OFFSET function, type the following syntax on your Excel spreadsheet:
OFFSET(reference,rows,columns,[height],[width]
So far, we’ve discussed Excel functions, formulas, and tools that’ll help you perform basic tasks, edit text and characters, and organize data. But if you want to truly step up your Excel game, you’ll need to learn advanced functions and formulas. Check them out below.
As discussed before, the VLOOKUP formula helps you search values in your spreadsheet’s leftmost column. However, it might not always give you the results you want. After all, VLOOKUP would be useless if a key field isn’t found on the leftmost column of your table.
So what do you do? You can use Excel’s INDEX and MATCH functions.
INDEX returns a value of a cell found at the intersection of a row and column within a given range. Meanwhile, MATCH returns the relative position of an item to match a specific value and certain order you’ve set.
Want to test these functions on your Excel spreadsheet? Type any of the following formulas:
Other than keywords, your SEO spreadsheets may include URLs, domains, blog titles, or email addresses for you to find and organize. This information is particularly useful if you’re conducting link building and outreach.
In that case, Excel’s search bar might help you look for the links you need. However, you may encounter trouble if you’re working with a long list.
The solution? Extract your data using the REGEXTRACT formula.
So far, the functions and formulas we’ve discussed have standard codes. REGEXTRACT, on the other hand, has a special syntax. Enter the formula below to see REGEXTRACT in action:
=REGEXEXTRACT(cell_num,“^?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)
Sometimes, your spreadsheet might show errors after you’ve entered a formula. But by using the IFERROR function, you can reduce formula errors.
Here’s how it works: if you specify a formula that translates to an error, the function will return the value you specify. If it doesn’t translate to an error, the formula will enter the result instead.
The IFERROR function will work when you enter the syntax below:
=IFERROR(value,value_if_error)
Once you’ve cleaned up your data, you can analyze and use it to develop your SEO strategy. Excel’s pivot tables can streamline your data analysis while making your metrics look organized. Moreover, using these tables enable easier analysis through queries and data presentation.
For example, you can include your spreadsheet data in a pivot table. Just make sure you’ve entered your metrics as a list before creating your pivot table. Moreover, your first row should have column labels. Excel will put those labels under the “Field Names” section.
Other guidelines for creating a pivot table include:
If you’re using Excel’s Windows version, follow these steps to create a pivot table:
Excel’s formulas, functions, and tools can save you time in sorting your SEO data. With these features, you won’t have to go through all your metrics and organize them manually. Plus, you can dig deep into results and present concise data with pivot tables. When you’ve got our Microsoft Excel hacks down pat, performing SEO tasks will be a breeze.
Google favors websites with good backlinks. Find out how to earn these links here.
Businesses investing in SEO face multiple challenges. We dive deep into these common roadblocks and how to overcome them.
Google is adding two more search updates to its list of 2022 algorithm updates. Our newest blog post looks into the company’s helpful content and product review updates.
Video media has evolved over the years, going beyond the TV screen and making its way into the Internet. Visit any website, and you’re bound to see video ads, interactive clips, and promotional videos from new and established brands.
Dig deep into video’s rise in marketing and ads. Subscribe to the Rocket Fuel blog and get our free guide to video marketing.