Google Sheets

Introduction

When creating a formula that contains cell references, knowing which type of cell reference to use will help your formula remain correct when it is copied to a new cell.

In this lesson, you will learn about relative and absolute cell references and how to create and copy formulas that include them.

Types of cell references

Watch the video (4:30).

In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.

Google Spreadsheets will interpret cell references as either relative or absolute. By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula from row 1 (for example, =A1+B1) into row 2, the formula will change (for example, to =A2+B2).

Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.

Watch the video to learn how to copy and fill relative and absolute references.

Relative references

Relative references can save you time when you are repeating the same type of calculation across multiple rows or columns.

In our example below, we are creating a formula with cell references in row 5 to calculate the total cost of the grocery, utility, and rent expenses for each month (B5=B2+B3+B4). For the upcoming months, we want to use the same formula with relative references (C2+C3+C4, D2+D3+D4, E2+E3+E4, etc.). For convenience, we will copy the formula in B5 into the rest of row 5, and Google Spreadsheets will calculate the value of the expenses for those months using relative references.

To create and copy a formula using relative references:

1. Select the first cell where you wish to enter the formula (for example, B5).
2. Enter the desired formula (for example, =B2+B3+B4).
   
   Entering a formula
3. Press the Enter key on your keyboard. The result will display in the cell.
   
   Displaying the result
4. Select the cell containing the formula you wish to copy (for example, B5). The fill handle will appear.
   
   The fill handle
5. Hover the mouse over the fill handle, and the cursor will change into a black cross .
   Hovering over the fill handle
   
6. Click, hold, and drag the fill handle over the cells you wish to fill. A black dotted line appears around the cells.
   
   Dragging the fill handle
7. Release the mouse. The formula is copied to the selected cells as a relative reference, and the values are calculated in each cell.
   
   Viewing the calculated values

Double-click on several filled-in cells to check their formulas for accuracy. The relative cell references should be different for each cell.

Double-clicking to view the formula

Absolute references

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula.

An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.

The three types of absolute references

You will most likely use the $A$2 format when creating formulas that contain absolute reference. The other two formats are used much less often.

To create and copy a formula using absolute references:

In our example, we will use the 5.5% sales tax rate (which is written as 0.055) in cell H14 to calculate the sales tax for all of the items in column G. In our formula, we need to use the absolute cell reference $H$14. This will make sure each cell will reference H14 by keeping the values of both the row and column the same when the formula is copied to other cells in column G.

1. Select the first cell where you wish to enter the formula (for example, G5).
2. Enter the desired formula (for example, =F5*$H$14).
   
   Entering a formula
3. Press the Enter key on your keyboard. The result will display in the cell.
   
   Viewing the result
4. Select the cell you wish to copy (for example, G5). The fill handle will appear.
   
   The fill handle
5. Click, hold, and drag the fill handle over the cells you wish to fill.
   
   Dragging the fill handle
6. Release the mouse. The formula is copied to the selected cells, and the values are calculated in each cell.
   
   Viewing the values

Double-click on several filled-in cells to check their formulas for accuracy. The absolute reference should be the same for each cell.

Double-clicking to view the formula

Challenge!

To work through the challenge, open GCFLearnFree L14: Monthly Expenses and Order Form and copy the file to your Google Drive. View the instructions below the challenge if you are not sure how to make a copy of the file.

1. Using the sheet labeled Expenses, in cell B6 create a formula that uses relative cell references to add the values for groceries, utilities, rent, and car.
2. Use the fill handle to fill in the formula across row 6, from cell B6 through M6.
3. Double-click on cell M6 to view the formula and the relative cell references. Enter a different value in cell M2. Observe how cell M6 recalculates the value.
4. Click the sheet labeled Order Form. In cell G5, create a formula that will add the value in cell F5 to the value in cell H14. Use an absolute cell reference to H14 in the formula.
5. Use the fill handle to drag the formula down column G to fill in the cells from G5 through G12. Double-click on cell G12 to view the formula for accuracy. H14 should be referenced in the formula.
6. In cell H14, enter 0.07 as the new sales tax. Observe how the cells in column G recalculate.

To copy the example file to your Google Drive:

In these tutorials, we will provide example files you can use to practice what you've learned in each lesson. Because these files are Google Docs we have chosen to share, you will need to copy the file to your Google Drive before you can edit the file.

1. Click the link at the top of this page to open the example file.
2. The example file will appear in a new browser tab or window. If you are not currently signed in to your Google account, locate and click Sign in on the top-right corner of the page.
   
   Signing in to your Google Account
3. After you have signed in to your Google account, locate and select File in the toolbar menu and select Make a copy... from the drop-down menu.
   
   Making a copy of the example file
4. The Copy Document dialog box will appear. Enter a new title for the file, then click OK.
   
   Naming the file and clicking OK
5. The copy of the file will appear in a new browser tab. Now you're ready to start using the example file.
   
   Viewing the copied example file in a new tab