Relative vs. Absolute References in Formulas [spreadcheats]
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 autofill 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.
Signup for our FREE Excel tips newsletter:
Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please signup below:
Your email address is safe with us. Our policies
 

Leave a Reply
Minor Changes to PHD’s Look and Feel  US Poll Trackers – Live Dashboards Ooze Visualization Goodness 
22 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 redo the vlookup formula every 4050 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 excolleague’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?) […]