Named ranges in Microsoft Excel simplify data management by assigning meaningful names to specific cell ranges, making formulas easier to understand and maintain.
How to Use Named Ranges in Microsoft Excel
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name.
You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
Guidelines for Names
- The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
- Names cannot be the same as a cell reference, such as Z$100 or R1C1.
- You can use multiple words in a name, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.
- A name can contain up to 255 characters. If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.
- Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
Name Manager
Use the Name Manager to create, edit, and delete range names. The Name Manager provides a complete list of range names in the workbook.
To Name a Range
- Select the cell or range of cells.
- Click in the name box.
- Type the name.
- Press Enter
Edit or Delete Named Ranges
Use the Name Manager to create, edit, and delete range names.
- From the Formulas Tab, in the Defined Names Group, click on Name Manager.
- Select the named range you want to modify.
- Make the necessary changes then close when finished.
Watch the full training
Watch the full training to learn advanced Excel techniques and best practices. You’ll get a quick overview of Excel’s advanced features and common business use cases. No matter what your experience with Excel is, you’ll leave this training with something new.
