Video:Cell References in Excel

Transcript:Cell References in Excel

Today I'll show you the differences between absolute and relative cell references in Microsoft Excel.

Before Using Relative and Absolute References in Excel

Open Excel. I'll go up to file, open to open a saved spreadsheet. You can see I already have the total for the first quarter and up here is the formula.

I'll type the same kind of formula for the second quarter. Equals parenthesis, C2+C3+C4+C5, and then close the parenthesis. Each cell reference in the formula is highlighted with a different color so you can see exactly where it is in the formula. I'll just hit enter and there is my total for the second quarter.

There is an easier way, but wouldn't you think that if you copied the formula from the second quarter total to the third quarter total that you would end up with the same total? Well let's see. If I click and drag over my active cell that contains the formula, you can see that the total is different. If you look up at the formula bar, Excel has updated the cell references to D2, D3, D4, and D5. It is able to do this because these cell references are relative which is the default when writing formulas. It allows excel to predict your intentions, which is great for this kind of function. But what if you want to reference the same cell again and again?

Creating Relative and Absolute References in Excel

Absolute cell references are fixed and Excel cannot change it unless you change it in the formula. So let's look at the totals from each regional division. Cell F2(check) contains the value for the total of the East, so if I drag this down to F3, it updates the formula and the value for the West. You can see the formula was changed from B2, C2, D2, and E2 to B3, C3, D3, and E3. Relative cell references.

To make the formula absolute, I'll have to enter in dollar signs, which are called strings in programming. If I put a dollar sign before every letter and number those specific cells will be referenced again. So now when I drag this formula down to the North total, the same total is produced as the West. You can add a string symbol to a single a letter or a single number but be aware that Excel might update the other.

You can also reference a single cell by just putting the sting symbol in front of each letter and number. An easy way to change a relative cell reference to an absolute one is using the F4 key on your keyboard. When I highlight my whole formula, and press F4, it rotates through putting stings just in front of the numbers, just in front of the letters, or completely relative and back to fully absolute. I'll put it back on relative and drag the formula the rest of the way down the total column.