Relative Cell Reference in Google Sheets

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.

Table of Contents

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:

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_reference

The 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.

=SUM(E3:E9)

The same format can be used for a multitude of other function combinations in Google Sheets.

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.