Excel 2016: Copying Formulas, Relative Cell References

Updated 2017.07.30

·
Use the **fill handle** to copy a formula.

·
Know how to use **relative** cell references
in a formula that is to be copied.

Download the budget workbook:

A formula
to sum the **Food** budget is **=B2+C2+D2**

A formula
to sum the **Rent** budget is **=B3+C3+D3**

A formula
to sum the **Misc** budget is **=B4+C4+D4**

Rather than typing in three formulas that are all essentially the same, we only need to type the first formula and we can get Excel to copy the formula into the other two cells.

Enter the
formula **=B2+C2+D2** in cell E2:

Press the Enter key and Excel will evaluate the formula for you:

Note the
locations of the three cells that are used in the formula *relative to cell
E2*. B2 is 3 cells to the left of E2, and C2 is 2 cells to the left of E2,
and D2 is 1 cell to the left of E2. This is how Excel interprets the formula:
Get the number 3 cells to the left, the number 2 cells to the left, and the
number one cell to the left and add all of them up. If we copy this formula to
the next row, Excel does *not* copy "=B2+C2+D2". Instead, it
copies a formula that will add the numbers 3 cells to the left, 2 cells to the
left, and 1 cell to the left of the cell in which the formula is copied to. So
the formula, when copied to the next row, becomes this:

**=B3+C3+D3**,
which is exactly what we want!

Move the cursor back up to cell E2:

Position the mouse cursor in the
lower right corner of cell E2 and the cursor will change into a **fill handle**
(the little black plus sign – you need to be careful – the region where
the cursor changes to the fill handle isn't very large):

Click and drag mouse down to the next two cells:

If you look at the formulas in cells E3, E4, and E5, you will see this:

When you copied the formula from E2 to E3 and E4, Excel automatically adjusted the terms in the formula. As you moved from row 2 to row 3, it replaced all of the 2's in the formula with 3's. And when you moved to row 4, it replaced the 2's with 4's. So each formula does exactly what it is supposed to do—it adds the three numbers to its left.

Now let's add a total to the bottom of the Total column:

· Click on cell E5.

·
On the **Home **tab, in the **Editing **group, click on the **Autosum** button:

.

You should see the following:

Excel is telling you that it will **sum
**the cells E2 through E4. **Sum **is the name of a built-in function. The
text in the parentheses (**E2:E4**)** **is called an **argument **to
the function. An argument is the data that must be made available to the
function so the function can compute an answer. The cells from E2 through E4
are called a **range**, and ranges are indicated in Excel by listing one
corner of the range (usually the upper-left corner), followed by a colon,
followed by the opposite corner**. **Excel gives you a visual clue by
drawing a dancing line around the cells that it is going to sum. Always verify
that the cells that are inside of the dancing line are actually the ones that
you want to sum. Excel will usually guess the correct range, but not always
(see the Average example below). Since Excel __is__ guessing correctly this
time, press the Enter key.

This formula can be copied to the previous cells in the row (B5:D5). Click on E5, get the fill handle, and drag to the left:

Excel will copy the formulas:

Now let's calculate the highest value in each category.

· Click in cell F2.

·
On the **Home **tab, in the **Editing** group (far right of
ribbon), click on the **Autosum** button's
down-arrow:

·
Click on **Max**.

Excel
assumes that you want the maximum of __all__ of the numbers immediately to
the left. __But Excel is assuming incorrectly!__

You do __not__ want to include the total from column E in
your computation of the maximum. You can fix this by dragging the mouse over
the correct cells: B2:D2.

Press Enter and use the fill handle to copy the formula down to the next three cells:

Now let's calculate the lowest value in each category.

· Click in cell G2.

·
On the **Home **tab, in the **Editing** group (far right of
ribbon), click on the **Autosum** button's
down-arrow:

·
Click on **Min**.

Again,
Excel assumes __incorrectly__ that you want the minimum of __all__ of the
numbers immediately to the left.

You do __not__ want to include the
numbers from column E or column F in your computation of the minimum (although
in this case, it wouldn't matter). You can fix this by dragging the mouse over
the correct cells: B2:D2.

Press Enter and use the fill handle to copy the formula down to the next three cells:

Now let's calculate our monthly averages.

· Click in cell H2.

·
On the **Home** tab, in the **Editing **group (far right of
ribbon), click on the **Autosum** button's
down-arrow:

·
Click on **Average**.

Again, Excel assumes incorrectly
that you want the average of __all__ of the numbers immediately to the left.

Again, you can fix this by dragging the mouse over the correct cells: B2:D2.

Press Enter and use the fill handle to copy the formula down to the next three cells: