Search Not Just Numbers

Loading...

Friday, 20 May 2011

EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

I want to cover something today that I use all of the time but seems to be understood in varying degrees by clients I work with.

I am talking about use of the dollar sign ($) in an Excel formula.

Relative cell references
When you copy and paste an Excel formula from one cell to another, the cell references change, relative to the new position:

EXAMPLE:

If we have the very simple formula "=A1" in cell B1 it will change as follows when copied and pasted:

Pasted to B2, it becomes "=A2"

Pasted to C2, it becomes "=B2"

Pasted to A2, it returns an error!

In each case it is changing the reference to refer to the cell one to the left on the same row as the cell that the formula is in, i.e. the same relative position that A1 was to the original formula.

The reason an error is returned when it is pasted into column A, is because there are no columns to the left of column A.

This behaviour is very useful and is what allows a sum to be copied across or down the page and automatically refer to the new column or row that it finds itself in.

But in some situations, you want some or all of the references to remain fixed when they are copied elsewhere.

The dollar sign ($)
This is where the dollar sign is used.

EXAMPLE:

Take an example where you have a column of Sales values in Pounds Sterling in column A and a formula to convert these into US Dollars in column B. You could enter the actual exchange rate into the formula but it would be more sensible to refer to a cell where the exchange rate is held, so that it can be updated whenever it is needed.


The simple formula for cell B2, would be "=A2*E1", however if you copy this down, then the formula in cell B3, would read "=A3*E2" as both references would move down a row as described above.

This is where the dollar sign ($) is used. The dollar sign allows you to fix either the row, the column or both on any cell reference, by preceding the column or row with the dollar sign. In our example if we replace the formula in cell B2 with "=A2*$E$1", then both the "E" and the "1" will remain fixed when the formula is copied. i.e. in cell B3, the formula will read "=A3*$E$1", still referring to the cell with the exchange rate in it.

In this example we have fixed both the row and the column, but in other situations, you may just want to fix one or the other, for example:


Above we have a spreadsheet calculating the times tables where we want to every cell in the white area to be the product of its row and column heading. This is easy using the dollar symbol. In cell B2, the formula without dollars would be "=A2*B1", but for this formula to work when copied to each column, we need it to always look at column A for the first reference and to work for each row, we need to always look at row 1 for the second. Using the dollar sign to do this, it becomes "=$A2*B$1". This can then be copied to every cell in the white area.

Quick Tip
You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

