Absolute link in Excel - description, examples.

Any, even the basic Microsoft Office suite contains a powerful Excel spreadsheet editor (Excel). The list of functions available in it is truly impressive: starting from the ability to sort and filter data and ending with the ability to build summary diagrams on their basis. And it’s almost impossible to imagine any data set stored in the editor without rows and columns calculated by means of formulas.

Formulas in Excel

Any formula is a command containing an indication of what actions to perform with a particular cell. And when the user is faced with the need to copy, “stretch” the formula to the entire row or column, he understands that in some cases part of the formula or the whole formula must remain unchanged. This will let the user know that there are absolute and relative links in Excel . Consider these concepts in more detail.

Relative link

Remembering what a relative link is in Excel is easy. This type of link is selected by default and changes its value when pulling (copying) a formula into neighboring cells, regardless of whether copying is performed along columns or rows.

Consider a simple example. Create a small data set consisting of two columns: “Employee” and “Salary”:

In the "Excel" parameters, we set the A1 link style - this addressing is convenient and understandable to most users. Here A, B, C are the column names, and the rows are numbered. Thus, the cell with the data "Zakirova E. M." Address - A4. This small digression will be needed when we begin to deal with links in formulas.

Now imagine that we want to calculate, based on salary data, the salaries of each employee. We put an equal sign in cell C2 and introduce the following formula (based on information that the salary is 40% of the salary): = B2 * 100/40. Here B2 is the salary of the first employee in the table. Press Enter and move the mouse pointer to the lower right edge of cell C2, waiting until the pointer takes the form of a thin black cross. While holding down the left mouse button, we drag the formula down to the end of the table, that is, to cell C7 (you can replace this action with a double click on the lower right edge of the cell). The column is automatically populated with data:

The formulas for these cells are as follows:

As you can see, when we stretched the formula down vertically, the column name remained unchanged (B), but the line number changed sequentially. Similarly, by copying the formula horizontally, we get an unchanged row value with a changing column number. Therefore, the link is called “relative” - copies of the first formula entered will change the link relative to its position in the range of cells in the sheet.

As you can see, understanding what a relative link is in Excel is not at all difficult.

Let's move on to the following types of links.

Absolute link

Absolute link in Excel is the next common type of link. In this case, when copying the formula, the row and column referenced in the formula are fixed.

Of course, the absolute link itself is not used alone, because copying it does not make much sense. Therefore, this type is common in combined formulas, where some of them are absolute references, the rest are relative.

For example, let’s introduce one more data set - the monthly salary supplement, the same for all employees:

Accordingly, it is necessary to change the cell with the calculation of salary C2, now it will contain the following formula:

When we press Enter, we will see that in this cell the salary is correctly recounted. But when we extend the formula to all employees, their salary will not be recounted, because the relative link used tried to take values ​​from G4..G8, where there is absolutely nothing. In order to avoid this situation, you must use an absolute link in Excel. To fix a column or row that should not change when copying a formula, you must put a dollar sign ($) next to the column name or row number, respectively.

Change our formula to the following:

And when we copy it, the entire salary of employees is recounted:

Next to the concept of "absolute link" in Excel there is always the concept of a mixed link.

Mixed Link

Consider this type of expression. A mixed link is a link that, when copied, changes the column number with the same row number or vice versa. In this case, the dollar sign is placed before the row number (A $ 1) or the column number ($ A1), that is, before the element that will not change.

A mixed link is used much more often than a true absolute link. For example, even in the simple previous example, we could completely replace the formula = (B2 + $ G $ 3) * 100/40 with = (B2 + G $ 3) * 100/40 and get the same result, because we copied the formula vertically , and the column number would have remained unchanged anyway. And this is not to mention situations when you really need to fix only the row or column number, and leave the rest available for change.

Interesting fact

It will be interesting to know that an absolute link in Excel can be set not only by indicating the dollar sign in front of the row and / or column number. "Excel" allows you to select the type of links by pressing the F4 key after specifying the cell address - the first click changes the link from relative to absolute, the second to mixed with a fixed row number, the third to mixed with a fixed column number, and the next Clicking the link will again take the form of relative. Changing the appearance of the link in Excel in this way is very convenient, because there is no need to resort to changing the layout on the keyboard.