Excel Practice
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.

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 changes it to B2:B5. But a space instead of a colon is a problem. B2 B5 is not a typo that Excel fixes. It is a different operator. It finds where two ranges overlap, and when they do not overlap it returns #NULL!. If a formula returns #NULL!, look for a missing colon first.

The mistake to watch for

People read a range as two cells. B2:D5 is not B2 and D5. It is every cell in the block between those two corners. If you name the wrong corner, the range includes a heading or misses the last row. The total is wrong, and there is no error message.

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. You write the first cell, a colon, and the last cell. B2:D2 means B2, C2 and D2.

Select Harbour Dental's three quarterly totals.

They are that range.

A1
Client
Row A B C D E F G
1 Client Q1 Q2 Q3
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

Every step

  1. 1

    A range is a block of cells. You write the first cell, a colon, and the last cell. B2:D2 means B2, C2 and D2. Select Harbour Dental's three quarterly totals. They are that range.

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

  2. 2

    Down a column it is the same idea the other way round: the letter stays and the number changes. Select every Q2 figure, from the first client down to the last. Leave the heading out.

    Hint. Start at C2 and stop at the last client, not at the heading.

  3. 3

    A range does not have to be a single line. Two opposite corners name a whole block: the top-left cell, a colon, the bottom-right cell. Select every invoice total in the table at once.

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

  4. 4

    A range does not have to start at the top or reach the bottom. It is whatever two corners you name. Select the Q3 totals for the last two clients only.

    Hint. Two cells is a perfectly good range.

  5. 5

    One more, from the address alone this time. Select the range A3:B4 and check what it covers before you let go.

    Hint. Column A row 3, to column B row 4.