Advanced Excel hacks every SEO should Know 

At some point, you will probably open an Excel document, whether you use it to analyze survey data or calculate sales data. Excel and other spreadsheet software are characterized by their rich functionality. This means there are plenty of opportunities to get stuck trying to set them up. If you need to use Excel every day, you don’t have to do everything manually. It’s a huge waste of time. Most marketers understand these features, but as they say, it’s just a learning curve.

What is excel?

Microsoft Excel is spreadsheet software for organizing, analysing, and manipulating data. It includes tools for performing mathematical calculations, creating charts and graphs, and creating pivot tables for data analysis. Excel also supports various data management and manipulation features, allowing users to create custom formulas for their specific needs.

You should learn excel for SEO

If you don’t already have a spreadsheet software, you should. One of the most important parts of developing a marketing strategy is reporting metrics, whether it’s a marketing plan, his weekly SEO tracking, or an annual report. A spreadsheet allows you to organize all this data in one easily accessible place. When it comes to things like SEO, it’s important to be organized. Especially when you have hundreds or thousands of keywords. The reason Excel is such a great tool for SEO is that it comes with add-ons and plugins designed to make your job as easy as possible.

For example, his SEO Tools for Excel comes with built-in functions for marketers. Tools like this can be necessary for people who need to use Excel but don’t like it. you know who you are However, aside from special tools, Excel is full of formulas that help with keyword curation, list segmentation, and data analysis. However, many people still shy away from Excel because they don’t know how to use formulas. Learning or remembering how to use certain formulas can feel silly. But it doesn’t have to be. Excel doesn’t have to look like a big puzzle. In fact, once you understand how it works, you can even enjoy it. Without further ado, here are 7 ways to use Excel’s built-in formulas to step up his SEO game.

1.   SUM: Adds up a range of cells.

The SUM formula adds up all the numeric values for the selected cells.

Syntax: SUM (number1, [number2], …)

SEO use cases: Getting the sum of website traffic for given keywords Getting the sum of search volume for given keywords, etc.

Example :- We want the total of the traffic values mentioned in cells E2 to E22. For that, we inserted the formula =SUM(E2:E22) in E23 and got the total.

2.   IF function allows you to check whether a specific condition is met.

And it returns the answer or value as you mention in syntax.

Syntax: IF (logical _test, value_ if _true, [value_ if_ false])

    SEO use cases: Determining whether the URLs match Checking if the keywords are worth targeting     based on their search volumes Marking the titles and meta descriptions’ character length as Yes/No or Good/Bad, etc.  Example: In the above example, we calculated the length of meta descriptions. Now we want to check whether the length of meta descriptions is within the recommended range i.e., less than 160 characters.

      Hence, we entered the formula =IF (H2<160, “Yes”, “No”) in the I2.

Since all the meta descriptions in this example meet the condition, the corresponding cells are marked as Yes. Had any of them exceeded 160 characters in length, the cell next to it would’ve returned the value as No.

3.   TEXT TO COLUMNS

Text to Columns function helps you split the data of one cell/column into multiple cells/columns.

Syntax: Text to Columns is not a formula, it’s a process.

SEO use cases: Breaking up URLs into multiple parts such as protocol, domain, path, parameters, fragments, etc. Splitting full names into first and last names Breaking up comma-separated values (CSVs) into separate columns, etc.

Example:- We have a list of URLs and we want to break them down into separate columns named according to the parts of the URL.

For this, we selected the URLs and clicked Data. Then we selected the Text to Columns function from the menu as shown below.

In the next screen, we clicked Delimited and hit Next.

4.   TRIM

When you apply the TRIM formula, it removes the extra blank spaces from text or string.

Syntax: TRIM(text)

SEO use cases: Removing extra spaces in data exported from SEO tool Removing extra spaces in data copied from a different file format, etc.

 Example:- Take a look at the titles in Column B. As highlighted, there are extra spaces in each of them. And we could remove them by using =TRIM(B2) formula in cell C2. 

For a sample size this small, it’s easy to remove extra spaces manually. But for huge datasets, it’s not feasible.

