Excel: Copying Formulas with the Fill Handle

 

Objectives:

 

Using Excel help (F1):

In the Search For box, type Copy and press the Enter key.

In the Search Results list, click on Move or copy a formula and read the topics.

 

In the Search For box, type Formula and press the Enter key.

In the Search Results list, click on Enter a formula and read the topics.

 

Excel Bible: Chapter 8

 

Consider the following spreadsheet:

 

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

 

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

 

Drag the (hold the left mouse button down) mouse down to the next two cells:

 

If you look at the formulas in cells E3 and E4, 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 totals to the bottom of the Totals column:

(1)   select cell E5,

(2)   click on the Autosum toolbar button ,

(3)   press the Enter key

 

Excel will sum the values from E2 through E4 and place the sum (3000) in E5 using the formula =SUM(E2:E4).

 

Now let's calculate the percent of our budget that is going towards food, rent, and misc. Select F2 and enter the formula =E2/E5.

 

Press the Enter key to have Excel accept the formula:

 

Format the number as a percent by selecting it and then clicking on the percent button on the formatting toolbar:

 

Since we want to calculate the percentages for rent and misc, we need to copy the formula to cells F3 and F4. Do the same thing that you did in column E. Position the cursor in the lower right corner of the cell you want to copy (F2) and when the fill handle appears, drag the formula down to the next two cells.

 

This time we get an error! The #DIV/0! means that we are trying to divide by 0. To see what the problem is, look at the formulas that Excel copied for us:

 

The formulas that we wanted were =E3/E5 and =E4/E5. What we got instead of E3/E5 was E3/E6 and instead of E4/E5 we got E4/E7. What went wrong? Excel did the same thing it always does – it adjusted each term in the formula to account for the fact that the formula had been moved down on the page. On row 3, it changed E2 to E3 (which we wanted) and it changed E5 to E6 (which we did not want). And when it got to row 4, it changed the E2 to E4 (which we wanted) and it changed the E5 to E7 (which we did not want). In both of the new formulas, we want the denominator (the E5) to remain the same. However, that's not how Excel copies formulas! It always adjusts them!

 

However, there is a way to tell Excel that a cell is not to be changed when it is copied as part of a formula – tell Excel that the cell is an absolute cell reference. An absolute cell reference is a cell reference that is never to be changed when a formula is copied. And the way you tell Excel that a cell reference is an absolute cell reference is by putting a pair of dollar signs in the term – one before the column letter and one before the row number, like this: $E$5. The dollar sign to the left of the E means "don't change the E" when copying the formula, and the dollar sign to the left of the 5 means "don't change the 5" when copying the formula, either.

 

So you have to know two things when you create a formula:

(1) Are you going to copy it?

(2) If so, are there any terms that you do not want changed when you copy the formula?

 

If the answer to both questions is yes, then you need to write the terms that you do not want changed as absolute cell references. So in our example, we need to go back to cell F2 and change the formula to =E2/$E$5. You can also delete the formulas in F3 and F4, but we will be writing over them soon anyway.

 

The result is the same as before. Making a cell reference absolute has no effect at all on the formula that it is in. The difference is when we copy the formula. Select cell F2, position the cursor in the lower right corner to make the fill handle appear, and drag the formula down to cells F3 and F4.

 

This time it works! If you go back and look at the formulas in F3 and F4, you will see that the relative term (E2) did get changed (first to an E3, then to an E4) when it was copied (that's what relative terms are supposed to do) and the absolute term ($E$5) did not get changed (that's what absolute terms are supposed to do). Check out the copied formulas:

 

What if you're not sure about whether a term is relative or absolute? Go to the next line and write out the formula for that line by hand. In this case, the next line is row 3. The formula for F3 should be E3/E5. When you compare this to the formula for row 2, (E2/E5) you see that the first term is changing (therefore it is a relative term) and the second one is not (therefore it is an absolute term). So to make the E5 absolute you need to put in the dollar signs. Remember that the dollar sign goes before the letter and before the number. The term E$5$ is incorrect and will result in an error.

 

 

Updated 2005.03.17