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.

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

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

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.

This is where the dollar sign is used.

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.

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"*.
Thanks for clear info about dollar sign in Excel.

ReplyDeleteI have used this several times but this example has cleared some confusions.

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

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

ReplyDeletethanks its really helpfull...can u also eplain about use of " : " in execl sheet?

Deletethanks a lot. Now i know use of dollar symbol

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

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

DeleteGreat

ReplyDeleteif 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

ReplyDeleteIf you don't want negative figures, then use the IF formula e.g., =IF(A1>15000,A1*0.05,0)

DeleteHope this helps

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

DeleteAnonymous

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

=(A1-15000)*0.05

would do it.

Very helpful. Thanks!

ReplyDeletethanks for helping, mate

ReplyDeleteI thought I would fix my problem with dollar sign, but it turns out I was wrong :) I need help...

ReplyDeleteI 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

i want to apply the same function to the row with the $

Deletebut 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

Thanks for explaining it in such simple manner!

DeleteTHis is really nice - thanks guys!

ReplyDeleteA good ref here too:

Dollar sign in Excel

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?

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

DeleteAlternatively, 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.

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.

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

DeleteDoes that make sense?

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.

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

Pleased you're sorted.

Deletethanks it was really helpful!

ReplyDeletewhat do i press for the dollar sign

ReplyDeleteAs per the post:

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

Hello sir.

ReplyDeleteIm 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

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

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

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

Thanks for asking the question, and thanks for the answer, Glen.

DeleteHi Glen,

ReplyDeleteIs 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

Fiona

DeleteFirst 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

thanks much for the great explanation!

ReplyDelete-kas

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

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

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

ReplyDeleteand

=vlookup($A4,$A$3:$E$2068,2,FALSE)

What is the difference betwwen these two and when we use it??????

Dear Muhammad

DeleteBoth 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!

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

ReplyDeleteThanks alot Sir i may need more attention regarding Excel.

ReplyDelete=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.

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.

Delete$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/

Thanku Sir I will join you for training in rest of days with hope that load shading will decrease in pakistan.

DeleteOne thing that i forget password of my excel file and knows first few words but rest of not is there any solution???

I use $A1 and i cop the formula in row and columns i found that

ReplyDeleteIn 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

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.

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

ReplyDeleteNot that I know of but it might be possible with VBA.

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

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

ReplyDeleteNikki - I'm pleased you found it useful.

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

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.

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

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

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

ReplyDeleteI love you.

ReplyDeleteYou have just saved me hours.

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

ReplyDeleteWDV 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

Shally

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

Thanks for replying Glen,

DeleteBut 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

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

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

That's great!

DeleteLife is so easy now.

Great website, great advice.

Cheers!!

Shally

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.

DeleteThanks 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!

ReplyDeletePleased to be of help. There's usually a way!

DeleteHello sir,

ReplyDeleteIt 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

Arta

DeleteYou'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.

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.

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

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

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

Very Helpful, especially the F4 comment!!

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

ReplyDeleteGreat Article.Really help-full.

ReplyDeleteHelpful to the core! :)) Thanks mate

ReplyDeleteThe F4 command does not work in Excel 2011.

ReplyDeleteInstead: Go to the toolbar for Formulas / Functions and click "Switch Rererence."

Or: Click the Command Key and T.

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

DeleteI'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.

ReplyDeleteThanks a ton!!! :D

ReplyDeleteTahnk you it was help full.

ReplyDeleteRegards

Amith B M

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?

ReplyDeleteEric

DeleteThat'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.

This is really helpful- thanks!

ReplyDeleteWhat 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?

Amanda

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

Dear Mr.Glen,

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

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

Deleteis there a way to select a whole bunch of random cells and insert dollar signs into their formulas?

ReplyDeleteI'm not aware of a way of doing this, I'm afraid.

DeleteThanks a lot Sir, Really learnt a good one and very useful!

ReplyDeleteI 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

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

