Useful formulas in Excel for an SEO specialist

Formulas

VLOOKUP

This formula is used to extract data from one table and add it to another.

Example: we have a table with queries and their frequency.

Task: cluster queries and add the cluster number to the main table.

After clustering is completed, a table is created with queries and cluster numbers. Now you need to add the cluster number to the table with queries and frequency.

To do this, we use the VLOOKUP formula:

In the cell to the right of the frequency column we write:

=VLOOKUP(A2,I :J ;2;FALSE)

In this formula:

• A2 - cell with the first query from the first table;
• AND :J — the range in which the second table is located (Query - Cluster);
• 2 — the number of the column in the table in which the cluster number is located.

We drag the formula down, and the cluster numbers will be drawn into the main table.

SUM and SUMIFS

SUM allows you to calculate the total sum of the values ​​in selected cells. SUMILIMS is more flexible and allows you to summarize data under several conditions.

For example, if we have a table with queries, frequency and cluster numbers, and we need to calculate the total frequency for each cluster, we can use the formula:

=SUMIFS(B :B ;WITH :C ;C2)

In this formula:

• IN :B — range for summation (column with frequency);
• WITH :C — the range by which filtering will be carried out (column with cluster numbers);
• C2 - condition (cluster number in the first line).

Result: the formula will calculate the total frequency of queries for each cluster.

COUNTIF

Using this function, you can count the number of cells that satisfy a certain condition.

For example, if you need to find out how many requests belong to each cluster, we use the formula:

=COUNTIF(C :C ;C2)

In this formula:

• WITH :C — column with cluster numbers;
• C2 — condition (the first value in the column with cluster numbers).

The formula will count the number of requests for each cluster.

Functions

Remove duplicates

If there are duplicate values ​​in a table, such as queries, you can quickly remove them.

To do this, select the desired range and select “Data” - “Remove duplicates” from the menu. You can specify which column to remove duplicates from.

Column text

This tool helps you divide the data contained in one cell into several columns based on a specific characteristic.

For example, if you have a column of page URLs and need to separate them by a separator, use the Text by Columns feature. Select a separator, such as "/", to split the URL into parts.

Find and Replace

To quickly replace one value with another in a table, use the Find and Replace function. For example, if you want to replace the word "catalog" with "katalog" in a URL, simply select the column, press Ctrl+H, enter the old and new words, and Excel will replace them throughout the table.

CSV import

To import data in CSV format into Excel, choose File - Import, then select the desired CSV file. After this, the data from the file will be loaded into Excel, and you can work with it as with a regular table.

If you have any questions about SEO or Excel, you can contact the "SEO COMPUTER" studio for any question by email info@seo.computer.

ID 7662