Google Sheets
Types of Cell References
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:
- Select the first cell where you wish to enter the formula (for example, B5).
- Enter the desired formula (for example, =B2+B3+B4).
Entering a formula - Press the Enter key on your keyboard. The result will display in the cell.
Displaying the result - Select the cell containing the formula you wish to copy (for example, B5). The fill handle will appear.
The fill handle - Hover the mouse over the fill handle, and the cursor will change into a black cross
.
Hovering over the fill handle - 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 - 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
