Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

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

  • lightyellow_01.jpg Select the cell or range, or nonadjacent selections you want to name.
  • lightyellow_02.jpg Click the Name box on the formula bar.
  • lightyellow_03.jpg 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.

  • lightyellow_04.jpg 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.
  • lightyellow_05.jpg 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

  • lightyellow_01.jpg Select the cells, including the column or row header, you want to name.
  • lightyellow_02.jpg Click the Formulas tab.
  • lightyellow_03.jpg Click the Create from Selection button.
  • lightyellow_04.jpg 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.

  • lightyellow_05.jpg Click OK.

Name a Cell or Range Using the New Name Dialog Box

  • lightyellow_01.jpg Select the cell or range, or nonadjacent selections you want to name.
  • lightyellow_02.jpg Click the Formulas tab.
  • lightyellow_03.jpg Click the Define Name button.
  • lightyellow_04.jpg Type a name for the reference.
  • lightyellow_05.jpg Click the Scope list arrow, and then click Workbook or a specific worksheet.
  • lightyellow_06.jpg If you want, type a description of the name.

    The current selection appears in the Refer to box.

  • lightyellow_07.jpg 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.
  • lightyellow_08.jpg Click OK.
  • + Share This
  • 🔖 Save To Your Account
Microsoft Excel 2010 On Demand

This chapter is from the book

Microsoft Excel 2010 On Demand

Discussions

comments powered by Disqus