Get a free consultation from an expert on your project

Why seo specialist need to be able to work with Excel

When promoting the site, each seo specialist regularly encounters the processing of a large amount of data that is needed group, filter and structure. It is most convenient to perform these tasks using the Excel or Google Table program. 

Tables allow you to reduce the time spent on performing certain tasks, organize work and make it more productive. This is especially convenient for manual clustering queries in the semantic core.

But at the same time, a large number of features of the Excel program can put a novice seo specialist at an impasse. In this article, we have prepared the main functions and table formulas that you may need.

Useful Excel Functions for Compiling a Semantic Core

1. Link Deleting in Excel

A very simple function that works the same way as in Excel and in Google tables. You will need it if you exported or inserted data into a table that contains links.

For example, when working with Yandex. Wordstat you did not use the browser extension yandex wordstat assistant (which is more convenient to work with), but simply copied the queries and pasted into the table. Then each keyword will contain a link to the service and impede work, so you need to convert them to plain text.

 We select the required range, right-click the mouse button and click “Delete hyperlinks”.

Удаление ссылок в семантическом ядре

2. Replacing characters and elements in the table

This is a quick way to clean data and may come in handy semantic core. For example, by inserting queries in the Excel table, you will see that many of them contain the special “+” symbol. In order to massively remove it, you need to use the “Find and replace” function. 

In Google tables, it is in the “Change” tab, in Microsoft Excel tables in the main section “Find and Select > Replace”. Or you can just press the key combination Ctrl + H. In the dialog box that opens, in the “Find” field, we write the + sign, we leave the “Replace” field blank. Click “Replace All” and get a list of keywords without additional operators.

Зачем seo специалисту функция Excel найти и заменить

3. Removing unnecessary spaces

When processing keywords in the Excel table, cells that start with spaces or contain multiple spaces in a row between words. To get rid of them, use the “= FAT ()” function.

Лишние пробелы при группировке запросов в семантике

4. Frequency or alphabetical sorting

You have two data columns. In one, this is a list of keywords, in the second - their frequency. If necessary sorting requests depending on their frequency, select these two columns, initially putting the cursor on the column with frequency and stretching the selection below. Next, click “Data > Sort the range in column B, A-Y”.

Группирование ключевых слов по частотности

If it is necessary to sort alphabetically, then we perform all the same actions, but when selecting, initially put the cursor on the column with requests.

Сортировка запросов в семантике по алфавиту

5. Search and select duplicate cells

Seo specialist this function will be very useful when grouping requests in a semantic core, it will allow you to quickly find repeating keywords in the table.
In Excel, select the column in which we want to find duplicates, then click on the Main tab “Conditional formatting > Cell Selection Rules > Repeating values”. We choose the color of the fill of cells and you're done. 

Использование условного форматирования в seo

In Google Tables, this is a little more complicated and is several ways to solve this problem

For example, you can perform the following actions: “Format > Conditional formatting > Add Rule > In the “Formatting Rules” section, select “Your formula” and insert this formula without quotation marks “= AND (HEUSTO (A1)); ACCOUNTESLI ($ A $ 1:$ Z; "=" & A1) > 1). ”. When entering this combination, empty cells will not be taken into account, and requests that are indicated in the selected range will be highlighted more than once. 

Выделение повторяющихся ключевых слов в Google таблицах

The second way can immediately solve several problems that a specialist needs to perform with an SEO when grouping requests in a semantic core - set browser extension Remove Duplicates (it is available only in English).

Расширение для гугл таблиц для поиска дублей ключевых слов

With it, you can select or immediately remove repeating cells. After installation, to use it, select the range of cells we need, click “Supplement > Remove Duplicate > Find duplicates or uniques”.

Расширение для поиска дублей запросов фото 1

We can back up this sheet by ticking in front of “Create a backup copy of the sheet”. Next, select the data type for which it will be necessary to select cells, for example, duplicates, excluding the first occurrence.

Как использовать функцию поиска дублей в seo

If empty cells are present in the selected range, check the box opposite the “Skip empty cells” field to skip them. If in the column with keywords for the semantic kernel there is a title with the name of the page where they will be placed in the future, then select “My column has header” so that the name is not taken into account. Also in the “Case sensitive” field, you can specify whether to search the register or not.

Настройка расширения для гугл таблиц фото 3

The last step is choose what you need to do with duplicates:

  • pour the cell in color;
  • add another column indicating the status of Duplicate;
  • copy or move them;
  • clear cells;
  • completely delete lines that contain a repeat.

Выделение повторов запросов в гугл таблицах

If you just need to select the duplicate query color, then select the first option and click the action confirmation button.

6. Delete request takes 