Besides, a single extra space here and there is difficult to locate when you’re dealing with hundreds of titles. That’s where you can keep your title consistent with this formula.

5.   LEN formula

When you use the LEN formula on a text string, it returns the number of characters in that string.

Syntax: LEN (text)

SEO use cases:

• Analysing the length of meta descriptions and title tags
• Evaluating PPC and ad copies
• Identifying URLs that are too long, etc.

Example: – Let’s say we want to check the length of multiple titles and meta descriptions. As shown in the below screenshot we applied the formula in cells F2 & H2, and simply dragged it down.

And we have the number of characters for each of the titles and meta descriptions.

6.   Upper / Lower / Proper

The UPPER LOWER PROPER formulas allow you to change the case of selected text or data sets into uppercase, lowercase, and proper case (title case).

Syntax:

=UPPER(text)

=LOWER(text)

=PROPER(text)

SEO use cases: Keeping the consistency in the title tags format Keeping the consistency in meta descriptions format Capitalising acronyms, etc.

Example:- Here, we have all the page titles in lowercase format. However, we want to change them into a title case. Therefore, we added =PROPER(A2) in B2 and dragged it down.

And there you have it. The text is converted in the proper case.

7.   COUNTIF

The COUNTIF formula gives you the count of cells with specific attributes as entered by you while typing the syntax.

Syntax: COUNTIF (range, criteria)

SEO use cases: Knowing the count of keywords with a certain search volume or ranking difficulty Getting the count of URLs according to their categories Knowing the count of duplicate keywords or URLs, etc. Example:

For instance, here we’ve applied the formula =COUNTIF (C2:C22,”>40”) in H2. It means counting the number of cells with a keyword difficulty (KD) score of more than 40.

There are 14 such keywords.

Thus, you can filter out the keywords with a specific difficulty score and decide whether to target them or not.

8.   Average

The AVERAGE function helps you calculate the average numeric value of the data for selected cells.

Syntax: AVERAGE (number1, [number2], …)

SEO use cases: Counting average search volume of related keywords Calculating average keyword difficulty for a group of matching keywords Calculating the average traffic generated by a group of matching keywords, etc.

Example :- We have the search volumes in Column B and we want to calculate the average search volume for this set of keywords. We applied the formula =AVERAGE (B2:B22) in cell H3.

As you can see, it returned the average value in H3. You can use this function to get the average search volume and difficulty while considering whether to target a group of keywords as a part of your content and strategy.

9.   SUMIF

