Pavel Shulga
Content:
- Why seo specialist need to be able to work with Excel
- Useful Excel Functions for Compiling a Semantic Core
- A few more Excel functions when performing other tasks of a seo specialist
- Summing up
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.
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.
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.
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”.
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.
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.
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.
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.
The second way for google tables is to use the Remove Duplicates supplement, which was written in the previous paragraph.
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.
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.
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”.
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.
