Relative vs. Absolute References in Formulas [spreadcheats]

Posted on November 4th, 2008 in Learn Excel - 22 comments

This is the first installment of the spreadcheats series.

I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the dirty work for you.

What is a reference?

A reference in excel lingo “identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look for the values or data you want to use in a formula.”

So what is the difference between relative and absolute references?

When you say a reference is relative, you are telling excel to adjust that reference in formulas based on where you move or copy the formula. For eg. if you have a formula in cell B1 as =a1*2 and now if you copy paste this in another cell, lets say, C1, the new formula would read like =b1*2

absolute cell referencesWhen you say a reference is absolute, you are telling excel not to adjust that reference in formulas when you move or copy them.

Switching between relative and absolute references:

while editing the formula you can use F4 function key to change the reference of a cell on which cursor is focused. By pressing F4, excel switches the references between relative (A2), absolute ($A$2), relative column & absolute row (A$2) and absolute column & relative row ($A2).

Understanding relative & absolute references plays a key role in writing effective spreadsheet formulas.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

22 Responses to “Relative vs. Absolute References in Formulas [spreadcheats]”

  1. Worm says:

    I’m quite happy with absolute/relative references – but I never knew about the F4 shortcut – very handy!

  2. Chandoo says:

    Thanks for your comments. :)

  3. […] Relative vs. Absolute References in Formulas […]

  4. […] that way excel converts your formula to array formula and the COUNTIF(C$3:C$9;C3:C9) will return an array of counts instead of one value. Now you can also guess why we have absolute reference for one parameter of countif () and relative reference for another. Learn more about Absolute and Relative References in excel formulas. […]

  5. […] most important advantage of tables is that, you can write meaningful looking formulas instead of using cell references. When you create and name the table (you can name the table from design tab), you can write […]

  6. Eric says:

    Hi Chandoo,
    I have a problem with my Vlookup – it only works partially and I need to re-do the vlookup formula every 40-50 rows to get the results. Is this an Excel bug or am i missing something? Very strange as I’ve never had this probelm before.

    Thanks in advance for your help!

    regards,
    Eric

  7. […] 8: Write better formulas by knowing the difference between relative and absolute references […]

  8. Sidhesh Mangle says:

    Sorry bymistake i added Eric’s name :(

    Tht Was my reply to Eric

  9. Sidhesh Mangle says:

    Make sure Formula Calculation Mode is Automatic.
    Excel 2007 – Formula / Calculation = Automatic

  10. Neeraj says:

    Hi Chandoo,
    Great to hear your amazing out of the box story.Keep it up.Great website.I use a lot of excel in my profession.I would definitely look forward to asking certain questions.

  11. […] Now you will see a bunch of TRUE and FALSE values. TRUE means the corresponding month’s sales is an all time high. Tip: See how we are using $C$6:C6 in the MAX. This style of referencing is called as mixed cell referencing. By using this, when you fill the formula for remaining months, the range inside MAX grows. Thus for each month we get the maximum sales value thus far. more ». […]

  12. john says:

    this is really awesome!! best help ever ever ever

  13. […] Sum function uses an array as an input. If used intelligently – starting element of the array with fixed reference and the second part of the array as moving, it can help you get a running cumulative value. [read more: Relative & Absolute References in Excel Formulas] […]

  14. leigh says:

    i encountered this formula in one of my ex-colleague’s excel file- =VLOOKUP([@SN],TBLInv,3,FALSE) .. how do i get the lookup value to be in that format- @SN as ooposed to the normal say for example, A2? .. thanks

  15. jrt says:

    Hi Chandoo,

    Thanks for “JoinAndMerge() macro”.

    Req. for Macro
    Help me in deleting 2nd and subsequent duplicates values range(“a1:a200″). Values to be deleted and rows and columns should remain constant.

    Thank U. 

  16. Brian Wilson says:

    I have a spreadsheet that uses VLOOKUP() and my boss keeps making changes by opening the spreadsheet in Excel and duplicating the file using the “Save As…” feature.  My formulas start out and look like ‘=IF(C6=””,””,VLOOKUP(C6,Contact_List,2,FALSE))’  When the spreadsheet is duplicated using “Save As…”, Excel automatically changes the formula and prepends the new filename to the Contact_List (i.e. ‘=IF(C6=””,””,VLOOKUP(C6,’file_name’!Contact_List,2,FALSE))’).  This causes the formulas to fail when someone copies or renames the file using a regular windows copy.

    Is there a way to stop Excel from prepending, or a way to specify to always use the current workbook?

  17. Brian Wilson says:

    I just tried File, Options, Advanced,  and unchecked the “Update links to other documents” setting in Excel 2010.  This seemed to prevent the change in two “Save As…” tests; but I hate having to make this kind of change to prevent the issue.

  18. […] F4: Switch reference styles – Absolute > Mixed > Relative > Absolute. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. You can use F4 key to do the switching. Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. (more: Guide to Excel cell references and when to use what?) […]

Leave a Reply