Mehrab Imtiaz
Understanding relative cell reference in Google Sheets. or any other spreadsheet application, is considered a starting point for any aspiring user.
Cell referencing is akin to breathing when it comes to working with spreadsheets. Inputting or extracting values, using values in formulas, working with known and unknown values, all use cell referencing in Google Sheets and relative cell reference is the most basic form of it.
The Basics of Relative Cell Reference in Google Sheets
Typically, applications like Google Sheets and MS Excel use cell references in the “A1” format. Where “A” and other alphabets are used to denote column numbers. On the other hand, “1” and other integer numbers are used to denote row numbers.
For example, let’s say we want to extract a value from the following table. Let’s go with the first name on the list. Notice that it is located in cell C3.
In an empty cell, we start the reference (or any formula) with “=” followed by the cell address, C3.
Press ENTER to apply the value to the cell.
This is what we call a relative cell reference, which is the foundation of every spreadsheet application.
Examples of Relative Cell Reference
To really show how relative cell references are utilized in Google Sheets, we have to go through some practical examples.
For our first example, let’s do a simple addition. Just add two relative cell references together.
=C3+D3
Instead of typing in the cell references, we can just click on the respective cells to reference them.
Another example would be multiplication.
=C3*D3
Like what has been shown, relative cell reference can be utilized in any formula in Google Sheets, be it simple arithmetic functions, logical functions, or complex formulas.
Using Fill Handle to Apply Cell Reference to Multiple Cells
Inputting values and formulas individually sure seems quite inefficient. If you are sure that a range of adjacent cells, columns or rows, is going to have the same format of formulas, we can utilize the fill handle to do so.
The fill handle is revealed when you move the cursor to the bottom-right corner of a selected cell and it turns into a plus (+) symbol.
Using fill handle to apply the formula to the rest of the column:
Read More: Variable Cell Reference in Google Sheets (3 Examples)
Using Relative Cell Range Reference in Google Sheets
The use of relative cell reference is not confined to just single cells, but it can also be used to define a range of adjacent cells.
The format for relative cell range reference is simple:
starting_cell_reference:ending_cell_referenceThe colon (:) defines the range.
Example of Using Cell Range Reference
The range of cell reference means little if not utilized by a function or formula. In this example, we will be calculating the subtotal of all the items in the table using the SUM formula.
Step 1: We start the SUM function, =SUM(
Step 2: Input the range of cells that we want. You can manually input it or just select the range with your mouse.
Step 3: Press ENTER to apply.
The same format can be used for a multitude of other function combinations in Google Sheets.
Read More: Google Sheets: Use Cell Value in a Formula (2 Ways)
The Disadvantage of Relative Cell References in Google Sheets
One glaring disadvantage of relative cell references is moving data addresses. That is when you use fill handles or other similar ways to automatically populate cells, the cell references will move or point to cells accordingly.
For example, when we used the fill handle to apply the formula to the rest of the Total column, each new row pointed to different cells.
Now, what if we had only one multiplier?
Let’s say we have 100 units for all Items, meaning we must multiply the cost of each item by 100 to get each total. If we use the regular multiplication formula with fill handle as before:
We will get the wrong results. Because Google Sheets is automatically moving the cell reference for the multiplier downwards as we move down the column.
Relative cell references give us unwanted results when we use fill handles to apply formulas.
To overcome this limitation, we have to lock the cell reference of the multiplier in the formula. We discuss this and other forms of locking cell references in our Lock Cell Reference in Google Sheets article.
Final Words
We hope that this article has provided you with a clear understanding of relative cell references in Google Sheets. Feel free to leave a comment or explore more basics of Google Sheets from the links below.
Related Articles
- Reference Another Sheet in Google Sheets (4 Easy Ways)
- Return Cell Reference in Google Sheets (4 Easy Ways)
- Reference Another Workbook in Google Sheets (Step-by-Step)
- Dynamic Cell Reference in Google Sheets (Easy Examples)
- Reference Another Tab in Google Sheets (2 Examples)
- Lock Cell Reference in Google Sheets (3 Ways)
