Excel 2016: Copying Formulas, Absolute Cell References

Updated 2017.07.30

Objectives:

·        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.

Cell References

Download the budget workbook:

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

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.

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

Press the Enter key to have Excel accept the formula:

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

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.

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

Excel will display the number as a percent:

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

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.

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

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:

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

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.

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

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.

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

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:

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

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.