ReplyDeleteAlthough 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!)

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

DeleteSomeone 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?

ReplyDeleteGraham

DeleteI'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/

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

ReplyDeleteplease am having problem in calculation, how can we do sum with $ sing

ReplyDeleteI'm not sure what you mean.

Deleteplease 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

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

DeleteSir thanks that is formula that i use,i will learn more from you.

ReplyDeleteTHIS IS VERY USEFULL FOR ME. THANK YOU SIR

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

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

ReplyDeleteBasit - 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).

DeleteThe 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

Thank you sir, i appreciate your help.

Deletei 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 ?

ReplyDeleteReally 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***Below each of these quantities is a cost.

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

DeleteThanks. Its really helps to understand in very basic form :)

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

ReplyDeletei want to know

1) What does it mean

2) what is the use of $85 and $15 here ??

Thanks!

ReplyDeleteHi, 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

ReplyDeleteTracey

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

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.

ReplyDeletehi, 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.

ReplyDeleteAre you sure that's what it is doing? Does the row not change when you copy down?

DeleteHow to fix random numbers generated using RAND or RANDBETWEEN functions ?

ReplyDeleteThe simplest answer is to copy them then paste them in the same position, using Paste Special, As Values.

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

ReplyDeleteI found a work-around using OFFSET, thank you.

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

DeleteGreat. Thanks for the help! It was useful

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

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

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

DeleteIf I have a column A with different times 8:01am, 8:24am, 8:11am.. etc

ReplyDeleteThen 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

The following should do it:

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

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

ReplyDeletePlease i need i very quick answer

Thankuyou so much for your help

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.

DeleteThanks..!! well explained

ReplyDeleteHi, 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 ?

ReplyDeleteI'm not sure I understand the question.

DeleteThanks. Well explained yes!

ReplyDeletethis 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?

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

ReplyDeleteThank you. This was very helpful and did the trick for me.

ReplyDeleteAndrews,

ReplyDeletethanks soo much, i am very grateful

Very good description! I could not find this in Excel help!

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

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

ReplyDeleteOne of the best explanation for the concepts of dollar. Thanks

ReplyDeleteGenerally we fix like $B1*A$1, why not B$1*$A1??

ReplyDeleteIf not then why ???

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.

DeleteIn 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=SUMIF(DBR!$E$4:$E$335,CSM!$C4,DBR!G$4:G$335)

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

What is the Difference Between above Two formulas??

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

ReplyDeleteSuresh - 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).

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

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?

ReplyDeletethanks for the info..was helpful

ReplyDeleteThank you sir. With your help finished the job as planned.

ReplyDeleteThank you

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.

ReplyDeletety

I have a problem and not sure how to solve it in excel.

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

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.

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

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

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

Delete=R2C4

is the same as

=$B$4

thnq

ReplyDeleteHi,many thanks for your post. I got one question:

ReplyDeletefor 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

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.

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

thanks a lot for your support. I will try it

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

ReplyDeleteHello Sir,

ReplyDelete$ fix Column E1, E2, E3

If i want fix row E1, F1, G1.

Thank you.

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

DeleteE$1

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

ReplyDeleteI don't understand the question

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

ReplyDeleteThanks , very helpful !

ReplyDeleteHello, there!

ReplyDeleteI 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!

Assuming you just the value of A1 to appear if it is 40 or more then the formula in B1 should be:

Delete=IF(A1<40,A1&"*",A1)

Thanks a lo!! That's great !

DeleteI 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

ReplyDeleteIf your sale price is in cell A1 then:

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

Thanks, I just used it and really love it.

ReplyDeleteThank you very valuable info

ReplyDeleteGreat, thanks, helped me a lot

ReplyDeleteThx bro!

ReplyDeleteTHANKS PROBLEM RESOLVED

ReplyDeleteThanks problem resovled

ReplyDeleteThank you!

ReplyDeleteThank you very much, it has been useful to me

ReplyDelete