Excel 2016: Copying Formulas, Absolute Cell References

Updated 2017.07.30

·
Learn how to use the **fill handle** to copy
a formula.

·
Know when to use **absolute** cell references
in a formula that is to be copied.

Download the budget workbook:

Let's calculate the percent of our budget that is going
towards food, rent, and misc. Click on I2 and enter the formula **=H2/H5.**

Press the Enter key to have Excel accept the formula:

Format the number in I2 as a
percent by clicking on I2. Then, on the **Home **tab, in the **Number **group,
click on the percent button.

Excel will display the number as a percent:

Since we want to calculate the percentages for rent, misc, and total we need to copy the formula to cells I3, I4, and I5. Click on the cell you want to copy (I2). Position the cursor in the lower right corner of the cell, and when the fill handle appears, drag the formula down to the next three 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. In the image below, I have clicked on I5
and then dragged up to select cells I2 through I5:

The formulas that we wanted were
=H3/H5 and =H4/H5 and =H5/H5. What we got instead was =H3/H6 and =H4/H7 and
=H5/H8. What went wrong? Excel did the same thing it always does—it copied
the formula *relative* to the cell that the formula was in: the first
number is one cell to the left, and the second number is one cell to the left
and 3 cells down. On row 3, it changed H2 to H3 (which we wanted) and it
changed H5 to H6 (which we did *not* want). And when it got to row 4, it
changed the H2 to H4 (which we wanted) and it changed the H5 to H7 (which we
did *not* want). And on row 5, it changed H2 to H5 (which we wanted) and
it changed H5 to H8 (which we did *not* want). In all three of the new formulas,
we want the denominator (the H5) to remain the same. But that's not how Excel
copies formulas! It *always* adjusts them—it makes them relative to
the cell that they are copied into!

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: $H$5. The
dollar sign to the left of the H means "don't change the H" 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 I2 and change the formula to **=H2/$H$5.** You could also
delete the formulas in I3, I4, and I5, but there’s no need to do so
because we will be writing over them soon anyway.

The result in I2 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 I2, position the cursor in the lower right corner to make the fill handle appear, and drag the formula down to cells I3, I4, and I5.

This time
it works! If you go back and look at the formulas in G3, G4, and G5, you will
see that the relative term (H2) **did** get changed (first to an H3, then to
an H4, then to an H5) when it was copied (that's what relative terms are
supposed to do) and the absolute term ($H$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 cell I3 should be H3/H5. When you compare this
to the formula for row 2, (H2/H5) 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 H5 absolute you need to put in the dollar signs.
Remember that the dollar sign goes *before* the letter and *before*
the number. The term H$5$ is incorrect and will result in an error.