If you need to immediately clustering the query of the semantic core in Excel remove repeating cells, then in the “Data” section we select the item “Delete duplicates”, after highlighting the desired range.

Удаление дублей ключевых слов seo специалистом
Google Tables can use special formula for displaying only unique values. In the free column we introduce a combination without quotation marks “= (UNIQUE (A1: A100))”, where A1: A100 is a column with keywords. Unfortunately, this method will not work if the column contains words written in different registers. 

Поиск seo специалистом уникальных запросов

The second way for google tables is to use the Remove Duplicates supplement, which was written in the previous paragraph.

Get a free consultation from an SEO expert on your site

7. Search and delete minus words

Conditional formatting can still be used to highlight cells that contain or do not contain specific values. SEO specialist this function may be needed to highlight queries in the semantic core that have the so-called minus words. 

Поиск минус слов в таблицах Эксель

In Excel, “Conditional Formatting” is located in the main section, in Google tables in the “Data” tab. We set the formatting rules “Text contains” and indicate the beginning of our minus word (beginning, because the ending of the word may be different), select the fill color and click “Finish”. Further, for the subsequent processing of selected cells, they can be sorted by color (more on this in 8 point).

Условное форматирование для выделения минус слов в семантике

Another way to quickly find and delete minus words is use of filters. With their help, you can make sure that only those cells that correspond to the specified parameters are displayed in the table. To do this, select the range of cells with keywords, in Excel we go to the “Data” section, click “Filter” if we work in Google Tables, then “Data > Create a filter”.

Использование фильтров для поиска минус слов в семантике

Click on the appeared filter icon at the top of the column, select “Filtrate by the condition > The text contains”. We get a list of requests that contain minus the word, and delete them.

Сортировка seo семантики по минус словам

8. Sorting cell fill color data

Excel is provided filtering data depending on the color of the cell fill, for this, in the “Data > Filter” section, select the color filtering conditions.

Сортировка по цвету ячейки в Экселе

There is no such function in Google tables built into it and you need to download another one for its use browser extension “Range sorting +”. This supplement will allow you to sort data by cell fill color and text color.

Расширение для Google Таблиц сортировка ключевых слов по цвету

A few more Excel functions when performing other tasks of a seo specialist

1. Line mount for easy operation

If you do seo, then you know that you often have to work with a lot of data. Each column in the table corresponds to a certain indicator and has a name (heading). For these names to always be in sight, you need to use the fixing function. Click “View > Fasten > 1 line”.

Закрепить строку в Google Таблицах фото

2. Expand cell to desired width

We have added this item to our guide for more convenient work with Excel, rather than for specific SEO tasks. 
To expand the cell that contains the text, to the optimum size, hover over the column dividing strip and click twice. 

Как расширить ячейку в гугл таблицах фото

3. Work with character register

If necessary first word in a cell with a capital letter, you can use the following formula: “= PROPISN (LEVSIMV (STROCK (A1); 1)) & PSTR (STROCK (A1); 2; DLSTR (A1) - 1). ”.

Формула для первой заглавной буквы фото

If vice versa you need make all the letters in the lowercase, then use the formula “= STRUCT (A1)”.

Формула для всех строчных букв в экселе

4. Calculation of the number of characters in meta tags

You, as a seo specialist, have already grouped requests in the semantic core and have begun to prescribe meta tags (title, description), and you need to count, how many characters in the end it worked out. To accomplish this, use the “= DLSTR (A1)” formula. 

Подсчет количества символов в мета тегах

Then, using conditional formatting, you can highlight cells that are larger or smaller than the recommended size.

5. Calculation of the number of requests in TOP

In the existing unloading with the site positions, it is necessary to calculate how many of these requests are, for example, in TOP 10. To do this, use the formula “= COMPRESSED (A1: A50;" < = 10 "), where“ A1: A50 ”is a dedicated range with positions.

Проверка сколько запросов в топе выдачи

6. Checking how many days have passed since some date

Consider how this function can be useful in SEO. Let's say you gave the task to the performer to place free links to your site. TK indicated that payment would be made if the link “lived” more than 7 days, the check is carried out after this time. So that you can be sure track the number of days passed use the formula “= EFF (EPUSTO (B2) ;; DIVINE (B2; TODAY (); "D")), where instead of B2, indicate the address of the cell in which the date of placement of the link is written. 

Подсчет прошедших дней в гугл таблицах

Summing up

In this article, we examined the main functions and capabilities of the Excel program and Google tables, which can be useful to both a novice seo specialist and an experienced one. To perform daily tasks, you do not need to memorize these complex formulas, it will be enough just to know what they are and, if necessary, you can use them. 

Master Excel, put the information into practice and share your own experience in the comments.