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 3. Total comments: 98

  1. 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.

  2. 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.

  3. Thank you so much! Very helpful

  4. 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

  5. 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?

  6. 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?

  7. powerful article, i enjoyed reading

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

  9. 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.

  10. 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

  11. 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.

  12. 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.

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

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

  15. thank you for your wonderful services

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

  17. 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

  18. 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

  19. 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

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

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

  22. Realy thanks to you

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

  24. 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

  25. 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/

  26. 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.

  27. appreciate it. very useful.

  28. 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.

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

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

  31. what is featurs of cell reference and advantage and diadvantage of cell reference..plz give me ans..as soon as

  32. what is the short cut of $ sign

      1. Dear Svetlana,

        Sometimes people are not reading full blog and asking question...... :-)

  33. I AM GOING TO PREPARE A WORKBOOK IN EXCEL HAVING MANY EXCEL SHEETS OF VARIOUS STOCK IN AND OUT RECORD AND NET FIGURE IN LAST COLUMN OF THE SHEET WHICH CHANGE THE FIGURE IN NEXT CELL OF THE COLUMN WHEN A UPDATE IS DONE IN THE SHEET.
    I AM ALSO GOING TO MAKE A SUMMURY SHEET IN THE SAME WORK BOOK HAVING HORIZONTALY NAME OF EACH ITEM OR EACH MATERIAL AND NEED TO SHOW THE NET RESULT OF EACH MATERIAL SHEET IN FRONT OF EACH ITEM WHEN EVER CHANGE IS MADE IN ANY OF THE MATERIAL SHEET.

    KINDLY ADVISE ME THE FORMULA TO BE PUT IN A CELL AGAINST THE EACH ITEM IN SUMMURY SHEET TO PICK THE RESULT FROM THE RELATIVE SHEETS.

    THANKS AND REGARDS

  34. Nice report. Congratulations !
    Sometimes I use long formulas that include absolute cell references associated with relative cell references. When copying such formulas for dozens of novel columns, I have to individually change the absolute cell column identification of each formula, which is time consuming. Is there any general way to push Excel to assume that the absolute cell reference of a given formula corresponds to that of the column the formula is located?
    Thankyou.
    Best regards,
    Dr. Gilberto Xavier, University of São Paulo, Brasil

    1. Hello Gilberto,

      Nope, there is no such way. This is the most essential difference between the relative and absolute references. An absolute reference is locked to the particular column and row, while a relative reference adjusts to the column and row where the formula is located.

      It's difficult to give any advice without seeing your data, but maybe it would make sense to use absolute row and relative column references in your formula for the column reference to change depending on the column to which the formula is copied.

  35. Hi Sirji,
    I want to find out the highest marks in the entire sheet for each code separately
    formula put in All marks columns

    code marks code marks code marks code marks code marks
    301 90 30 35 85 99 65 45 301 76
    40 92 41 65 30 85 41 36 37 85
    41 85 301 22 41 92 55 85 55 70
    30 35 48 25 301 45 40 77 30 55

    1. First you type the list in the following manner or you can paste it in this style by your origin table, because of computers works then when you give it (computer) perfect quary status.
      code marks
      301 90
      40 92
      41 85
      30 35
      30 35
      41 65
      301 22
      48 25
      85 99
      30 85
      41 92
      301 45
      65 45
      41 36
      55 85
      40 77
      301 76
      37 85
      55 70
      30 55
      After it select this table and press Ctrl + L (it helps you to convert it in table style format), then go to design menu (which would be appear after fomat apply by press Ctrl+L). There is in design menu a sub menu tool in which first tool is "Summarize with PivotTable", click it and by default it give you, your data result in next sheet and that sheet would be create by "Pivot Table". Now Press Ok. Step 1 - Drag "Code" into "Row Label". Step 2 - Drag "Marks" into "Values" (when you draged Marks into Value it would be convert into "sum of Marks") Step 3- Click on "Sum of Marks", then click on value field setting, now click on max (instead of "sum"). Here you are find your result that is following :-
      30 85
      37 85
      40 92
      41 92
      48 25
      55 85
      65 45
      85 99
      301 90

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 :)