Related references, absolute references e mixed references they are a fundamental element of Excel. In fact, they represent that set of references without which the program would not know where to go to retrieve information that he needs.
Understanding what they are is the first necessary step to learning how to use them correctly. Then you just need to open a spreadsheet and start calculating the formula that you need.
-
1. What are references in Excel
Before going into the merits of how to use references relativethe references absolute and references loose, it will certainly be useful to explain what they are. References or references are a fundamental element for the correct execution of different formula o Excel functions. In fact, they allow you to attach the software to one or more cellsfrom which to obtain specific information.
The default settings of Excel predict that the so-called related references. This means that the software takes as reference some relative positions as far as its formulas are concerned.
For example, imagine typing any formula inside cell D1. And to select cell D2 as a reference. Excel relative references require the software to take information from the cell below the one where the formula is being typed.
So if the user had to copy and paste the formula in any other cell, the relative reference would find information in one different space than the original. In the above case, the cell below the one into which the formula is copied.
The absolute references instead they latch onto specific cells, which are not changed regardless of the position of the formula. Returning to the example above, absolute references would cause the formula to fish for information only and exclusively from the reference cell D2.
Finally, Excel also provides for the presence of mixed references, or mixed references. In this case it is simply one combination of absolute references and relative references.
-
2. How to use relative references in Excel
As previously mentioned, the related references are the’default setting of Excel. Therefore, to use them, all the user has to do is start setting any one formula within the spreadsheet.
Take one of the examples basic formulas most appreciated of all which the sumwhich can be enabled by typing the following inside a cell:
=SUM(***)
Inside the parenthesis you need to insert referencesor the cells containing the digits to be added. This way you will get an output like this:
=SOMMA(D1+D2)
Being about related references, you must pay close attention to the cell in which the formula is written. If the user writes the formula above in cell D1Excel will add the digits in that cell and the one below it.
The relevant references will take into account this relationship between the cell location where the formula is written and the one below even if the user copies and pastes the formula elsewhere.
For example, by copying and pasting the formula above in cell C2Excel will actually perform a sum between the digits contained in the cells C2 e C3 (i.e. the cell below C2).
-
3. How to use absolute references and mixed references in Excel
The procedure to follow for use absolute references in Excel it is conceptually identical to the one to follow for related references, already explained in the previous paragraphs. With a difference fundamental.
The user must remember report to Excel to use absolute and not relative references. To do it he must add the dollar sign $ before each element of the formula to be used.
Going back to the example above, to make the formula use absolute and not relative referencesyou need to insert an output like this into the cell:
=SUM($D$1+$D$2)
In case the user should copy and paste the formula above in any other cell, Excel will continue to perform a sum that takes into account the absolute references: that is, the figures contained within cells D1 and D2.
However, there are some cases in which relative and absolute references are not sufficient for the correct execution of a formula. There are also cases in which the need arises alternate the two types of reference. And in these cases we talk about mixed references.
A classic example of hidden reference predicts what the user wants freeze a row or column of the spreadsheet. However, maintaining the relative reference as far as the put away the one to keep in mind. A sometimes necessary prerequisite for create a graph.
The technical difficulty of this operation is almost nothing. In fact, all you have to do is remember use the dollar sign $ in conjunction with all relevant references.
The main obstacle of mixed references if anything it is natural conceptual. In the sense that it’s not always simple set the formulas correctlytaking into account which elements must be absolute and which must be relative.
-
4. How to switch from relative references to absolute references and vice versa
A final aspect to consider concerns the difficulty (or at least the inconvenience) to calculate manually the dollar sign every time you want to switch from relative reference to absolute reference.
Excel provides shortcuts (keyboard shortcuts) which reduce the user’s effort in this regard. For example, simply press the F4 keyafter typing the first cell reference.
Likewise it is also possible to switch more quickly from relative to absolute references or mixed ones. To do this you just need to do click on the cell on which you want to intervene
Then you have to bring the cursor on the bar in which the formula Excel. Then you have to press again F4 keyto change the reference type of the cell, row or column.
Read more: What is Excel and how to use spreadsheets online