Search Not Just Numbers

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

174 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
    3. Very helpful indeed... I was amazed by what i just read about your explanation. I have encountered this so many times and long time ago. I have just realised its function just now. Thank you so much. it is a great help. I hope you will not stop spreading what you have learned.

      Delete
    4. Very helpful indeed... I was amazed by what i just read about your explanation. I have encountered this so many times and long time ago. I have just realised its function just now. Thank you so much. it is a great help. I hope you will not stop spreading what you have learned.

      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
    Replies
    1. If you don't want negative figures, then use the IF formula e.g., =IF(A1>15000,A1*0.05,0)
      Hope this helps

      Delete
    2. Also a simple VBA function can accomplish this and is actually the starter example (think "hello world") for many VBA tutorials.

      Delete
  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
    2. Thanks for asking the question, and thanks for the answer, Glen.

      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 at 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. 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
  45. 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
  46. 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
  47. 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
  48. Thanks a lot Sir, Really learnt a good one and very useful!

    ReplyDelete
  49. 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
  50. 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
  51. 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
  52. Great Help!!! laymen explanation made me learn this first time from internet, could not find any such related video too... good work...

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

    ReplyDelete
  54. 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
  55. Sir thanks that is formula that i use,i will learn more from you.

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

    ReplyDelete
  57. 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
  58. 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
  59. 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
  60. 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
  61. Thanks. Its really helps to understand in very basic form :)

    ReplyDelete
  62. 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
  63. Hi, I seem to remember using a formula that allows a row to move in a different sheet and the formula adjust to where the row has moved to. Is this possible? Help really needed

    ReplyDelete
    Replies
    1. Tracey

      I am not sure what you mean but cut and paste will do what I think you mean.

      Delete
  64. Thanks for you sharing this excel tip. I have been struggling this problem for while. I used to rely on paste by value to avoid these kinds of errors. Now i know the correct way to do this.

    ReplyDelete
  65. hi, i have a problem with the $formula. i want ot fix only the column but when i use $A1 both the rows and columns are fixed! no idea why! any help will be very much appreciated.

    ReplyDelete
    Replies
    1. Are you sure that's what it is doing? Does the row not change when you copy down?

      Delete
  66. How to fix random numbers generated using RAND or RANDBETWEEN functions ?

    ReplyDelete
    Replies
    1. The simplest answer is to copy them then paste them in the same position, using Paste Special, As Values.

      Delete
  67. Hi Glen, for Data Validation -> List, as source, instead of 23 in '=$K$6:$K23', i want to give a formula, for example, I want to give something like '=$K$6:$K(VLOOKUP(C3,N2:P9,2))', is it possible? If so, what is the right format? please advice, thanks in advance.

    ReplyDelete
    Replies
    1. I found a work-around using OFFSET, thank you.

      Delete
    2. I didn't fully understand your question, but it looked like something that required a dynamic range using OFFSET. Pleased you're sorted!

      Delete
  68. Great. Thanks for the help! It was useful

    ReplyDelete
  69. This too clear, it's great to have such details on web. Thank you so much for sharing this with the public

    ReplyDelete
  70. Thank you so so much. Always wondered how to use the $ sign. Finished my computation in no time.

    ReplyDelete
    Replies
    1. +1. $ symbol has baffled me for months. This article finally ended my confusion.

      Delete
  71. If I have a column A with different times 8:01am, 8:24am, 8:11am.. etc
    Then I have a column B with different times as well 6:25am, 9:12am, 7:20am...etc
    Then column C reads either "Complete" or "In Progress."
    I would like to create a formula that would Give me a COUNT if column A is LESS THAN or EQUAL to column B AND column C reads "Complete." I tried... =COUNTIFS(A:A," <=" &B,C:C,"Complete") Help Please

    ReplyDelete
  72. The following should do it:

    =SUMPRODUCT((A:A<=B:B)*(C:C="Complete"))

    ReplyDelete
  73. Is it possible to disable the $ sign on a new excel sheet so that when making any links i don't have to use f4 to remove itcel in every linked cell
    Please i need i very quick answer
    Thankuyou so much for your help

    ReplyDelete
    Replies
    1. Sorry, but I don't know of any way to do that, as Excel assumes an external link will be to an absolute cell, rather than a relative reference.

      Delete
  74. Thanks..!! well explained

    ReplyDelete
  75. Hi, I used to work with excel every day and just wanted to clarify one thing...may i know is there a possibility for me to maintain a data sheet which will get 4 columns with static data for a long period and other 5 columns having daily changing data. In that case how can i keep the non updating data on correct alignment when I'm updating the rest of the columns ?

    ReplyDelete
  76. this is hard to explain. How can you take a cell that was created with a formula and make it stay the same. I want to be able to delete the cells that derived the formula. Here is what I am doing: I am working on a spreadsheet where I import negative numbers (expenditures) from a different program and I want them to be positive. I have been creating a formula where I take the number times a negative one and copy it down the entire line. This works but I can't figure out how to keep those cells as that number, while deleting the column I don't need full of negative numbers?

    ReplyDelete
  77. Copy them and then paste them back on top of themselves using Paste Special, as Values.

    ReplyDelete
  78. Thank you. This was very helpful and did the trick for me.

    ReplyDelete
  79. Andrews,
    thanks soo much, i am very grateful

    ReplyDelete
  80. Very good description! I could not find this in Excel help!
    The F4 key is a very useful trick.
    Another that I resort to is to use a "variable" name
    for the $x$y cells, but this requires many names in some cases.

    ReplyDelete
  81. 0(zero) should not displaid in the cell and the formula give should not be er-raze

    ReplyDelete
  82. One of the best explanation for the concepts of dollar. Thanks

    ReplyDelete
  83. Generally we fix like $B1*A$1, why not B$1*$A1??
    If not then why ???

    ReplyDelete
    Replies
    1. In the first instance you have fixed the column of (B) the first reference and the row (1) of the second. In the second example you have done the opposite.

      Delete
    2. In the first instance you have fixed the column of (B) the first reference and the row (1) of the second. In the second example you have done the opposite.

      Delete
  84. =SUMIF(DBR!$E$4:$E$335,CSM!$C4,DBR!G$4:G$335)

    =+SUMIF(DIST!$C$2:$C$1048576,CSM!B4,DIST!$E$2:$E$1048576)

    What is the Difference Between above Two formulas??

    ReplyDelete
  85. Explain IST!$E$2:$E$1048576 and DBR!G$4:G$335??

    ReplyDelete
    Replies
    1. Suresh - I'm not quite sure what it is that you want me to explain, but I will assume that it is the different use of the dollar signs in the two examples (as this is the subject of the blog post).

      The first one looks at the range from E2 to E1048576 on sheet IST, whereas the second one looks at the range G4 to G335 on sheet DBR.

      In the first one the $ is on both the E and the 2 (as well as the E and the 1048576), fixing both the column and row in both cases. This means that if the formula was copied elsewhere it would still look at exactly the same range.

      In the second one, the dollar is only on the row numbers (4 and 335), meaning that the column references will move relatively if the formula is copied elsewhere. e.g. if the formula was copied one cell to the right, it would now refer to DBR!H$4:H$335.

      Delete
  86. How to have just a certain number that change when dragged down? e.g: 01/Q4/2015. How to have just 01 that change to 02, 03 and so on but Q4 and 2015 sill fix?

    ReplyDelete
  87. thanks for the info..was helpful

    ReplyDelete
  88. Thank you sir. With your help finished the job as planned.

    Thank you

    ReplyDelete
  89. Any idea how to use cell references in C#? Actually there is no cell in C# i just need to know if there is a code equivalent.

    ty

    ReplyDelete
  90. I have a problem and not sure how to solve it in excel.
    I want each cell along vertical column lets say column A to refer a calculation on two columns on a separate sheet and replace the second column moving towards right keeping first column fixed on each consecutive cell of the resulting sheet in the same column.
    For example,

    cell A2 of sheet 2 = sumproduct(all of col b2:b21 of sheet 1, all of col m2:m21 of sheet 1)
    I want cell A3 of sheet 2 = sumproduct( all of col b2;b21 of sheet 1, all of column n2:n21 of sheet 1)

    what would be the $ usage in the case above? Please suggest.

    ReplyDelete
    Replies
    1. If I've understood your problem correctly, you can't do it just with dollars as the you are wanting the column to increase with the row. The dollar would only allow you to specify whether the row increased with the row or not.

      I think OFFSET would be your simplest solution:

      Try

      =SUMPRODUCT(Sheet1!$B$2:$B$21,OFFSET(Sheet1!$M$2,0,ROW(A2)-2,20,1))

      We are basically using the row number of the current cell , ROW(A2)-2, to determine how many columns we should offset from M2.

      The OFFSET function is explained here:
      http://www.notjustnumbers.co.uk/2010/02/using-offset-function-in-microsoft.html

      I hope that helps.

      Delete
  91. Does the dollar sign for fixing the collumn/row also work in row/collumn numericall reference style sheet ? (E. g. =R[-1]C+RC[-2]; newest version of office) I am afraid not...

    ReplyDelete
    Replies
    1. You can achieve the same thing with R1C1 referencing by using the actual row and column numbers (no square brackets) rather than the relative square brackets you've used in your example. e.g.

      =R2C4

      is the same as

      =$B$4

      Delete
  92. Hi,many thanks for your post. I got one question:
    for Data Validation -> List, as source, instead of 23 in '=$K$6:$K23', i want to give a formula, for example, I want to give something like '=$K$6:$K(VLOOKUP(C3,N2:P9,2))', is it possible? If so, what is the right format? thanks for any help

    ReplyDelete
    Replies
    1. You should be able to do what you are after using a dynamic range. Open the Name Manager on the Formula ribbon, and add a new range (let's call it dropdown), and enter your formula as the range area. Dollar rules apply, so if you don't add the dollars, the reference will be relative to the currently active cell - for example if you have cell B2 selected and you enter =A2 as your range definition, your range will always be the cell to the left of the active cell. If you enter =$A2, the range will always be the cell in column A of the same row as the active cell, etc.
      Once you have created the range, you just enter its name preceded by the equals sign as the list in your data validation, e.g. =dropdown.

      Delete
    2. thanks a lot for your support. I will try it

      Delete
  93. yeah, thanks for your post. I think i found out my problem, I have fixed and everything is ok now,

    ReplyDelete
  94. Hello Sir,
    $ fix Column E1, E2, E3
    If i want fix row E1, F1, G1.
    Thank you.

    ReplyDelete
    Replies
    1. Just put the $ in front of the row rather than the column, i.e.

      E$1

      Delete
  95. Sir i want fixed in cell about $0.00 Ex: A1= $0.00 ( don't delete in cell except values )

    ReplyDelete
  96. Brilliant! so clear and concise and just helped on a deadline submission of a tender. Thank you so much for sharing your knowledge.

    ReplyDelete
  97. Hello, there!
    I want someone to help me formulating for the following condition :
    If A1 is less than 40, the value in A1 should return together with an asterisk. For example, 19 in A1 should appear in B1 as 19*.

    Thanks in advance!

    ReplyDelete
    Replies
    1. Assuming you just the value of A1 to appear if it is 40 or more then the formula in B1 should be:
      =IF(A1<40,A1&"*",A1)

      Delete
    2. Thanks a lo!! That's great !

      Delete
  98. I am trying to calculate a formula that I want a fix value and then the formula calculated by a percentage. Therefore if my sale price is < or = than $10,000.00 my fee is $50.00 but if my sale price is > or = $10,000.00 my fee is $50.00 plus 0.018% of sale price

    ReplyDelete
    Replies
    1. If your sale price is in cell A1 then:

      =50+IF($A$1<=10000,0,$A$1*.00018)

      Delete
  99. Thanks, I just used it and really love it.

    ReplyDelete
  100. Thank you very valuable info

    ReplyDelete
  101. Great, thanks, helped me a lot

    ReplyDelete
  102. Thank you very much, it has been useful to me

    ReplyDelete
  103. Very efficiently written information. It will be beneficial to anybody who utilizes it, including me. Keep up the good work. For sure i will check out more posts. This site seems to get a good amount of visitors.

    ReplyDelete