Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 93 ยท Formulas Fundamentals

Ranges and Named Ranges

Learn Excel ranges (B2:B9, whole columns and rows, 3D references) and how to create named ranges and named constants with the Name Manager.

Most Excel formulas work on groups of cells rather than single ones: add up a column, find the largest value in a block, look something up in a table. A group of cells is called a range. Giving an important range a human-readable name, such as Sales or TaxRate, makes formulas shorter and much easier to read.

This lesson covers how ranges are written, what whole-column, whole-row and 3D references mean, how to create names with the Name Manager, and how a name's scope decides where it works. Formulas in code blocks are real Excel syntax. The live editors use a small stand-in engine that resolves ranges and names the way Excel does, so you can check the results.

Ways to point at cells

One cell B2
A block B2:D9
Whole column B:B
Across sheets Jan:Mar!B2

A colon means "from here to there".

1 / 2

Range notation

A range is written as two corner cells separated by a colon: B2:D5 is the rectangle from B2 (top left) to D5 (bottom right). Excel does not care which corners you give first: D5:B2 is the same range, and Excel rewrites it as B2:D5.

TEXT
=SUM(B2:B5)        a vertical range, one column
=SUM(B2:D2)        a horizontal range, one row
=SUM(B2:C3)        a block, 2 columns by 2 rows
=SUM(B2:B5, D2:D5) two ranges at once (the comma is the union operator)

You do not have to type ranges. Click the first cell, hold Shift and click the last, or press Ctrl+Shift+Arrow to extend the selection to the edge of the data. While you build a formula, dragging across cells inserts the range for you.

Whole columns and rows

Writing B:B means every cell in column B, and 2:2 means every cell in row 2. B:D is three whole columns. These are convenient because new data added at the bottom is included automatically:

TEXT
=SUM(B:B)      everything in column B
=SUM(2:2)      everything in row 2
=COUNTA(A:A)   how many cells in column A are not empty

Text in the range is ignored by SUM, so a header such as Sales in B1 does not break the total. One caution: a whole-column reference covers more than a million cells. For SUM that is fine because Excel only looks at cells that are in use, but with some heavy formulas (array calculations, many lookups) it can slow a workbook down. Performance and Spreadsheet Best Practices returns to this.

Union and intersection

Besides the colon and the comma, there is a third range operator: the space. It means intersection, the cells that two ranges have in common:

TEXT
=SUM(B2:C5 C4:D8)   adds only the cells in C4:C5
=SUM(A1:A3 C1:C3)   the ranges share no cells, so the result is #NULL!

You will rarely write intersections, but when you see a #NULL! error, a missing comma or colon between two references is the usual reason.

Seeing ranges resolved

The stand-in below resolves ranges against a small sales sheet. It treats B5:B2 like B2:B5, includes a whole column and a whole row, and ignores the text headers during SUM. It also contains names, which the next section explains.

JS Ranges, whole columns and names in a mini engine

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that SUM(sales) works with a different capitalization (names ignore case) and that the misspelled Sale gives #NAME?. That error means "Excel does not recognize this word as a function or name", which is exactly the safety net that makes names pleasant to use.

Named ranges

A name is a label you attach to a cell, a range or even a value. There are three quick ways to make one:

  1. Select the cells, click the Name Box (left of the formula bar), type a name and press Enter.
  2. Select a table with headers and use Formulas โ†’ Create from Selection (Ctrl+Shift+F3) to make a name for each column from its header.
  3. Open the Name Manager (Ctrl+F3, or Formulas โ†’ Name Manager) and click New to set the name, scope and what it refers to.

The Name Manager lists every name in the workbook, what each refers to, and its scope. You can edit or delete names there, and filter the list, for example to find names with errors. Once defined, a name works anywhere a reference works: =SUM(Sales). Press F3 while writing a formula to pick from the list, and use the name box dropdown to jump to the cells.

Rules for names

