Cells are the building blocks of a spreadsheet… where your data is stored, calculated, manipulated, and eventually displayed.

Each bordered area where a row and column meets is a cell:

ABC
1CellCellCell
2CellCellCell
3CellCellCell

There are so many cells in a spreadsheet that you need an efficient way to find them.

This is why each cell has what's called a 'reference'.

What is a cell reference?

A cell's reference is made up of its corresponding column letter and row number.

It's like a cell's address.

The first cell in a spreadsheet is always A1 (the cell at the intersection of column A and row 1).

The cell to the right of A1 is B1 (column B and row 1).

The cell below A1 is A2 (column A and row 2).

You get the idea:

ABC
1A1B1C1
2A2B2C2
3A3B3C3

What is a range?

Sometimes you will need to refer to more than one cell at a time.

Instead of entering each cell like this A1,A2,A3,A4 you can use what’s known as a 'range'.

A range is a group of adjacent cells.

It cannot contain gaps and must be rectangular.

How do you reference ranges?

Ranges are referenced like this:

Top-Left Cell Reference : Bottom-Right Cell Reference (e.g. A1:A2 or B3:D5)

You just insert a colon between the two references.

For example, these four cells:

AB
1A1B1
2A2B2

Are identified using A1:B2.

You can reference entire columns like this:

Leftmost Column Letter : Rightmost Column Letter (e.g. A:A or B:D)

You can also begin a range at one cell and end it at the bottom of a column:

Top-Left Cell Reference : Rightmost Column Letter (e.g. A2:A or B5:D)

You can reference entire rows like this:

Top Row Number : Bottom Row Number (e.g. 1:1 or 2:4)

You can also begin a range at one cell and end it at the end of a row:

Top-Left Cell Reference : Bottom Row Number (e.g. B1:1 or A2:4)

Using cells and ranges in formulas

Cells and ranges can be referenced in formulas by:

  1. Typing them out, or
  2. Selecting them by clicking and dragging the mouse (they'll be written in the formula for you automatically)
Spreadsheet Cell Referencing

Using either method, the reference will become a specific color and the corresponding cells in the spreadsheet will be highlighted using the same color.

If an identical range is used more than once in a formula, its color remains the same.

Entering multiple reference like A1,A2,A3,A4 without wrapping them in a function (more on that later) will result in a formula parse error (#ERROR!).

Entering just a range like A1:B2 without wrapping it in a function will result in a #VALUE! Error.

Don't worry about these things when they happen below… the important thing is that now you know what cell and range references are.

Cell Referencing Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. Single Cell
In cell B1, enter a formula that refers to the cell to its left. Remember to start your formula with '='.
2. Multiples Cells
In cell A2, enter a formula that refers to cells A1, C5, and B9 (in that order). Remember to start your formula with '=' and put commas between the cell references.
3. Range Of Cells
In cell B3, enter a formula that refers to the range of cells from D1 to E10. Remember to start your formula with '=' and the colon you need for a range reference.
4. Cells And Ranges
In cell A4, enter a formula that refers to cells C6 and B10 and the range of cells from A8 to D9 (in that order). Remember the commas between references.
ABCDE
1
2
3
4
5
6
7
8
9
10
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.