fbpx
Search
Close this search box.

Relative vs. Absolute References in Formulas [spreadcheats]

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

30 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?) [...]

  19. anu says:

    plz post some more diff.........

  20. Ice says:

    Yh what is the advantage of a relative reference?

    • Richard says:

      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.

      • Richard says:

        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.

  21. […] 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 […]

  22. Richard says:

    How do you create absolute references to table data?

  23. Mariya Parusheva says:

    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

Leave a Reply