Even if you recently started working with the Microsoft Excel program, you probably know that one of its advantages is the convenient work with formulas and tables. Therefore, today we will consider addressing in such tables and find out what relative, absolute and mixed links are. Their work is quite simple, so it will be easiest to consider them in practice. So what is an absolute and relative link?
Definition
Links in Microsoft Excel are not the same as links on the Internet. A link is a cell address (for example: A1, B10). They are divided into two main types - absolute and relative links. In Excel, addressing occurs in cells, so when transferring and copying formulas, we often need to save or, conversely, replace the data.
Relative link
This species is most common. If you did not attend special courses, nobody taught you, then you are unlikely to meet with absolute links. Therefore, we will start with what is simpler.
Absolute and relative links in formulas serve to work with cell addresses, but for opposite purposes. The view we are considering is basic and does not save the addresses of the cells in the formula when it is transferred. Consider an example.
- Let us be given cells with data (columns) - M1, M2, M3, H1, H2, H3. Under them in cell M4 we write a formula, the result of which will be the sum of three numbers (M4 = M1 + M2 + M3). Everything seems to be simple. Now we need to calculate the contents of cell H4. It is also the sum of the data that is above it. By dragging or simply copying an M4 cell, we transfer the formula to H4. As a result, all references in it (M1, M2, M3) will be replaced by H1, H2 and H3, respectively.
This is called relative addressing. The example seems simple and a natural question arises, isn’t it easier to re-write the formula? No, in large tables and volumetric calculations, in which a variety of functions and operators can be used, it is much more reliable to simply copy and transfer the formula than to re-compose the expression.
Absolute link
As already mentioned, absolute and relative links in Excel serve different purposes. The second type of links that we are considering today is designed to preserve the addressing of any changes and movements of the formula.
- Suppose you need to solve some physical problem. You have a certain constant written in cell A1. Of course, it would be possible to write this number manually each time, but it is easier to write it in one specific place. We are also given two columns of data (M1, M2, P1, P2). In cells M3 and P3, it is necessary to calculate the following formula: M3 = (M1 + M2) * $ A $ 1. Now, when you drag and copy to cell P3, you get the following result P3 = (P1 + P2) * $ A $ 1. That is, the address of the used cell will not move.
To make the link address absolute, click on the formula, select the necessary "link" and press F4. Or manually type in the dollar signs.
Mixed
This is the last type found in formulas. As the name implies, this is an absolute and relative link at the same time. It is easy to guess that in practice it is denoted as $ A1 or A $ 1. Thus, this allows you to save the addressing to a column or row, but to continue to "glide" over it. Consider an example.
So, we have a table with some data. In column "A" we have a certain quantity, and in row "1", the coefficient by which we must multiply. Cells B2, C3 will contain the results. As you can see, absolute and relative link are powerless here. In such cases, you must use a mixed type.
We write the formula in the first cell B2 = A2 * B1. But to drag it to the right, you need to pin column “A”, and to down, pin column “1”. Therefore, the correct way to write the formula would be B2 = $ A2 * B $ 1. Thus, when dragging the formula, we get a series of values in each of the remaining cells:
- B3 = $ A3 * B $ 1
- C2 = $ A2 * C $ 1
- C3 = $ A3 * C $ 1
As you can see, absolute and relative link would not help here. Therefore, in complex calculations, it is necessary to use an exclusively mixed type. This will allow you to scale the table painlessly and add rows and columns to it. The only thing when working with these types of addressing is always to be very careful, because if you make a mistake once in the table with the mixed link used, then finding the flaw will be very difficult.
That's all. We hope that now you will better understand what an absolute and relative link is.