When working with formulas in the Microsoft Excel program, users have to operate with reference to other cells located in the document. But, not every user knows that these references are two species: absolute and relative. Let's find out what they differ from each other and how to create a link of the desired type.
Determination of absolute and relative links
What are absolute and relative links in Excele?Absolute links are links, when copying which cell coordinates do not change, are in a fixed state. In relative references, the coordinates of the cells are changed when copying, relative to other sheet cells.
An example of a relative reference
Let's show how it works on the example. Take a table that contains the number and price of various product items. We need to calculate the cost.
This is done by a simple multiplication of the amount (column b) on the price (column C). For example, for the first name of the product, the formula will look so "= B2 * C2". Enter it to the appropriate table of the table.
Now, in order manually, do not drive the cell formulas that are located below, simply copy this formula to the entire column. We become on the bottom right edge of the cells with the formula, click the left mouse button, and when the button is squeezed, the pull the mouse down. Thus, the formula will also be copied to other cells of the table.
But, as we see, the formula in the lower cell does not look "= B2 * C2", but "= B3 * C3". Accordingly, those formulas that are located below have changed. This is the property of change when copying and have relative links.
Error in the relative link
But, not in all cases, we need relative links. For example, we need to calculate the specific value of the value of each product name from the total amount. This is done by dividing the cost for a total amount. For example, to calculate the share of potatoes, we are its cost (D2) divide for a total amount (D7). We obtain the following formula: "= d2 / d7".
In the event that we try to copy the formula to other lines in the same way as the previous time, then we get a completely dissatisfaction result. As we can see, in the second line of the formula table, it has the form "= d3 / d8", that is, not only a link to a cell with a line by line, but also a link to a cell responsible for a general result.
D8 is a completely empty cell, so the formula and gives an error. Accordingly, the formula in the string below will refer to the D9 cell, etc. It is also necessary that when copying the link to the D7 cell is constantly maintained, where the total amount is located, and this property has absolute links.
Creating an absolute link
Thus, for our example, the divider must be a relative reference, and change in each line of the table, and the dividend must be an absolute reference, which is constantly referenced by one cell.
With the creation of relative links, users will not have problems, since all references to Microsoft Excel are relative to the default. But if you need to make an absolute link, you have to apply one reception.
After the formula is entered, simply put in the cell, or in the formula row, in front of the coordinates of the column and lines of the cell to which the absolute link should be made, the dollar sign. You can also, immediately after entering the address, press the F7 function key immediately, and the dollar signs before the coordinates of the string and column will be displayed automatically. The formula in the uppermost cell will take this kind: "= d2 / $ d $ 7".
Copy the formula down the column. As you can see, this time everything turned out. In cells are correct values. For example, in the second line of the formula table looks like "= D3 / $ D $ 7", that is, the divider has changed, and the divisible remains unchanged.
Mixed links
In addition to typical absolute and relative references, there are so-called mixed links. In them, one of the components varies, and the second fixed. For example, at the mixed reference $ D7, the line changes, and the column is fixed. Reference D $ 7, on the contrary, the column changes, but the line has an absolute value.
As we can see, when working with formulas in the Microsoft Excel program, you have to work with both relative and absolute links to perform various tasks. In some cases, mixed links are also used. Therefore, the user even the average should clearly understand the difference between them, and be able to use these instruments.