What does absolute reference mean in Excel?

Answer 1

Absolute references are very useful in a spreadsheet. You would use them when you want to refer to a cell that is going to be a fixed value in a cell. If that value is likely to change from time to time, it is better to implement it in a cell and use an absolute reference.

So for example: Go to B1 and type in 10%

Then go to A2 and type in any value. Put something into A3 and A4 and so on, down to about A10. Now say you want to find 10% of all of those numbers.

In B2 type:

=A2*$B$1

It will give you 10% of the value in A2. Then copy it down to through all the cells to B10. If you look at the formula you will see they all use $B$2 but the A2 that was in the first formula will be A3 in the second formula and so on. You'll get 10% of all the cells in column A, because all the formulas are looking at cell B2 where the 10% is. Now change the 10% to something else. All the values in the formulas in column B will now change to multiply by the new value, because they are all looking at cell B1. You did not need to change any of the formulas. If you had originally done =A2*10% in B2, you would have had to change all the formulas to the new value. Using absolute references is more efficient.

Another good example of the use of absolute references is for getting running totals. As an example, try this:

Put values in all the cells from A1 to A10. Then go into B1 and type the following formula:

=SUM(A$1:A1)

Copy the formula down through the cells to B10 and you will have a list of running totals in column B.

There are a lot of other ways of using absolute cells. As the last formula shows, you don't always need to use two $ symbols when doing them. In fact, the in the first example, it would have worked if it was B$1. There are reasons for using just one dollar or putting the dollar before the letter instead of the number, but that is more complex to explain. Rest assured that there are many reasons for using absolute cell referencing and they can save you a lot of work.

Answer 2

Whenever you were going to copy that reference (or range) to other cells and you wanted the reference to stay exactly the same when so doing.

Example:

Imagine you were using a function like VLOOKUP (it's not important if you know what this is or not). You just need to appreciate that to use this you have to declare a range of cells (a table array) to look in. Which would be written something like A1:B100 (for example). Now if you wanted to copy that exact formula to multiple cells you would almost certainly want to make this range absolute so that it didn't change from one cell to the next.

In this example, if the range was absolute it would look like $A$1:$B$100.

Answer 3

It means the reference to a cell will remain frozen and it wont change no matter where to copy. If you are asking this question related to macros, then it means macro will always start at the same point where you started the recording.

Answer 4

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.

Answer 5

The dollar in front of the letter locks the column. When the formula with the reference is copied across, the letter will not change, like it would if there was no dollar in front of it.