The SUMIF formula helps you add the numeric values from different cells that meet specific criteria.

             Syntax: SUMIF (range, criteria, [sum_range]

             SEO use cases: Calculating the total website traffic data based on specific criteria Sorting and    manipulating the datasets Adding up the search volume of keywords with high or low difficulty, etc. Example :- Now, we want to calculate the total traffic for the keywords with a difficulty score above 40. Here’s the formula we used in cell E24: =sumif (C2:C22,”>40”, E2: E22).

This function can be useful to understand the difference in traffic generated from high and low difficulty keywords. Accordingly, you can finetune your keyword strategy.

Also, if you’re not sure how to create the right keyword strategy for your business, you can hire external  for the same.

10.VLOOKUP

VLOOKUP helps you pull the data from one Excel sheet to another by using a formula instead of copying and pasting them manually.

Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

SEO use cases: Pulling or sharing keyword data between different sheets Combining different datasets to make a master sheet Combining data from multiple sheets for SEO/Content/Backlink audit, etc.  Example :- Let’s say we have a set of SEO data such as keywords, SERP features, volume, traffic, etc. in one sheet. We’ll call it Sheet 1 and it looks like this.

And there’s Sheet 2 that holds the keyword difficulty (KD) score for the keywords mentioned in Sheet 1. It looks like this.

Now we want to pull the KD score in Sheet 1. So we used this formula in G2:

=VLOOKUP (B2, Difficulty !B:C,2,FALSE)

Then we simply dragged it down from G2 to G30.

And there it is. All the KD score data is right there in Sheet 1.

As an SEO, you might be creating multiple Excel sheets for different types of data and sometimes you might need to combine multiple sheets together. That’s where VLOOKUP can save you time that goes into copying and pasting time and again.

Besides, the data in the master sheet gets updated automatically when you change values in source sheets.

11. PIVOT TABLE

Pivot table lets you group data from different columns, extract trends from comprehensive datasets, and provide a table of summary.

Syntax: Since pivot table is not a formula, it doesn’t have a syntax. Instead, it’s a step-by-step process that we’ve shared in the example.

SEO use cases: Analysing the changes in ranking for different pages Analysing traffic based on GA data Reviewing site architecture using crawl data, etc.

Example:- Being an SEO agency, we also keep upgrading our SEO strategies regularly. Therefore, we want to extract certain trends from our keywords dataset and get a summary in a pivot table.

Here’s the keyword data and we want to generate a pivot table for the same. So, we selected the data range and clicked Insert.

From the Insert menu, we selected Pivot Table.

Next, we clicked Existing Worksheet since we wanted the pivot table on the same sheet. And clicked the cell H5 to select the location where we want to place the table and hit OK.

In the next screen, we selected the fields that we want to include in the pivot table. For instance, we chose fields like Parent Keyword, Difficulty, and Volume.

And clicked Sum of Volume to modify the settings for this field.

Here, you can select how you want to manipulate the data for this field. Since it’s for illustration purposes, we went ahead with the simple Sum function and clicked OK.

            However, you can also do advanced calculations in pivot tables.

          Here’s what the pivot table looks like.

The pivot table has a list of unique parent keywords extracted from Column A and the sum of the search volume for each of the keywords that belong to parent keywords. Also, we have a grand total of the search volumes. This can help us identify the parent keyword that is more popular and we can thus keep that keyword as the focus keyword of the content/page we are planning to optimise for SEO.

12.SUBTITLE

 With the SUBSTITUTE formula, you can replace any character or word with another word.

Syntax: SUBSTITUTE (text, old_text, new_text, [instance_num])

SEO use cases: Editing like title tags or meta descriptions at scale Updating the URLs or parts of the URLs quickly Changing or redirecting the URLs during the website migration process, etc.

Example:- Refer to Column A in the screenshot below. We want to replace “Supple” with “Supple digital” at the end of the titles. We did this by inserting the =SUBSTITUTE (A2,” Supple”, ” Supple Digital”) formula in D2.

Thus, you can edit titles, URLs, meta descriptions at scale with this function.

Thus, you can edit titles, URLs, meta descriptions at scale with this function.

FAQ’s

How do you add a Note to a cell?

Ans: In Microsoft Excel, you can add notes to cells by right-clicking the cell, selecting Add Note from the context menu, and typing your note. You can view the note by hovering over the cell. In Google Sheets, Then you can add notes to cells by right-clicking the cell, selecting Notes also from the context menu, and typing your note. You can view the note by clicking the cell to display the note icon.

Can you protect workbooks in Excel?

Ans: Yes, you can protect Microsoft Excel workbooks using the workbook protection feature. Then This feature can allows you to set a password to prevent unauthorized access or modification to workbook structure and items such as sheets, cells, macros, etc. You can also specify the actions that users can perform with their password. B. Sorting or filtering data. To protect your workbook, go to the Review tab, click Protect Workbook, and follow the on-screen instructions to set a password and select the level of protection you want.

How do you create dropdown lists in Excel?

Ans: To create a dropdown list in Microsoft Excel, you can use Data Validation. Method is as follows. Select the cell where you want the drop-down list to appear. Then Go to the Data tab and click Data Validation. In the Data Validation dialog box, select List in the Allow field. In the Source field, then enter a comma-separated list of items to display in the drop-down list. then You can also select the range of cells that contains then the list item by clicking the range of cells in the worksheet. Click OK to close the dialog box. Also When you create a dropdown list and select a cell in the dropdown list, and a down arrow appears next to the cell allowing you to select an item from the list.

How do you generate Pivot Tables?

Ans: To create a pivot table in Excel: Select data, go to “Insert” > “Pivot Table”, select location for pivot table, drag fields to “Rows”, “Columns”, “Values” and/or “Report Filter”. Customize as needed.