To create any formula in Excel, it is absolutely necessary to understand what are relative and absolute references. This is a feature that best helps to manipulate certain formula. Basically, it indicates what should be changed or maintained when copying the formula.
A relative reference is the most common functionality, used by default in the formula. When the formula is copied, the formula is automatically redone based on logic. Look:
This is the original formula:
Note that when copied, it automatically adjusts the formula.
Tip: You can copy and paste using the Crtl + C keys e Ctrl + V or by means of the fill handle.
Already the absolute references is the functionality that indicates which formula particles should not be changed when copying and pasting. They remain as initially defined and are represented by the dollar sign ($).
To illustrate this concept, we will use a table that automatically converts the values of the products that are in US dollars to Real.
The formula used is ok. It takes the dollar value of the cell referring to the smartphone product and multiplies by the dollar quotation in January. This will get the value of the product in the Real currency. In the example used, the formula should look like this:
= B6 * B2
Now notice that by copying my formula to the line below, it will not work correctly.
With this formula, Excel automatically references the cell below the correct cell (C2), giving an incorrect result:
In order for the formula to be copied correctly to the other columns, an absolute reference to the Dollar value in Real (C2, in the example case) must be made. To do this, place the cursor on the value corresponding to C2 in the formula and press F4. A dollar sign will appear in the corresponding value.
By copying and pasting the formula, the value for the dollar product price will be updated (in the example for B7), but the dollar value will remain in January (C2), and will not be moved to the row below.
However, if this formula is copied to the next cell, the value will be copied incorrectly. Therefore, we will have to apply absolute and mixed references in the formula.
Before proceeding with formula creation, it is necessary to know that there are other types of absolute references. To apply them, simply press the F4 key until the desired absolute value is reached. Check out what types of references Excel has.
A1: Relative References. When copying, both column and row values will be updated.
A $ 1: Mixed Reference. Absolute row and relative column. When copying, the line will remain unchanged, however the column will change.
$ A1: Mixed reference. Relative row and absolute column. When copying, only the line will change. The column will remain unchanged.
$ A $ 1: Absolute reference. Absolute row and column. When copying, both the row and column remain unchanged.
Based on this information, we will change the formula so that we can copy this formula to the other rows and columns of the table. The initial formula should look like this:
Now, when copying and pasting the column and the row, Excel will correctly change the values.
See how simple it is to work with absolute references and relative references? This way it is easier to create and apply formulas. If you have more questions, check out the other posts with tips on Microsoft Excel.