Cell References

Many times it is needed to use values from other cells to calculate totals, averages, or other useful information. This is a basic feature of all spreadsheets. In this lesson you will learn how to work with reference that change as formulas are copied to new cells, references that don’t change when they are copied to other cells, and even cell that are on a different worksheet.

Download File

• Click to download the file.
• From the main office.com screen, upload the file from your download folder.

Relative Cell References

Relative references change when a formula is copied to another cell.

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns.

Copy the formula to find the total books read from cell D2 (=B2+C2) down to the cells under it. The cell reference will change to match the amount of movement. If you paste the formula three cell down, the formula will refer to cells three cells down from the ones that were originally referred to. This works in any direction, up, down, left, or right.

Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

Absolute Cell References

Absolute references remain constant no matter where they are pasted.

There may be times when you do not want a cell reference to change when pasting a formula to other cells. Unlike relative references, absolute references do not change when copied or pasted. You can use an absolute reference to keep a row and/or column constant.

In this worksheet, the formula in cell C4 is =B4/$A$1. Cell A1 contains the book reading goal (6). If we copy this formula down to the next row below, Excel will change the formula to =B5/$A$1. Notice that the reference to A1 using the dollar signs stayed the same. A1 will ALWAYS contain the book reading goal so we want it to always stay the same in the formula and not change. We want THAT value to remain ABSOLUTE. We tell Excel this by putting a $ in front of the “A” and the “1” in the first formula. Then we copy the formula down and only items without a $ in front will change.

Experiment with leaving the dollar signs out of the formula in C4 and then copy and paste it down. Then change it back to the correct way.

The following picture shows how it should look if done correctly.

Other Worksheet References

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you’ll simply need to begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell reference would be Sheet1!A1.

If a worksheet name contains a space, you will need to include single quotation marks (‘ ‘) around the name. For example, if you wanted to reference cell A1 on a worksheet named Absolute References, its cell reference would be ‘Absolute References’!A1.

Look at the formula in C2 on the “Other Worksheet References” worksheet. It is referencing the book reading goal on the “Absolute References” worksheet.

This formula works great! But if you copy it and paste it down on C3 through C6 it won’t work. Can you see why? Can you fix it?

The A1 part of the formula =B2/’Absolute References’!A1 is a relative reference and will need changed to be absolute. Try =B2/’Absolute References’!$A$1. Does that work?

When you have the formulas in column C working correctly, write a formula in E2 that will figure the percent of goal achieved for 2022. Then copy and paste it into the remaining cells in column E.

This is what it should look like when you are finished.

Feel free to change the book goal in cell A1 on the “Absolute References” worksheet and note how the % of goal numbers change on that worksheet as well as on the “Other Worksheet References” worksheet.

Great work! Now you know how to reference values in other cells!