104 comments:

  1. Thanks for clear info about dollar sign in Excel.
    I have used this several times but this example has cleared some confusions.

    ReplyDelete
  2. Thanks for the clear explanation of usage of the $ sign in Excel. There are plenty of tutorials on the web but I found yours particularly clear and easy to understand. Nick, New Zealand

    ReplyDelete
  3. Thanks for the feedback, it's always nice to know that my posts have been helpful.

    ReplyDelete
    Replies
    1. thanks its really helpfull...can u also eplain about use of " : " in execl sheet?

      Delete
    2. thanks a lot. Now i know use of dollar symbol

      Delete
  4. if I want to make a page for commision calcualtions over a set figure how would I enter a formula that says: 5% of anything > 15,000.00

    ReplyDelete
  5. Anonymous

    If I've understood what you want, if the sales figure is in cell A1, then

    =(A1-15000)*0.05

    would do it.

    ReplyDelete
  6. Very helpful. Thanks!

    ReplyDelete
  7. thanks for helping, mate

    ReplyDelete
  8. I thought I would fix my problem with dollar sign, but it turns out I was wrong :) I need help...
    I have a cell in which I have sum of values: SUM(A10:A20) and I want that sum always like that, not to change in any case. But every time I take a value from cell for example A12 and put it higher in cell let's say A5, SUM changes to SUM(A5:A20).
    What should I do, so the SUM stays A10:A20 at all times?
    I hope you understand me :)
    Thanks, Neli from Slovenia

    ReplyDelete
    Replies
    1. i want to apply the same function to the row with the $
      but when i try to insert the function to a row
      the $ make the number to stack in the column
      it make the job for the row
      but the same dollar in the row rowing to apply the formula to a row after it been work great in the column

      Delete
    2. Thanks for explaining it in such simple manner!

      Delete
  9. THis is really nice - thanks guys!

    A good ref here too:

    Dollar sign in Excel

    ReplyDelete
  10. I have a spreadsheet which changes very day. All the columns from V onward move one space to the right. In column L I want to display the difference between column P, (which does not move) and column AF. Each day the values in column AF shift to the right (into column AG). I want my formula to show the difference between P and the new value in AF. With or without a $ the result shown in L is always the difference between P and the the old AF which is now AG. The next day when the columns move on the result in L becomes the difference between P and AH and so on. Not what I want. Any ideas about what I can do?

    ReplyDelete
    Replies
    1. It sounds like you are inserting columns for new data or cutting and pasting to make room for the new data. Both of these will change any formulae pointing at the cells to move with the data. If so, the simplest solution would be to copy (not cut)and paste to make room for the new data. Copy does not affect formulae referencing the cells. The new data can then be pasted over the old data and the formula will stay where it is.

      Alternatively, instead of referring directly to the cell, you could use offset to refer to the cell relative to a cell that is not going to move.

      I hope that helps.

      Delete
  11. Thanks very much for this. I am pretty sure that "offset" is the way to go but I am struggling to get it to work properly. In your examples you put a cell reference where I am trying to work with a number of cells difference. I hope I will be able to crack exactly how it works.

    ReplyDelete
    Replies
    1. If on row 2 your formula in L2 is =P2-AF2, then you can replace this with =P2-OFFSET(L2,0,20). You should then be able to copy that down. All you are doing is stating AF2 relative to L2. i.e. instead of saying AF2, you are saying the cell 20 columns to the right of L2.

      Does that make sense?

      Delete
    2. I've cracked it. I did it by counting columns and that worked. I've now updated my sheet for the day and it has kept the relationships I wanted. So thanks very much indeed. Terrific job.

      I keep my stock market portfolio on a spreadsheet and this will help me identify shareholdings that are beginning to flag.

      Delete
  12. thanks it was really helpful!

    ReplyDelete
  13. what do i press for the dollar sign

    ReplyDelete
    Replies
    1. As per the post:

      Quick Tip
      You can speed up entering the dollar signs by using the function key F4 when editing the formula, if the cursor is on a cell reference in the formula, repeatedly hitting the F4 key, toggles between no dollar signs, both dollar signs, just the row and just the column.

      Delete
  14. Hello sir.
    Im from Sri Lanka, your article really explained of using the dollar sign.
    Im doing an IT degree and exams starts day after tomorow.
    Thank you once again

    M. Sulaiman

    ReplyDelete
  15. What if I want to actually input a dollar amount into a function like "=B2*[$15]+B3*[$10]"?

    ReplyDelete
    Replies
    1. You would never put the dollar symbol in the formula for this purpose. Just put the actual numbers (15 and 10 in your example) and format the cell as Currency.

      Your formula should just read =B2*15+B3*10.

      Delete
  16. Hi Glen,
    Is it possible to replace a cell with the formula result?
    For example and apologies if this is very long winded!
    i have a list of prices in a table and i want them all to increase by 10% i think that the formula would be
    =B4($R$1)/100) and in R1 cell the amount 10 but i don't know how to replace the original cell with £5.01 to the new price of £5.11?

    Your assistance would be much appreciated!
    Regards
    Fiona

    ReplyDelete
    Replies
    1. Fiona

      First of all, a slight correction to the formula, you will need to put the asterisk (multiplication) symbol in so it reads:

      =B4*($R$1)/100

      The simplest way to do it is to add this function in a new column (say column C), so that column C now shows the calculated new prices. You can then copy column C and Paste Special -> As values, to paste the results into column B.

      The simplest way is to introduce a new columns

      Delete
  17. thanks much for the great explanation!
    -kas

    ReplyDelete
  18. V Sundararaman7 May 2013 16:04

    Thanks, your practical example was very useful. I was often confused with this. Could you suggest or give a few problems to solve

    ReplyDelete
  19. I have opened microsoft excel help menu several times to understand this function btu never did completely. this was so helpful. Thank You.

    ReplyDelete
  20. =VLOOKUP(A4,CTSCs!$A$3:$E$2068,2,FALSE)
    and
    =vlookup($A4,$A$3:$E$2068,2,FALSE)
    What is the difference betwwen these two and when we use it??????

    ReplyDelete
    Replies
    1. Dear Muhammad

      Both of those functions will do the same thing, however, if you copy the formula one cell to the right, the second version (with the $ on the A) will stay the same but the first version will now read:

      =VLOOKUP(B4,CTSCs!$A$3:$E$2068,2,FALSE)

      So if you want the formula to always look at column A for the lookup value (wherever you copy the formula to), then choose the second version (this is most likely).

      Alternatively, if you want the column that the formula gets the lookup value from to change relative to the position of the formula, then choose the first version.

      Obviously, if you are never going to copy the formula to other cells, it won't make a lot of difference!

      Delete
  21. in above i just want to know about $ sign which was used with in 2nd formula not in first one.

    ReplyDelete
  22. Thanks alot Sir i may need more attention regarding Excel.

    =VLOOKUP(B4,CTSCs!$A$3:$E$2068,2,FALSE) in this formula $A$3:$E$2068
    also covered by $ sign and some time $A3:$E2068 and some time A$3:E$2068 kindly i need interpretion of all these 3 formulas.

    ReplyDelete
    Replies
    1. As before, and in the post, the dollars only make a difference when the formula is copied elsewhere. All of those ranges are the same, but when copied, the dollars will change the behaviour.

      $A$3:$E$2068 will not change when copied as all columns and rows have been fixed by dollars

      $A3:$E2068 - the row (2068) will change relative to the position of the new formula (e.g. if copied from row 2068 to row 2069, the formula would now read $A3:$E2069), as there is no dollar fixing that row number.

      A$3:E$2068 - the column (E) will change relative to the position of the new formula (e.g. if copied from column F to column G, the formula would now read $A3:F$2069), as there is no dollar fixing that column reference.

      If you need to understand the VLOOKUP formula itself, take a look at my post:

      Excel Tip: VLOOKUP or HLOOKUP?

      If you have regular queries like these, you might be interested in my Excel Advice by Email service:

      http://www.needaspreadsheet.com/excel-advice/

      Delete
    2. Thanku Sir I will join you for training in rest of days with hope that load shading will decrease in pakistan.
      One thing that i forget password of my excel file and knows first few words but rest of not is there any solution???

      Delete
  23. I use $A1 and i cop the formula in row and columns i found that
    In Row and Column $A1 $A1 $A1 $A1 $A1
    $A2
    $A3
    $A4
    $A5
    $A6
    the formola which i copy indicates that row is absolute and columns is relative.??????
    but by defination of $A1 column must be absolute
    waiting you guidence please

    ReplyDelete
    Replies
    1. That is working how it should. By putting the $ in front of the A, it is the A that you have fixed. Copying along row 1 will have no effect as you are not changing the row, and you have fixed the column. The $ will have no effect when you copy down, because you have only fixed the column, which you are not changing. The row however does change as you have not fixed it.

      Delete
  24. I have created some formulas without putting the dollar sign, is there a way a can add to different cells/columns at the same time?

    ReplyDelete
    Replies
    1. Not that I know of but it might be possible with VBA.

      Delete
  25. Great - I knew some of this but had only really used it to fix both row and column and wasn't really aware why I was putting the $ where I was inserting it. Also, the F4 shortcut will be particularly useful.

    ReplyDelete
  26. The fact that you put the exchange rate in a different cell is another good tip. I try not to hard card numbers in my formulas. For instance, in a spreadsheet that I am now responsible for, feet are converted to miles by the formula =a2/5280. Someone not familiar with the American (antiquated) system of measures might wander what this formula is doing.

    ReplyDelete
  27. Nikki - I'm pleased you found it useful.

    Paul - That is point 2 on My Ten Principles for Excel Good Practice.

    ReplyDelete
  28. Might be a stupid question but how do you add dollar sign in excel for mac with only Euro sign in the key board and it don't seem to cut it.

    ReplyDelete
  29. Thanks for explaining this. Everyone who uses excel professionally likes it because it is easy to change data and have your whole file update if you want it to. Unfortunately, the same thing happens sometimes when you don't want it to.

    Just as you pointed out in your example, people may copy a cell. They forget that it relies on information that is found elsewhere and may even change that information and it messes up their results.

    Excel is a powerful tool and can help us save time when we use it well.

    ReplyDelete
  30. Thanks for this refresher, this is always kind of tricky, even when you know it!

    ReplyDelete
  31. I love you.
    You have just saved me hours.

    ReplyDelete
  32. I have a problem. When manually running depreciation schedule fo 1000s of items every month, how could I use this function.

    WDV Rate July Aug Sep Oct Nov Dec Jan
    100 10% 10 10 10
    500 18% 90 90 90

    Just two rows here but in real world I have 1000s. Now, I could use $ to get value for jul, aug and so on for the first row but just wondering if there is any way to do it for whole sheet at one go?
    Any suggestion would be much appreciated!!

    Shally

    ReplyDelete
    Replies
    1. Shally

      If you just use the dollar to fix the column (and not the row), then you can copy down the formula and it will work for the whole sheet.

      Delete
    2. Thanks for replying Glen,
      But if we don't fix the row, corresponding value changes.
      Actually I am after something which can give me A1*B1 in whole row 1, A2*B2 in whole row 2 and so on. I just don't want to type for each of the rows manually. Is there something to help this ?
      Regards,
      Sally

      Delete
    3. That's exactly what my suggestion would do. Cell C1 should be =$A1*$B1.

      You want the row to change.

      This will stay the same all along row 1, but when copied down to row 2, the row number will change to 2, etc.

      Delete
    4. That's great!
      Life is so easy now.
      Great website, great advice.
      Cheers!!
      Shally

      Delete
    5. Pleased to be of assistance. If you would like access to email advice when you need it, you might be interested in my Excel Advice by Email service.

      Delete
  33. Thanks for this, Glen. I was interpolating two sets of values (over 5000 values for each) and i needed the formula to consecutively refer to another single separate cell that contained a constant. I just knew there had to be a way of referring to that cell in the formula AND having it remain same in subsequent copy/paste, so i went searching... and Google popped you up!

    ReplyDelete
    Replies
    1. Pleased to be of help. There's usually a way!

      Delete
  34. Hello sir,

    It is possible to use $ sign for changing the column istead the row ? ( example : A1, B1, C1, ......)
    So far i can use $ signto change row and stay in one cell ....$A1 or A$1 ...

    Thank you
    Arta

    ReplyDelete
    Replies
    1. Arta

      You've got it with A$1, when you copy that to column B and C, it will become B$1 and C$1 - no matter which rows you copy it onto.

      Delete
  35. Cell references are useful. As you pointed out in your example, sometimes a cell points to information that you want to remain fixed, such as an exchange rate. You can then worry about making sure that the exchange rate information is always up to date, without thinking about how it affects a calculation in another part of the spreadsheet.

    ReplyDelete
  36. Is there any way dollars can be used to fix references to table ranges, i.e. "Table1[Column1]"?

    ReplyDelete
    Replies
    1. Not exactly. This does seem to be an oversight from Microsoft. I tend to change it to a cell reference and use the dollars when I do this but there are a number of alternative approaches suggested in the following discussion:

      http://answers.microsoft.com/en-us/office/forum/office_2010-excel/absolute-reference-to-table-column-in-excel-2010/51c910d5-5140-4288-8824-87ceb462813c

      Delete
  37. Very Helpful, especially the F4 comment!!

    ReplyDelete
  38. Thank you so much sir, really help us to important time. So keep it up.

    ReplyDelete
  39. Great Article.Really help-full.

    ReplyDelete
  40. Helpful to the core! :)) Thanks mate

    ReplyDelete
  41. The F4 command does not work in Excel 2011.
    Instead: Go to the toolbar for Formulas / Functions and click "Switch Rererence."
    Or: Click the Command Key and T.

    ReplyDelete
    Replies
    1. Thanks for that John. Not having a Mac, I never know where the Mac versions differ.

      Delete
  42. I'm very new to excel so I have little to no knowledge of how to use excel. I saw my Calc teacher calculate a function as x->0, and found I could use such an application, and after some tweaking I finally got it. Cheers ^^. Excel is such an amazing tool.

    ReplyDelete
  43. Tahnk you it was help full.

    Regards
    Amith B M

    ReplyDelete
  44. This comment has been removed by the author.

    ReplyDelete
  45. Good article, but I admit, I'm flummoxed. I'm on Sheet1, cell B4, with formula =Sheet3!J7. If I fill across from B4 to P4, it increments to =Sheet3!K7, =Sheet3!L7, etc. (by design). What I need is to increment to =Sheet3!J8, =Sheet3!J9, etc. Using =Sheet3!$J8 and trying to fill across gives me the same results as if it were =Sheet3!$J$8, with no increment at all. Do I have to fill down for it to work?

    ReplyDelete
    Replies
    1. Eric

      That's working correctly, when you fill across the row will stay fixed without the dollar - the column will move. Your dollar is fixing the column.

      If you fill down, you will see that the row changes.

      The dollars can only fix references, not get them to change in a different way. In your example you can fix the J or fix the 8. Without the dollar, a row will change when the row changes and a column will change when the column changes.

      I hope that makes it clearer.

      Delete
  46. This is really helpful- thanks!

    What if I want to use =CONCATENATE($A$2,"-",D3:10) until I reach the end of my Col D data, then have the formula auto update to =CONCATENATE($A$3,"-",D3:10), =CONCATENATE($A$4,"-",D3:10) ...etc?

    ReplyDelete
    Replies
    1. Amanda

      I'm not sure what the D3:10 bit supposed to be in your formula.

      Delete
  47. Dear Mr.Glen,

    Am Arivalagan, am make a hyperlink formal in one column(E2), in another column am used if conditional formula(F2), in that IF condition is true means E2 columns value will show their, but I need that hyperlink also.... Can you help me for above that problem...

    ReplyDelete
    Replies
    1. If you use HYPERLINK(E2) in the IF statement rather than just E2, that should do what you are after (if I've understood correctly).

      Delete
  48. is there a way to select a whole bunch of random cells and insert dollar signs into their formulas?

    ReplyDelete
    Replies
    1. I'm not aware of a way of doing this, I'm afraid.

      Delete
  49. This comment has been removed by the author.

    ReplyDelete
  50. Thanks a lot Sir, Really learnt a good one and very useful!

    ReplyDelete
  51. I need fresher tutorials in excel spreadsheet. My profession requires it for my daily work. I face challenges in my daily work because of lack of knowledge in excel /spreadsheet. Joshua

    ReplyDelete
  52. Really really helpful thanks. As with your clients i had a varied understanding of it's use but your very clear and concise definition has helped me immensely.

    Although as a patriot i do feel aggrieved that they didn't implement this function using the £ for UK releases ;) (don't worry, i'm not that stupid really!)

    ReplyDelete
    Replies
    1. Pleased you found it useful. It's ok, we don't have to worry about confusing it with our currency! ;)

      Delete
  53. Someone created a sheet where when I want to enter a formula it gives me an error message to the effect that it has to be a number between 1 and 24. What do I do?

    ReplyDelete
    Replies
    1. Graham

      I'm not sure this is relevant to this post, but I think you'll find that there is data validation on that cell. Assuming the spreadsheet is not protected, you can switch this off by highlighting the cell and clicking Data Validation on the Data ribbon and clicking Clear All.

      You might be interested in my Excel Advice by Email service, where I answer questions like this for you for a modest annual fee.

      For more details, take a look at the link below:

      http://www.needaspreadsheet.com/excel-advice/

      Delete
  54. Great Help!!! laymen explanation made me learn this first time from internet, could not find any such related video too... good work...

    ReplyDelete
  55. please am having problem in calculation, how can we do sum with $ sing

    ReplyDelete
  56. please for example am calculating =DOLLAR(SUM(C35,C36) the result show £220.00 instead of dollar sing what will i do sir, or how can i calculate sum by using dollar singlike c35120 ,c36100

    ReplyDelete
    Replies
    1. Why not just use =SUM (C35, C36) and change the number format of the cell to Currency and select $ as the symbol?

      Delete
  57. Sir thanks that is formula that i use,i will learn more from you.

    ReplyDelete
  58. THIS IS VERY USEFULL FOR ME. THANK YOU SIR

    ReplyDelete
  59. Hello Mr.Glen Feechan, i have found your tips really useful. i need your help. the issue is that i have a range of students in excel in column A, lets say, from 1 to 10. Now 8 students appeared out of 10 in a test and their result is made along with their roll numbers.their roll numbers are in column D and their marks appear in Column E against their roll numbers. All I need is to automatically appear their marks in column B against their roll numbers in column A and the rows of 2 students in column C ,who have not appeared, may remain blank. I hope I have not complicated much.Any excel formula or vbasic macros solution is much appreciated. Thanks.

    ReplyDelete
  60. Hello Mr.Glen Feechan, i have found your tips really useful. i need your help. the issue is that i have a range of students in excel in column A, lets say, from 1 to 10. Now 8 students appeared out of 10 in a test and their result is made along with their roll numbers.their roll numbers are in column D and their marks appear in Column E against their roll numbers. All I need is to automatically appear their marks in column B against their roll numbers in column A and the rows of 2 students in column C ,who have not appeared, may remain blank. I hope I have not complicated much.Any excel formula or vbasic macros solution is much appreciated. Thanks.

    ReplyDelete
    Replies
    1. Basit - you can use VLOOKUP or INDEX and MATCH to lookup the marks and use IF and ISERROR to show blank if this returns an error (as it will for those without a mark).

      The following posts should tell you all you need to know:
      http://www.notjustnumbers.co.uk/2013/02/excel-tip-vlookup-or-hlookup.html

      http://www.notjustnumbers.co.uk/2013/03/excel-tip-index-and-match-alternative.html

      http://www.notjustnumbers.co.uk/2011/07/excel-tip-eliminating-div0-and-other.html

      Delete
    2. Thank you sir, i appreciate your help.

      Delete
  61. i have one querry , if in one column name and their roll number or contact number is provided , how can i seprate both data from one column into two column ?

    ReplyDelete
  62. Their particular field regarding know-how, their particular practical knowledge, the kind of structure in addition to features they've, the kind of technological innovation they will use in their particular customer products and services in addition to the kind of assurance they give in the instance of accidents, delays or perhaps shipment deterioration. car export

    ReplyDelete
  63. Really need some help here. I have a product that I have a manufactured. I have four pricing tiers I receive for quantities of 1-25, 26-50, 51-75, and 76-100. Each of those ranges has a column on my spreadsheet. Just to the right of that is the column where I input the quantity I want to order. To the right of that is where I want the appropriate cost to be displayed, based on the quantity. Any help would be immensely appreciated. Thank you.

    ReplyDelete
    Replies
    1. ***Below each of these quantities is a cost.

      Delete
    2. You could use HLOOKUP with the fourth argument set to TRUE. State your quantities row as the start of each range, I.e. 1, 26, 51, 76. If you've never used HLOOKUP before there is a post on this blog, just enter HLOOKUP into the search bo at the top of the blog.

      Delete
  64. Thanks. Its really helps to understand in very basic form :)

    ReplyDelete
  65. if($B7?$INPUTS.$G$15."",LOOKUP($B7,$'cash flow'.$f$85:$AJ$85))

    i want to know
    1) What does it mean
    2) what is the use of $85 and $15 here ??

    ReplyDelete