How to Create Named Ranges in Excel 2007

By Contributing Writer

Microsoft Excel is a spreadsheet program used to store, organize, analyze, manipulate and chart data. When you create a named range in Excel, you assign a descriptive, user-friendly name to a cell or a range of cells that replace the original cell reference (i.e. A1, B3, C7, etc.). Naming ranges can make it easier to understand the data in the spreadsheet--especially the purpose of formulas, which can have very long and somewhat obscure cell references.

Step 1

Launch the Microsoft Excel program by double-clicking the program’s shortcut on the desktop or by selecting “Microsoft Excel” from the “Start”, “Programs” or “All Programs” menu.

Step 2

Click the “Office Button” in the upper left corner of the program window and select “Open.” Click the down arrow to the right of the “Look in” text box to browse to the folder that contains the file you want to work with. Select the file, and click “Open.” If you are creating a new spreadsheet, Click the “Office Button” and select “New.” Select “Workbook” and click “OK.” A blank spreadsheet should be displayed. Enter your data.

Step 3

Select the cells you are interested in using in the named range. Click the “Formulas” menu on the menu bar. Click the “Define Name” button on the ribbon to open the “New Name” dialog box. Click in the “Name” text box and type a name of your choice. Click “OK” to define the name and return to your spreadsheet. The name of your range should appear in the “Name Box" in the upper left corner of the program window.

Step 4

Click somewhere outside the named range to deselect it. Look at the “Name Box.” The name you created has been replaced by the location of the cell you are in.

Step 5

Click the down arrow to the right of the “Name Box” and select the name you created. The name reappears in the “Name Box,” and the name range becomes selected.

Step 6

Click the “Name Manager” button on the ribbon to view all of the names in your Workbook. Select a name to view the cells it references. Notice that the range of cells that the name refers to can be changed here. Click “OK.”

Step 7

Click the “Office Button” and select “Save” to save the spreadsheet. If you are prompted with the “Save As” dialog box, navigate to the location you want to save the file in, enter a name for the file in the “File Name” text box, and click “Save.”

Tips & Warnings

  • The name of a range cannot contain spaces or characters such as "*," "&" or "%."
  • The first character of a named range must be a letter, an underscore or a backslash.
  • You can paste a list of the names in your spreadsheet by pressing "F3" on the keyboard and then selecting "Paste List."