Excel 2013 & 2016: Copying Formulas, Relative Cell References

Updated 2016.08.31

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 budget workbook:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image001.jpg

 

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image002.jpg

 

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image003.jpg

 

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image004.jpg

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):

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image005.jpg

Click and drag mouse down to the next two cells:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image006.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image007.jpg

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image008.jpg.

You should see the following:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image009.jpg

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.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image010.jpg

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image011.jpg

Excel will copy the formulas:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image012.jpg

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image013.jpg

·       Click on Max.

Excel assumes that you want the maximum of all of the numbers immediately to the left. But Excel is assuming incorrectly!

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image014.jpg

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.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image015.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image016.jpg

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image017.jpg

·       Click on Min.

Again, Excel assumes incorrectly that you want the minimum of all of the numbers immediately to the left.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image018.jpg

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.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image019.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image020.jpg

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image013.jpg

·       Click on Average.

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image021.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image022.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/013-CopyingFormulasRelative_files/image023.jpg