How to Use Relative and Absolute References in Excel Formulas and Functions

22
33620
How to use relative and absolute references in excel formulas and functions
Excel Spreadsheets

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:

How to use relative and absolute references in excel formulas and functions

Note that when copied, it automatically adjusts the formula.

How to use relative and absolute references in excel formulas and functions

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

How to use relative and absolute references in excel formulas and functions

Now notice that by copying my formula to the line below, it will not work correctly.

How to use relative and absolute references in excel formulas and functions

With this formula, Excel automatically references the cell below the correct cell (C2), giving an incorrect result:

How to use relative and absolute references in excel formulas and functions

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.

How to use relative and absolute references in excel formulas and functions

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.

How to use relative and absolute references in excel formulas and functions

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:

How to use relative and absolute references in excel formulas and functions

Now, when copying and pasting the column and the row, Excel will correctly change the values.

How to use relative and absolute references in excel formulas and functions

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.

Excel Spreadsheets
previous articleIntroduction to VBA in Excel
Next articleMeet the 10 best add-ins for Excel
Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

22 COMMENTS

  1. A1 - without references
    $ A1 - relative reference for rows and absolute for columns

  2. in case I am interested in maintaining a relative reference for rows and absolute for columns how can I do it?

  3. I wonder if it is possible to pull a die on an XLS from another XLS that is a relative folder behind the original XLS. Instead of putting for example D: \ bancodedados.xls in the function that is in XLS in D: / Work / test.xls, could be used something like ../bancodedados.xls. So when you move the set bancodedados.xls and teste.xls, you would not have to correct the functions for the new location of bancodedados.xls. I know that editing links solves but that would be more automatic.

  4. Hi Carlos, you need to explain a little better what you want to do. In principle INDIRETO is an excellent alternative, but it would be necessary to know more of what you want to do to try to help

  5. Hi guys.
    I wonder if you have any formula for this case, see:

    I want to put a formula (whatever) in one cell and leave it as a reference to the others. So, if I am to change it, I change only this formula (which I left as a reference) and all the others, which I left "linked" to it, if it changes automatically.

    I thought about the INDIRECT formula but I still could not find a way to do that ...
    If you can help me, thank you in advance.

  6. Hi Valeska, you're correct, it was a mistake to order the same explanations. Already corrected, thanks for the feedback! =]

  7. Rafael, that's incorrect. Notice that the two definitions are the same, they only change the orders of the terms.
    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 column will change. The line will remain unchanged.

  8. Flávio, did you solve it? I had to do the biggest job here because of it.

  9. I created a list tab where in one column I place the item number and in the other I place the description of the item. I would like that in the control tab when the item number was selected through the drop-down list the next column was filled with your description, is it possible to do this?

  10. You can create a tab in the spreadsheet you want and paste the values ​​of that tab special. In that case, you should paste just the formatting and formula if I'm not mistaken

  11. Guys, I'm making a tab that should work for all the spreadsheets I move. However, when creating a copy of the tab for another worksheet, the reference that should be "AJA1 '!" A1 "turns" =' [Project Reuse v0.1.xlsm] Aba1 '! A1 ".
    Would there be any way for me to move the flap and continue with the static reference? THX

  12. Thanks for the tips, it worked perfectly on my spreadsheet and it helped me to better understand this concept. Congratulations and all the best to you. ABC

  13. Hi Flávio, you can transform the table into a normal range, so you can use the $ normally

  14. DOES SOMEONE KNOW HOW TO USE ABSOLUTE VALUES IN FORMULAS WITH TABLES (STRUCTURED REFERENCES)?

    EX: = SOMASES (Table6 [Unit], Table6 [Vehicle Type], $ H7, Table6 [[WEEK]], L $ 5, Table6 [[TURN]], $ I $ 3)

    IN CASE ABOVE ALL THE FIELDS THAT ARE EITHER FROM THE TABLE ARE FIXED.

  15. In both cases does the column change and the line remains unchanged? This setting is not correct.

  16. Hi Renato, I do not understand which part is wrong in our post, can be a bit more clear?

  17. 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 column will change. The line will remain unchanged.

    Please correct.

LEAVE AN ANSWER

Please, write your comment
Please enter your name