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

**Click here for our our exclusive offer on Online Excel Training****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?

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

ReplyDeleteAnonymous

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.

Hi 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

This comment has been removed by the author.

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

DeleteThis comment has been removed by the author.

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

ReplyDeleteTheir 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