Overview Transcript



We just saw how to create a formula that adds two cells. Now let's copy the formula down the column, so it adds the other pairs of cells.

Want more?

Create or change a cell reference

Use cell references in a formula

Create a reference to the same cell range on multiple worksheets

We just saw how to create a formula that adds two cells.

Now, let's copy the formula down the column, so it adds the other pairs of cells.

The formula in C2 is A2+B2. To copy it, put the mouse pointer over the bottom right-hand corner of the cell until it is a black plus sign.

Click and hold the left mouse button and drag the plus sign over the cells you want to fill.

And the formula is copied into the other cells.

But, the formula wasn't just copied. If it was just copied, all of the cells with the formula would have remained A2+B2.

And by looking at the results in each cell, that obviously has not happened.

Copying the formula from C2 to C3 increased the relative position of the formula by one row, but didn't change the column.

The formula in C2 was A2+B2.

The formula in C3 is A3+B3. The cell references in the formula increased by one row.

The formulas in C4 and C5 updated similarly.

This is called a Relative Cell Reference and it is the default for Excel.

But you don't always want cell references to change when you copy a formula.

In this example, F2 is divided by E2. And we want the other numbers in column F to be divided by E2 as well.

In other words, we want E2 to not change when the formula is copied.

We can do that by making the reference absolute instead of relative.

If we copy the formula like we did before, we get an error.

For example, in G3 the copied formula is F3, which is 12, divided by E3, which is blank.

Excel interprets a blank cell as a 0 and that is a math error.

To make E2 an absolute cell reference, I type a $ before the E and a $ before the 2, and press Enter.

Now when we copy the formula, we get the results we expect.

When we look at the formula in G3, the first cell reference did update, as expected, but the E2 absolute cell reference didn't update.

Neat!

Now you have a pretty good idea about what cell references are and how to use them.

Of course, there's always more to learn.

So check out the course summary at the end, and best of all, explore Excel 2013 on your own.