LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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.

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:

1. Typing them out, or
2. 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.

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.
 A B C D E 1 ➜ 2 3 4 5 6 7 8 9 10
Next Lesson  FREE RESOURCE

12 exclusive tips to make user-friendly sheets from today: 