When a formula is copied to a new location absolute cell reference will change?

In Excel, if you write a formula in a cell, it is common for it to contain a cell reference, which means the formula points to a value in one or more other cells.

In the example below, the formula in cell E2 references cells B2, C2 and D2. It determines the total number of phone cards requested during the quarter by adding together the requests in April (B2), May (C2) and June (D2).

But if there are more requested items than phone cards, what is the best way to add a formula to the rows with the other items?

Pro Tip: A formula can be copied down a row or across a column. Left-click in the bottom right corner of cell E2, then continue to hold down the left click until the mouse is dragged down to E7. A formula counting the items in all three months will populate down Column E. This technique will improve your data quality by lessening the chance of errors typing the same formula repeatedly.

Relative Cell References

The most common type of cell reference is a relative cell reference. It is also Excel’s default.

The example above shows relative cell references because the formula in E2 changed its cell references in the different rows of Column E to reflect cells in each row. If you compare the formulas in cells E2 and E7 from the example above, you will notice that the formula is the same except for one small difference. In E2, the formula =SUM(B2:D2) references cells B2, C2 and D2. While in E7, the formula =SUM(B7:D7) references cells B7, C7 and D7.

Absolute Cell References

Now it’s time to discuss absolute cell references. If a cell contains a formula with an absolute cell reference, that cell reference stays absolutely still. Even if the formula is copied to another cell, the cell reference in the formula will not change. Adding ‘$’ in front of the column letter and row number of a cell reference will make it an absolute reference.

To show an absolute cell reference, let’s look at what percent of the total requests each item represents.

First, change the style in Column F to the Percent Style. This can be done by right-clicking on the F in Column F to highlight the whole column, then selecting the % button from the Home Ribbon.

To calculate the percent phone cards are of the total requests, see the formula in cell F2, which is =E2/$E$9.

• E2 is a relative reference: the cell reference will change if the formula is copied to a new cell.
• $E$9 is an absolute reference: Column E and Row 9 are locked with $s; the cell reference will not change.

Now copy the formula in cell F2 down into the rows of other items in the table. Let’s take a closer look at cell F7. The formula is =E7/$E$9.

• E7 is a relative reference: the cell reference changed to reflect the items summed in the row.
• $E$9 is an absolute reference: Column E and Row 9 are still locked with $s; the cell reference did not change even though the row containing the formula did.

You can do a lot with absolute cell references, and we haven’t even talked about the mixed cell references! As you use different types of references, it is important to make sure you understand where formulas used in any given cell are pulling from. To learn more about cell references, check out these two helpful posts about cell references from Microsoft:

• Making sense of dollar signs in Excel by Steven Thomas
• Switch between relative, absolute, and mixed references

We hope this article helps you love your data a little more!

* * * * *

Interested in learning more Excel tips? Check out other articles on the HMIS blog.

Want to sign up for our newsletter? Go to the HMIS website, scroll down until you see the ‘HMIS E-mail Updates’ heading in the right column, add your email address, then click ‘Submit.’

Relative and Absolute Cell Addresses

A relative cell address has the form ColRow (e.g., A1, Z25). An absolute cell address either has the form $ColRow (e.g., $A1, $Z25), Col$Row (e.g., A$1, Z$25), or $Col$Row (e.g., $A$1, $Z$25). The $ doesn't change the cell reference; it is just to help you build your spreadsheet model faster. That is, A1, $A1, A$1, and $A$1 all refer to the top left cell of the spreadsheet.

Excel attempts to help the user by automatically updating all cell formulas as they are moved or copied from one location to another. If the formula includes relative cell addresses, then all references are changed based on the relative distance between the source (i.e., copied from) and destination (i.e., copied to) locations.

For example, if user copies the formula AVERAGE(A1:A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B1:B20). That is, the cell reference will change from A1:A20 to B1:B20 (i.e., to the right one column). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE(A11:A30).

Most of the time these automatic changes to a copied formula are exactly what the user wants. Sometimes, however, the user does not want to have the cell references changed. This is when absolute addresses should be used. The $ argument simply tells Excel not to change a column or row references if the formula is copied to another location. Cell references of the form $ColRow will not be changed if the cell is copied to the right or left, but will be changed if it is copied up or down. Cell references of the form Col$Row will not be changed if the cell is copied up or down, but will be changed if it is copied to the right or left. Cell references of the form $Row$Col will not be changed for any type of copying.

For example, if user copies the formula AVERAGE($A1:$A20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will keep the formula as AVERAGE($A1:$A20). That is, the column labels will not change since they were preceded with a $. If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will change it to AVERAGE($A11:$A30) because the row numbers were not preceded with a $.

Continuing with this example, if user copies the formula AVERAGE(A$1:A$20) from cell C4 to cell D4 (i.e., to the right one column), then Excel will change the formula to AVERAGE(B$1:B$20). If the user copies the same formula from C4 to C14 (i.e., down ten rows), then Excel will leave the formula as AVERAGE(A$1:A$20).

If used properly, absolute addresses allow the user to type only one "generic" version of each formula, then simply copy it to all of the other cells that need it.

Additional Excel Information

When a formula is copied to a new location absolute cell references will change?

When you copy and paste a formula with absolute cell references?

Does a cell reference change when copied to another cell?

When a formula is copied any absolute cell reference in it will change based on the distance between the original and the copied formula?