Comments on: Why use dollar sign ($) in Excel formulas: absolute & relative cell references

When writing an Excel formula, $ sign in cell references confuses many users. But the explanation is very simple. The dollar sign in an Excel cell reference serves just one purpose - it tells Excel whether to change or not to change the reference when the formula is copied to other cells. And this short tutorial provides full details about this great feature. Continue reading →

Comments page 2. Total comments: 98

  1. To do this you will need to calculate the increase in expenditure (current total expenditure multiplied by the percentage increase) and add it to the current total expenditure. Copy the formula down the column. (Make sure that all the calculations are using the value in Z2!).

  2. Hello, I have 2 workbooks - WB1 and WB2. In WB2 I want to reference a cell in WB1 to copy the data from that cell over. However if a new line is added to WB1 it loses the reference data. Should I be using $ symbols in my formula reference or not for WB2 to follow the movement of cell in WB1 if lines are added?

    Thank you for your help

  3. there are 2 sheets in a excel file and i wrote a formula below. can i simplify that?

    Cn!$G9*Pro!K$6+Cn!$H9*Pro!I$7+Cn!$I9*Pro!I$8+Cn!$J9*Pro!I$9+Cn!$K9*Pro!I$10+Cn!$L9*Pro!I$11+Cn!$M9*Pro!I$12+Cn!$N9*Pro!I$13+Cn!$O9*Pro!I$14

  4. Please see the Table below
    Sheet 1
    A B C D E F G H
    1 2 3 4 5 6 7 8
    Sheet 2
    A
    1
    2
    3
    4
    5
    6
    7
    8

    the formula will be

    Sheet A1*Sheet 1A1+Sheet 1A2* sheet 2B1............

    how can i do this formula easily.

  5. *Awesome article* - it fixed my problem of Excel auto-incrementing my formulas.

    On a separate note, I found out that auto-incrementing (aka autofill) does not work if you have filtered your column(s). I filtered to exclude every other row and couldn't get autofill to work. Took off the filter, selected 4 rows (2 rows I didn't want and 2 that I did), dragged the autofill down and it worked.

  6. Thanks for the info

  7. Hi. First thanks for ur excellent lesson here.
    I have a workbook with a few sheets, one sheet is called "day to day" where i enter all my expenses, each expense is entered separately on there own row (like a bank register). And each column is meant for there thing, for example: in column A i write the date when the expense was, in column B i write the amount, then in column D i write the type of expense, like "insurance", "gas", etc..
    Now on a different sheet i have a table that calculates the total of each type of expense, every year, it looks something like this:
    A B C
    1 EXPENSSES 2017 2018
    2 Insurance $ - $ -
    3 Maintenance $ - $ -

    I entered in cell 'C1' this,
    =SUMIFS('Day To Day'!B:B,'Day To Day'!A:A,">="&DATE(2018, 1, 1),'Day To Day'!A:A,"<"&DATE(2019, 1, 1),'Day To Day'!D:D,"Insurance")
    it gives me the total how much i paid for insurance in the year of 2018.

    Now when i want to add to the table for the next year, cell D2 should be for 2019, and i try to move with the fill handle (the small cross) from C2 to D2, the date doesn't change, even though i didn't put a $ sign by the date, so what correction do i need to get this done?

  8. WHICH OF THE FOLLOWING FORMULAS PROPERLY PRODUCES THE STRING "INCOME STATEMENT"?

  9. WHICH OF THE FOLLOWING FORMULAS CORRECTLY ANCHORS ONLY THE ROW IN CELL A1?
    1. A1
    2.A $1
    3.$A $ 1
    4.$A1

    PLEASE GIVE ME

  10. Excellent working
    zia

  11. it is really helpful. this help a lot, how can I follow your regular post/updates?

  12. Hi Svetlana, I have an interesting dilema and hopeing you can point me in the right direction. In column A, i have a list of various items, lets call them Cars and Trucks. In column B I have a list of hours associated with each item in Column A. I now want to do a sum of hours based on items in Column A...pretty stratight forward with something like =SUMIF($A$1:$A$1000,"Car",$B$1:$B$1000). But now I want to use another Cell to determine the row number to use in my range...so something like $A:$(value obtained from input) Any ideas?

  13. The strike through functions is not worked when referenced with the previous tab. Can you help me to clear this please.

  14. Hi
    What does this mean in Excel

    =VLOOKUP(B12,'file1.xlsb]Sheet1'!$A:$J,10,FALSE

    Especially !$A:$J,10,FALSE

    1. Alex:
      This tells Excel to lookup the value in cell B12 in the file1 xlsb workbook that matches the value on Sheet 1 in the range A:J, is in the 10th column from the left and it doesn't have to be an exact match.
      The exclamation point signifies the Sheet1 is a sheet in the file1.xlsb workbook.

      1. Thanks so much for this explaination.

        Can tyhis be simplified at all ? as the file1.xlsb only has one sheet in there anyway ?

        Cheers
        Alex

  15. Thank you very much for this review.

    Your explanations are simple and straight to the point. You make a great job in making them applicable, and the cases that you use as examples just sink the concepts in.

    So thanks once more.

  16. Hi all, need a quick help here!
    Supposedly I understand excel default is relative cell reference. But when I copy over a formula, it's not updating the references of the new columns/rows.

    Eg. formula =[@[Dec 17]]*(1+E3)

    Note: @[Dec 17] is referencing to a cell under the header of "Dec 17".
    When the cell is selected, the formula shows @[Dec 17] instead of the cell location G2 (which is what I want).

    How do I fix it?

    1. I think that's conditional formatting

  17. In sheet 1:
    Column A is ‘ITEM’, column B is ‘Balance Count’
    Row A2 is RR1
    Row A3 is RR2
    .
    .
    .
    Row An is RRn

    In sheet 2:
    Column A is ‘ITEM’, Column B is ‘Inward Qty’, Column C is ‘Issued Qty’, Column D is ‘Date’ and Column E is ‘Balance Count’.

    Row A2 is RR1
    Equation for ‘Balance Count' E2 is =(B2-C2)
    Equation for ‘Balance Count' E3 to En is =IF(AND(ISBLANK(B3),ISBLANK(C3))," ",(E2+B3-C3))

    My Question is
    Balance Count B2 in sheet1 should be updated whenever the last Balance Count against RR1 (E2:En) is modified in sheet 2

  18. dear,
    hi i created one sheet, Item Name,Pack Size, price of items, different parties Sales, Bonus, Sales Bonus, Sales Bonus, Sales Bonus, Sales Bonus, Sales Bonus, Sales Bonus, Sales Bonus, for the whole month same sales and Bonus columns a daily basis will be 8 orders i want total sales items and bonus sepreated kindly help me in this problem

  19. In case others want to know the solution, it's =$D4+$B5-$C5 where column D is income, column B is expense, and column C is current balance. The only problem is that when pasting the formula all that way down column C, it lists the value in the last row of data entered all the way. I'm sure there's a solution for this and will search for it.

  20. No need to reply. I just played with what I learned from your tutorial and made the formula straight away! Thanks again!

  21. Thank you so much for your excellent tutorial. Can you tell me how to make a formula that references the output of a previously calculated formula and keeps changing row by row? I want to make a budget with a running balance that changes on each new row, sort of like C3=C2-B3, followed by C4=C3-B4. Many thanks.

  22. I have a column in one sheet where I want data to be populated. In my other sheet I have the data that is to be populated which is plotted in one row.

    What I want is that when I pull down the cell formula from A2 to A3 from the first sheet it should pick up the value in the next cell in the next column in in the same row in the second sheet ie from A2 to B2.

    Is it possible?

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =INDEX(Sheet2!$2:$2,1,ROW(A1))

      Hope it will help you.

  23. Thank you so much! Very helpful

  24. got stuck in a formula in my thesis.now i got a clear understanding about $ sign.well written.actually description with figures helped a lot.thanks miss

  25. Hi

    i am working on 2 sheets in a workbook, SheetA and SheetB
    i am using cell reference from Sheet B in Sheet A.

    I want something like below when i drag down:
    A1 of SheetA is from D1 of SheetB.
    A2 of SheetA should be D3 of SheetB.
    A3 of SheetA should be D5 of SheetB.
    A4 of SheetA should be D7 of SheetB.

    Any Help Please?

  26. Your articles are excellent and I usually find a fix to an issue. If I am bringing in the contents from a cell in another spreadsheet, it makes it absolute. if I want to drag that down it takes the absolute with it. If I remove the absolute I can then drag it down and get what I want - relative numbers but, I then want to make them absolute. Is there a quick way of making them all absolute?

  27. powerful article, i enjoyed reading

  28. Very nicely articulated the use of $ in Excel and covered every aspect. Thanks a million

  29. What is the equivalent of $ a table? In a table it shows like table[X]

    1. Hi Ignacio,

      When you create a table, Excel automatically assigns a name to it, as well as to each column in the table. Those names appear in a formula if you select the cell references in the table instead of entering them manually. And that combination of table and column names is called a structured reference.

      To make a structured references absolute, you need to duplicate the reference as if it were a range of multiple columns. For example:

      Table1[[Column1]:[Column1]] will be locked to Columnn1.

      Please note that to copy such formulas, you must drag them across columns using the mouse.

      For more information, please see Excel Table Absolute Structured References.

  30. I am printing coupons for a monthly buyer. I want to print June in the first coupon, and have Excel print the next months (July, Aug, Sept) in the next 12 coupons. ??
    Windows 10

  31. How do I make absolute cell references to items in a named range?

    For example: create a named range, called Phone using A1:a128, make sure the cell references are absolute.

    1. Hi Jo,

      Named range references are absolute by their nature. In other words, no matter where you use or copy a formula referencing your "Phone" range, it will always refer to cells A1:A128.

  32. Thank you very much for this informative post ,ALLAH solved my problem from this post.

    1. it was Jesus boi. But don't be afraid - everything is good. God i love. Peace.

  33. Hello Sir,
    $ fix column E1, E2, E3,
    If i want to fix row E1, F1, G1.
    Thank you

  34. How can I change Excel to default to relative cell references (instead of absoulte references it currently defaults to)?

  35. thank you for your wonderful services

  36. How can i switch between relative at the one time shot in whole excel sheet?

  37. I am using Excel online to create a checkbook. I have tried to use absolute reference formulas, but the system doesn't save the formula. Anyone know if this can be done online

  38. How do you incorporate the absolute cell reference in a date? Instructions say to use an absolute reference to the date in cell C20 and the date is 10/14/2016 but nothing is telling me how to use the absolute cell reference with that

  39. Dont know if i can solve the problem i have with absolute cell reference. The problem is if i have a row of data (and i want it to refer to a column of data) even if i make a two cell formula to get back data from column, after dragging them both the form ends up bringing back row data. Thanks

  40. well written! This thing has confused me for many many years(even as an Engineer!) but you made it look sooo simple. Thanks

  41. hi
    it helped me very much but i have a question how do you make other cells fixed.

  42. Realy thanks to you

  43. Thank you
    You are a life saver
    It was good to know how to use f4 toggling to a non $ reference

  44. Hi Svetlana! thank you for an excellent article! I have a work sheet that I have all the equipment that needs yearly certification. if I assign cell G19 to be the date of last certification and H19 to be next due certification (which a year from the last certification)what would be my formula for H19. and also if it is less than a year the cell should be green and if it is over a year the cell should turn Red and if is within 10month of the certification it should turn yellow. the formula should be for entire H Column. I hope it make sense to you.

    thank you in advance

  45. What happens to "$"s that are not part of addresses? Any problems?

    1. Hi Gary,

      No problem at all. When $ is entered in a cell, it behaves like any other text character. However, if you want to use it as a currency symbol, do not type $ in a cell in front of a number, but apply the desired currency format to the cell. For more information about Excel number formats, please see this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-format-number-text/

  46. Thank you for this. I've been using excel formulas / macros for years. I even use absolute/relative references fairly often. For whatever reason they've always confused me and i get them switched around. Your article crystallized in my mind information I should have already had. It's embarrassing really but kudos for a well written article. :)

    1. Thank you, Chrisp. I am really pleased to know that my article has proved helpful.

  47. appreciate it. very useful.

  48. Is there a way to change the direction of formulas autofill - so once I copy a formula downwards in a column it'll gather data from the consecutive cell to the right (in the adjacent column) and not the consecutive cell down (in the same column)? In short i need to transpose a formula.

  49. Just wanted to thank you so much for the straightforward explanation on the cell references. It now makes total sense thanks to you!

  50. Very excellent article. Well understood. I'm very impressed. Thanks a million

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)