GOOGLE SHEETS CELL REFERENCES

What are Cell References?

A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Google Sheets can find the values or data that you want that formula to calculate.

In one or several formulas, you can use a cell reference to refer to:

• Data from one or more contiguous cells on the worksheet.
• Data contained in different areas of a worksheet.
• Data on other worksheets in the same workbook.

There are three kinds of cell references that you can use in Google Sheets:

• Relative Cell References
• Absolute Cell References
• Mixed Cell References

Understanding these different types of cell references will help you work with formulas and save time (especially when copy-pasting formulas).

WHAT ARE RELATIVE CELL REFERENCES IN GOOGLE SHEETS?

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

WHAT ARE ABSOLUTE CELL REFERENCES IN GOOGLE SHEETS?

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 the row and the column constant in the formula.

An absolute reference is designated in the formula by the addition of a dollar sign ($).

If you add the dollar sign ($) in front of both row and column, every time you will copy the formula containing that reference, it will always take into account that specific location you set initially. 

Using the example above, if you copy the formula =$A$1+B1 from row 1 to row 2, the formula will become =$A$1+B2.

WHAT ARE MIXED CELL REFERENCES IN GOOGLE SHEETS?

Mixed cell references are a bit more tricky than the absolute and relative cell references.

There can be two types of mixed cell references:

• The row is locked while the column changes when the formula is copied: A$2
• The column is locked while the row changes when the formula is copied: $A2

This is especially useful when you need to copy formulas within big tables with multiple lines and columns: i.e. Monthly Sales tables (multiple columns) per sales agents (multiple lines).

HOW TO CHANGE THE REFERENCE FROM RELATIVE TO ABSOLUTE (OR MIXED)?

To change the reference from relative to absolute, you need to add the dollar sign before the column notation and the row number.

For example, B10 is a relative cell reference, and it would become absolute when you make it $B$10.

If you only have a couple of references to change, you may find it easy to change these references manually. So you can go to the formula bar and edit the formula (or select the cell, press F2, and then change it).

However, a faster way to do this is by using the keyboard shortcut – F4.

When you select a cell reference (in the formula bar or in the cell in edit mode) and press F4, it changes the reference.

Suppose you have the reference =B10 in a cell.

Here is what happens when you select the reference and press the F4 key.

• Press F4 key once: The cell reference changes from B10 to $B$10 (becomes ‘absolute’ from ‘relative’).
• Press F4 key two times: The cell reference changes from B10 to B$10 (changes to mixed reference where the row is locked).
• Press F4 key three times: The cell reference changes from B10 to $B10 (changes to mixed reference where the column is locked).
• Press F4 key four times: The cell reference becomes B10 again.