- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
This chapter is from the book
This chapter is from the book
This chapter is from the book
Naming Cells and Ranges
To make working with ranges easier, Excel allows you to name them. The name BookTitle, for example, is easier to remember than the range reference B6:B21. Named ranges can be used to navigate large worksheets. Named ranges can also be used in formulas instead of typing or pointing to specific cells. When you name a cell or range, Excel uses an absolute reference for the name by default, which is almost always what you want. You can see the absolute reference in the Refers to box in the New Name dialog box. There are two types of names you can create and use: defined name and table name. A defined name represents a cell, a range of cells, formula or constant, while a table name represents an Excel table, which is a collection of data stored in records (rows) and fields (columns). You can define a name for use in a worksheet or an entire workbook, also known as scope. To accommodate long names, you can resize the name box in the formula bar. The worksheet and formula bar work together to avoid overlapping content.
Name a Cell or Range Using the Name Box
Select the cell or range, or nonadjacent selections you want to name.
Click the Name box on the formula bar.
Type a name for the range.
A range name can include up to 255 characters, uppercase or lowercase letters (not case sensitive), numbers, and punctuation, but no spaces or cell references.
By default, names use absolute cell references.
To adjust the width of the Name box, point between the Name box and the Formula box until the pointer changes to a horizontal
double arrow, and then drag left or right.
Press Enter. The range name will appear in the Name box whenever you select the range in the workbook.
)
Let Excel Name a Cell or Range
- Select the cells, including the column or row header, you want to name.
- Click the Formulas tab.
- Click the Create from Selection button.
- Select the check box with the position of the labels in relation to the cells.
Excel automatically tries to determine the position of the labels, so you might not have to change any options.
- Click OK.
)
Name a Cell or Range Using the New Name Dialog Box
- Select the cell or range, or nonadjacent selections you want to name.
- Click the Formulas tab.
- Click the Define Name button.
- Type a name for the reference.
- Click the Scope list arrow, and then click Workbook or a specific worksheet.
If you want, type a description of the name.
The current selection appears in the Refer to box.
Click the Collapse Dialog button, select different cells and click the Expand Dialog button, or type = (equal sign) followed by a constant value or a formula.
Click OK.
)