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:
| A | B | C | |
| 1 | Cell | Cell | Cell |
| 2 | Cell | Cell | Cell |
| 3 | Cell | Cell | Cell |
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:
| A | B | C | |
| 1 | A1 | B1 | C1 |
| 2 | A2 | B2 | C2 |
| 3 | A3 | B3 | C3 |
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:
| A | B | |
| 1 | A1 | B1 |
| 2 | A2 | B2 |
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:
- Typing them out, or
- Selecting them by clicking and dragging the mouse (they'll be written in the formula for you automatically)
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.
| A | B | C | D | E | |
| 1 | ➜ | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.