In Excel, a specific cell can be named or referred to with a Cell Reference. Basically a simple cell reference is just the letter at the top of the cell’s column, paired with the number at the left of the cell’s row. Cell A1, for example, is the first cell in the top left corner of the Excel grid (first column letter, A, and first row number, 1).  

 

 

In this image, Cell C3 contains the value 500, and D3 contains the value 250. Cell E3 contains a formula (=C3+D3, visible in the formula bar) that adds these two numbers by using their respective cell references.

 

All formulas must begin with an equals sign. This tells Excel that what follows is a formula.

 

If you use your mouse to drag the cell containing the formula (E3) down to fill part of the column (using the thin cross mouse pointer), you will see zeros in the cells that you drag the formula to.

 

You should also notice that the formula for cell E8 (the active cell) can be read from the formula bar as =C8+D8. But remember, the original formula in E3, the cell we filled from, was =C3+D3.

 

The formula has changed to reflect the relative positions of the cells. The formula in cell E3 adds the two cells to its immediate left. Each cell that this formula has been filled to, will contain a formula that adds the two cells to its immediate left.

In other words, the formula adopts cell references that are relative to its position in the worksheet. This maintains the same relative positioning of the original formula. The result is zero in the locations where the cells to the immediate left are empty.

 

This is called a relative cell reference, meaning that if the formula is moved (dragged, filled, or copied) the cells involved will change to reflect the formula’s new position.

 

 

In this example, we have mouse dragged a formula with absolute references to fill the five cells beneath it.

 

Notice that this time, the value of 750 is in each cell, and the formula contained in the active cell, E8, contains dollar signs. These dollar signs tell Excel that the references in the cell are absolute. No matter where the formula is copied or filled to, it will always use the same cell references.

 

The original formula at cell E3 is =$C$3+$D$3. When this formula is copied or dragged or filled anywhere on the worksheet, the formula will retain the same cell references because they are marked with dollar signs.

 

To summarize, a reference like A1 it is a relative cell reference because there are no dollar signs included. The cell reference $A$1 is an absolute cell reference, because of the dollar signs in front of the letter and number. The cell reference $A1 has an absolute column reference (because the column letter has a dollar sign in front). In this case, the column reference will never change. The cell reference A$1 has an absolute row reference, meaning the row reference will never change.

 

If you want to copy or fill a formula across cells, and you want the cells in the formula and the result of the formula to change relative to location, use relative references.

 

If you want to copy or fill a formula across cells, and you always want the cells used in the formula to remain the same, use absolute references.