Absolute and Relative Cell References in Excel

excel logoA reference is a way to point to a cell or range of cells in Excel. In effect what you are doing is telling Excel where to look for values to use in your formulas or to place in other cells. There are several different ways to do this, the most common being the absolute and relative references. A relative reference means that Excel will change the cell your formula is pointing to if you copy or expand the cell containing the formula. An absolute reference means that the referenced cell will not change when you change the position of the cell with the formula. You can refer to cells on the same sheet you are working on, to another sheet in the current workbook, or to a sheet in a different workbook. When you refer to a cell in another workbook you are creating a link to that workbook. But let's back up a little and give a couple of simple examples of relative and absolute references.

The following examples all refer to Microsoft Excel 2003. If you are using a version that is different than that your screens will look slightly different, but the basic functionality should be the same.

Relative reference

Open a blank workbook in Excel and enter the following numbers in to the worksheet:

 

excel references 01

 

We'll use Autofill now to add some more data to the sheet (click here for more information on Autofill). Highlight the range of cells A1:B2, then place your mouse over the autofill box in the lower right corner of the selected cells. Now, while holding down the left mouse button drag down about 10 cells, like so:

 

excel references 02

 

When you release the mouse the cells will have filled in automatically with incremented values:

 

excel references 03

 

Now, let's enter in a basic formula into cell C1, then use autofill to expand the formula for the rest of the data (there is more information about entering basic formulas into Excel here). Let's multiply them, so enter the formula =A1*B1 into cell C1:

 

excel references 04

 

Again using Autofill, drag down to the bottom of the data to get the results:

 

excel references 05

 

Now select the bottom right cell, then look up at the formula bar to see the results of dragging the formula down:

 

excel references 06

 

So, what's happened here? Excel has incremented the referenced cells in the formulas, starting at =A1*B1 all the way down to =A12*B12. While this can be very handy, it isn't always what you want to do.

Absolute References

There will be times when you want to lock a reference to a particular cell, so that when you copy, move or expand the formula it will always reference the same cell. You do this by using an absolute reference. Let's experiment on this by multiplying a range of numbers by a constant. Start by entering the same numbers as above, only this time just expand the second column:

 

excel references 07

 

If you were to use the same type of reference as above in your formula you will get a 0 for the results when you expand the formula down, because it will be multiplying by the empty cells:

 

excel references 08

 

So instead, let's make an absolute reference to the 2 in cell E1. You tell Excel you want to use an absolute reference by inserting a $ in front of the row and column indicator, like this:
$A$1
In this case, we're going to multiply all of these rows by the value in cell E1, so we need to reference it as $E$1, like this:

 

excel references 09

 

Now when you expand the formula it will increment the values in the formula from column F (because we left that as a relative reference), but it will lock in the multiplier to cell E1 because of the absolute reference:

 

excel references 10

 

Now when you check the results of expanding the formula in the formula bar you see that it incremented the cells in column F but locked in the cell E1 for each formula:

 

excel references 11

 

This covers a couple of the basics of how to reference cells in Excel. We'll cover some of the others, like mixed references and R1C1 references in later posts.

Comments

Its my pleasure to announce this good news for people who are looking for some platform for article submission. Our website is free platform for every one who is interested in publishing their content, thesis or anything. The only condition is that it must be unique and not copied from any where else.