What Is a Range in Excel? A1:B5 Explained

What is a range in Excel? It's a group of cells you refer to all at once, written as the first cell, a colon and the last cell. A1:B5 means the rectangle from A1 to B5: columns A and B, rows 1 to 5, which is 10 cells. Ranges are how formulas like =SUM(B2:B6) work on many cells at once. This guide explains how to read, select and use them, for anyone just starting with Excel formulas.

What is a range in Excel?#

A range is two or more cells that you select or use together. Each cell is one box in the grid, named by its column letter and row number, like B3. A range is named by its top-left cell and its bottom-right cell, with a colon between them.

The colon is called the range operator. Read it as "through": B2:B6 is "B2 through B6", which includes B2, B3, B4, B5 and B6.

Ranges are always rectangles. You can't have an L-shaped range, but you can join two rectangles with a comma, as shown below.

All the examples use this timesheet of hours worked:

How to read a range address like A1:B5#

To picture a range, find the two corner cells and fill in the rectangle between them. For A1:B5:

• The columns go from A to B: 2 columns.
• The rows go from 1 to 5: 5 rows.
• The cells are A1, A2, A3, A4, A5, B1, B2, B3, B4 and B5: 2 × 5 = 10 cells.

In the timesheet, B2:D5 is all the hours: 3 columns × 4 rows = 12 cells. Excel can count this for you with =ROWS(B2:D5)*COLUMNS(B2:D5), which returns 12.

Order doesn't change the cells. D5:B2 covers the same block, and Excel rewrites it as B2:D5 when you press Enter.

Types of ranges you'll see#

Ranges come in a few shapes. Here they are on the timesheet:

Whole-column ranges like B:B are handy when a list keeps growing, but they include the header and everything else in the column, so use them with care.

How to select a range#

There are three main ways to select a range. When you select a range, Excel highlights it, and the active cell (where you started) stays white.

With the mouse: click the first cell and drag to the last one. Or click the first cell, hold Shift and click the last cell. That second way is easier for big ranges.

With the keyboard: click the first cell, hold Shift and press the arrow keys. To jump to the end of the data in one go, press Ctrl+Shift+↓ (Mac: ⌘+Shift+↓).

For a whole column or row: click the column letter or row number. With the keyboard, Ctrl+Space selects the column and Shift+Space selects the row. On a Mac, Control+Space may switch the keyboard language instead, so click the column letter.

The selection shortcuts lesson practises all of these, and the guide to selecting ranges fast adds more tricks for large sheets. Once a range is selected, the status bar at the bottom of the window shows its sum, average and count, with no formula needed; see the status bar sum for how to use it.

How to use a range in a formula#

Most functions take a range as their input. Type the function, then select the range with the mouse or type its address:

=SUM(B2:B5)

This adds everyone's Monday hours: 8 + 6 + 7 + 8 = 29. Here are a few more on the same data:

The SUM function guide shows the other ways SUM accepts ranges, and the SUM lesson lets you try them in a live sheet.

There is also a rarely used intersection operator: a space. =SUM(B2:D5 C1:C5) adds only the cells the two ranges share, which is C2:C5, Tuesday's hours: 28. You won't need it often, but now you'll recognise it.

Absolute ranges and named ranges#

When you copy a formula, Excel moves its ranges too. =SUM(B2:B5) copied one column right becomes =SUM(C2:C5). That is usually what you want.

To keep a range fixed, add $ signs: $B$2:$B$5. Press F4 (Mac: ⌘T) after typing the range to add them. You need this when every row divides by the same total, for example.

You can also give a range a name. Select B2:D5, click the Name Box left of the formula bar, type Hours and press Enter. Now =SUM(Hours) returns 83. The guide to named ranges in Excel covers creating and editing names, and the comparison of named ranges vs cell references helps you decide when a name is worth it.

Common range mistakes#

These slips cause most wrong results with ranges:

1. A minus sign instead of a colon. =SUM(B2-B5) subtracts B5 from B2 and returns 0, not 29. If you meant to subtract, see the Excel subtract formula; if you meant a range, use the colon.
2. A range that misses a row. =SUM(B2:B4) leaves out Dina. Check that the range covers the last row, especially after adding data.
3. Including the header in a count. =COUNTA(B1:B5) counts the "Mon" header too and returns 5 instead of 4.
4. Forgetting the $ signs before copying. The range slides along and points at the wrong cells.
5. Using a semicolon or comma the wrong way. In some regional settings, Excel uses ; between arguments instead of ,. The colon for ranges is the same everywhere.

Practise it#

Ranges are in almost every formula you'll write, so it pays to make them automatic. Try the exercise on this page, which uses a block range. Then practise selection shortcuts in a live spreadsheet: you do each step and it checks it instantly. Follow it with the SUM lesson to use ranges in your first real totals.