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!
To help you use Excel more effectively (and save a ton of time), we've compiled a list of essential formulas, keyboard shortcuts, and other small tricks and functions you should know. NOTE: The following formulas apply to Excel 2017. If you're using a slightly older version of Excel, the location of each feature mentioned below might be slightly different.
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. 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, where you can subscribe for regular updates and get your free report 'The 5 Excel features that you NEED to know'.
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? 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 to refer to the cell relative to a cell that is not going to move. I hope that helps. 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!
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: If you have regular queries like these, you might be interested in my service: http://www.needaspreadsheet.com/excel-advice/. 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.
![Dollar Dollar](/uploads/1/2/5/3/125373578/241480415.jpg)
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/. 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.
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.
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?
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.
If the formula was copied one cell to the right, it would now refer to DBR!H$4:H$335. 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. 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: I hope that helps. 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.
I aim to provide practical Excel tips to those using Excel in their day-to-day jobs. I am a Chartered Accountant who has used Excel for too many years to remember, and now focus my efforts on helping others to get the most out of this powerful tool.
If you have any Excel requirements, I provide both on-site and remote consultancy and development services, of which you can find out more. You can sign up (below) and get a free report and regular updates of new posts to the blog. Also have a look at the section for free training videos, etc. I hope you find the content useful and that every now and again it makes you smile. Glen Feechan.