A name can contain letters, numbers, underscores and periods. It must start with a letter, an underscore or a backslash, and it cannot contain spaces (use Tax_Rate). It also cannot look like a cell address: Q1, A1 and even TAX2024 are all valid cell addresses, so they are refused. The single letters R and C are reserved because they mean row and column in R1C1 style. Names are not case sensitive.

Named constants

A name does not have to point at cells. You can define TaxRate as =0.08 in the Name Manager's "Refers to" box. Then =B2*(1+TaxRate) reads naturally, and changing the rate means editing one place. A constant name has no cell to look at, so for numbers that people need to see and change, a labeled cell with a name is often friendlier than a hidden constant.

Scope

Every name has a scope: the whole workbook or one worksheet. A workbook-scoped name works on every sheet. A sheet-scoped name works on its own sheet, and from other sheets only when you qualify it, as in Jan!Rate. If both exist, on the sheet itself the sheet-scoped name wins. This is useful when each month's sheet has its own Rate, but it is also a classic source of confusion, so use sheet scope sparingly.

The second stand-in has no formula engine. It checks whether a name is allowed and shows how scope is resolved. It also demonstrates a 3D reference, covered next.

JS Valid names, scope and a 3D sum

Press Run (or Ctrl + Enter) to see the console output here.

No output.

3D references

A 3D reference covers the same cell or range on a run of worksheets. =SUM(Jan:Mar!B2) adds B2 on Jan, Feb, Mar and every sheet whose tab sits between them. It is a handy way to total identical monthly sheets, but the result depends on the tab order, as the stand-in shows: drag a sheet out of the span and its numbers disappear from the total, drag one in and it is counted. A common habit is to add two empty "bookend" sheets (Start and End) and write =SUM(Start:End!B2); any sheet dropped between them is included.

Not every function supports 3D references. SUM, AVERAGE, COUNT, MAX and MIN do. Lookup functions and many others do not.

Good habits with names

  • Prefer descriptive names (Commission_Rate) over cryptic ones (x1).
  • Delete unused names; broken ones show #REF! in the Name Manager and are a common source of clutter in inherited workbooks.
  • Do not make a name for every cell. Names shine for ranges used many times, constants and inputs.
  • Excel Tables (see Excel Tables) give you names for entire columns that grow automatically, so many "dynamic named range" tricks are no longer needed.
  • Use cell references with $ for one-off formulas, and names for things that deserve a label.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the range D5:B2 refer to?

    Excel normalizes ranges, so the corner order does not matter. D5:B2 is the rectangle B2:D5.
  2. 2. Which of these is NOT allowed as a name?

    Q1 looks like a cell address (column Q, row 1), so Excel refuses it. Underscores and periods are fine.
  3. 3. A name is workbook-scoped. On which sheets can you use it without any prefix?

    Workbook scope means every sheet sees the name. A sheet-scoped name works unqualified only on its own sheet.
  4. 4. =SUM(Start:End!B2) is a 3D reference. What decides which sheets are included?

    A 3D reference includes every sheet whose tab lies between the two named sheets, so moving tabs changes the result.
  5. 5. A formula contains =SUM(Sale) but the name defined is Sales. What does Excel show?

    #NAME? means Excel does not recognize a word in the formula as a function or defined name.

Summary

  • A range is two corners joined by a colon (B2:D5); corner order does not matter. B:B is a whole column and 2:2 a whole row.
  • The comma joins ranges (union); a space intersects them, and no overlap gives #NULL!.
  • Names make formulas readable. Create them in the Name Box, with Create from Selection, or in the Name Manager (Ctrl+F3).
  • Names cannot contain spaces or look like cell addresses; they are not case sensitive, and a misspelled name gives #NAME?.
  • A name can refer to a constant. A name is workbook-scoped or sheet-scoped; the sheet name wins on its own sheet.
  • A 3D reference such as Jan:Mar!B2 covers all sheets between two tabs and depends on tab order.

Next: SUM, AVERAGE, COUNT, MIN and MAX.