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
When 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.
30 Responses to “Relative vs. Absolute References in Formulas [spreadcheats]”
Thanks for this tip
I'm quite happy with absolute/relative references - but I never knew about the F4 shortcut - very handy!
[...] how we are using both absolute and relative references to fetch the [...]
Thanks for your comments. 🙂
[...] Relative vs. Absolute References in Formulas [...]
[...] 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. [...]
[...] 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 [...]
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
@Eric.. can you post the formula for us? I am not able to visualize this.
[...] 8: Write better formulas by knowing the difference between relative and absolute references [...]
Sorry bymistake i added Eric's name 🙁
Tht Was my reply to Eric
Make sure Formula Calculation Mode is Automatic.
Excel 2007 - Formula / Calculation = Automatic
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.
[...] 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 ». [...]
this is really awesome!! best help ever ever ever
[...] 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] [...]
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
[...] Understanding Relative & Absolute References in Excel [...]
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.
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?
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.
[...] 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?) [...]
plz post some more diff.........
@Anu
What are you asking ?
Yh what is the advantage of a relative reference?
When you drag the formula to another column, row, Excel will adjust all cell relative references. For example, if you have "=b2+b3" in column c2, if you copy this formula to d3 it will become "=c3+c4". If the formula was "=$b2+$b3" then it would become "=$b3+$b4". If the formula was "=$b$2+$b$3" it would remain the same whichever cell you put it in.
So the advantage it where you have a formula in on column that sums (a simple example) the contents of the adjacent cells, you can create the formula in the top cell then just copy it down and it will adjust the references accordingly.
[…] the $ symbol? The dollar sign is Excel’s symbol for absolute (and relative) references and are used to fix the row or column reference that follows it—here the G column. (If I were in […]
How do you create absolute references to table data?
Hi,
I apologize you for the stupid question but I cannot understand what do you mean under "relative column & absolute row (A$2) and absolute column & relative row ($A2)". I tried the examples above but my F4 keys does not work. When I push the F4 key there appear some Project suggestion from the right side of the screen.
I would be happy if you could help me somehow!
Mariya