Absolute and Relative Cell References in Excel
A 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.
Open a blank workbook in Excel and enter the following numbers in to the worksheet:
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:
When you release the mouse the cells will have filled in automatically with incremented values:
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:
Again using Autofill, drag down to the bottom of the data to get the results:
Now select the bottom right cell, then look up at the formula bar to see the results of dragging the formula down:
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.
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:
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:
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:
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:
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:
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:
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.