Lessons Lesson 3

Ranges

About this lesson

A range is a block of cells. You write it as two addresses with a colon: B2:D5 means every cell from B2 to D5.

By the end you can

  • read a range like B2:D5 and picture exactly which cells it covers.
  • select any row, column or block by naming its two corners.
  • type a range into a formula and see what it covers.

By Ali, Founder, Excel Lessons

The idea

Almost every useful function takes a range, not a single cell. So you will see this notation everywhere. Two things to know early. You can write a range backwards: type B5:B2 and Excel reads it as B2:B5. But a space instead of a colon is a different thing. B2 B5 is not a typo Excel fixes.

A range on its own is a block, and the sheet shows you every cell in it. That is the quickest way to check you named the cells you meant. Type the range, look at what appears, and count.

The mistake to watch for

Naming a range that includes the heading by accident. B1:B5 covers the word Q1 as well as the four figures under it. A total ignores the word and still looks right, and a count comes out one too many. Start the range at the first figure, not at the label above it.

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Click a cell

A range is a block of cells: first cell, colon, last cell. B2:D2 means B2, C2 and D2.

Select B2:D2.

A1
Client
Row A B C D E F G
1 Client Q1 Q2 Q3 What B2:B5 covers
2 Harbour Dental 1450 1620 1380
3 Nolan & Reid 2890 2450 3110
4 Pinecrest Vets 940 1105 875
5 Wexford Tiles 3320 3480 2990
6
7
8 Harbour, all year
9 Nolan & Reid, all year

Every step

  1. 1

    A range is a block of cells: first cell, colon, last cell. B2:D2 means B2, C2 and D2. Select B2:D2.

    Hint. Start at B2 and stretch the selection across to D2.

  2. 2

    A range on its own shows every cell it covers. In F2, type: =B2:B5.

    Hint. First cell, colon, last cell.

  3. 3

    A range can be written either way round. Read =B5:B2 and say what it returns.

    Hint. Which cells does that block cover?

  4. 4

    Without a range you name every cell. In B8, type: =B2+C2+D2, which is Harbour Dental's three quarters.

    Hint. Add the three cells on row 2.

  5. 5

    Two opposite corners name a whole block: top-left cell, colon, bottom-right cell. Select B2:D5. That is every figure in the table.

    Hint. Two corners are enough: the top left and the bottom right.

  6. 6

    In B9, total Nolan & Reid's year the same way.

    Hint. Same shape as the last one, one row down.