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.

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.

cell location for relative cell reference in google sheets

In an empty cell, we start the reference (or any formula) with “=” followed by the cell address, C3.

relative cell reference in google sheets

Press ENTER to apply the value to the cell.

result of cell reference

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.


addition using relative cell reference in google sheets

Instead of typing in the cell references, we can just click on the respective cells to reference them.

clicking to get cell reference from worksheet animated

Another example would be multiplication.


multiplication cell references

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.

location of the fill handle

Using fill handle to apply the formula to the rest of the column:

using fill handle to apply formula to the column animated

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:


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.

table to calculate subtotal

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.

calculating subtotal using cell range reference in google sheets animated

Step 3: Press ENTER to apply.


result of sum

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.

using fill handle applies cell reference automatically

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:

wrong results with fill handle

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 reference can sometimes give wrong results in google sheets

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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply