How to Create a Range in Excel

In Excel, a range is a group of cells, like A1:A10 or B2:D20. Every formula that adds, averages or looks up values works with ranges. Giving a range a name, such as SalesData or TaxRate, makes formulas easier to read, harder to break, and quicker to write.

This guide shows you how to select a range, turn it into a named range three different ways, use and manage names, make a range that grows automatically, and fill a range with a series of numbers.

How to Select a Range

• Drag: click the first cell and drag to the last.
• Shift-click: click the first cell, hold Shift and click the last.
• Keyboard: hold Shift and use the arrow keys, or press Ctrl + Shift + Arrow to extend to the end of the data.
• Type it: enter an address like B2:D20 in the Name Box to the left of the formula bar and press Enter.
• Several areas: hold Ctrl while selecting to add non-adjacent ranges.

How to Create a Named Range

Method 1: Use the Name Box

1. Select the cells.
2. Click in the Name Box at the left end of the formula bar.
3. Type a name, such as SalesData, and press Enter.

That’s it. The name now appears in the Name Box drop-down whenever you want to jump to it.

Method 2: Use Define Name

1. Select the cells.
2. Go to Formulas > Define Name.
3. Enter a name, choose the Scope (the whole workbook or one sheet), add an optional comment, and check the Refers to box.
4. Click OK.

Both methods are also covered in the support article on creating a named range from selected cells.

Method 3: Create names from headers

If your columns already have headings, select the data including the header row and go to Formulas > Create from Selection. Check Top row and click OK. Excel names each column after its header, so a column headed Price becomes a range called Price.

Naming Rules

• Names must start with a letter, an underscore or a backslash.
• No spaces. Use an underscore or capital letters instead, like Sales_2026 or SalesTotal.
• Names can’t look like a cell reference, such as A1 or R1C1.
• Names aren’t case-sensitive, so Sales and SALES are the same name.
• Each name must be unique within its scope.

Use a Named Range in Formulas

Type the name wherever you’d type the cell address: =SUM(SalesData) instead of =SUM(B2:B50), or =B2*TaxRate where TaxRate is a single named cell. Names act like absolute references, so they don’t shift when you copy the formula. Press F3 while typing a formula to pick from a list of names.

Edit or Delete a Named Range

Go to Formulas > Name Manager or press Ctrl + F3. Select a name and click Edit to rename it or change which cells it refers to, or Delete to remove it. Use the Filter button to find names with errors.

Create a Range That Grows Automatically

A regular named range stays fixed, so new rows below it aren’t included. Two better options:

• Use an Excel table: select the data and press Ctrl + T. Tables expand as you add rows, and formulas can use structured names like Table1[Sales].
• Use a dynamic formula: in Name Manager, set Refers to as =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1). In newer Microsoft 365 versions, TRIMRANGE does a similar job.

Create a Range of Numbers

If you need a range filled with a sequence, like 1 to 100, type 1 in the first cell, then use Home > Fill > Series, choose Columns, set the Step value to 1 and the Stop value to 100. In Microsoft 365 or Excel 2021 and later, =SEQUENCE(100) fills the numbers instead. Use =SEQUENCE(10,1,0,5) for 0, 5, 10 and so on.

Troubleshooting

• “The name that you entered is not valid.” It contains a space, starts with a number, or looks like a cell address. Rename it following the rules above.
• #NAME? error in a formula. The name is misspelled or was deleted. Check Name Manager.
• #REF! in Name Manager. The cells the name pointed to were deleted. Edit the name to point to a valid range.
• New data isn’t included. The named range is fixed. Edit it, or convert the data to a table.
• The same name exists twice. One has workbook scope and one has sheet scope. Excel uses the sheet-level name on that sheet.

Frequently Asked Questions

What’s the difference between a range and a named range?

A range is any group of cells, identified by its address. A named range is a range with a label you can use in formulas and navigation.

Can I name a single cell?

Yes. Named cells are great for constants like tax rates or exchange rates.

Can I use a named range on another sheet?

Yes, if its scope is the workbook. Sheet-scoped names only work on their own sheet unless you prefix them with the sheet name.

How do I find the range of a data set instead?

That’s a statistics calculation: the maximum minus the minimum. The formula is in finding the range of a data set in Excel.

Wrapping Up

Select your cells, type a name in the Name Box and press Enter. Use Define Name for more control, Create from Selection for labeled data, and Name Manager to keep everything tidy. For ranges that need to grow, use an Excel table.