Excel 2019/365: Copying Formulas, Relative Cell References

Updated 2019-08-09

Objectives:

·         Use the fill handle to copy a formula.

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

Relative cell references

Download the samples workbook. Go to the Budget worksheet.

 

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:

Max

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:

Min

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:

Average

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: