Midlands Microsoft Office Provider

2.5 Customizing a Table – Creating Names Using Name Box – Practice Exercise 12 – Intermediate Excel 2016

Creating Names for Ranges and Tables Using the Name Box Feature
The Name Box allows you to assign descriptive names to cells and ranges, regardless of whether you are only using a range of cells or a table. Working with these names instead of cell/range addresses has the following advantages:

  • A meaningful range name, such as Total Income, is much easier to remember than a cell address such as A24.
  • Entering a name is less error prone than entering a cell or range address, and if you type a name incorrectly in a formula, Excel will display a #NAME? error.
  • You can quickly move to areas of your worksheet using the Name box instead of hunting around sheet after sheet looking for a cell or range.
  • Names make your formulas more understandable and easier to use. A formula such as =Income – Taxes is more intuitive than =D20-D40.
  • A few rules about creating a name for the Name box:
  • Names cannot contain any spaces. The underscore is often used to substitute for a space.
  • You can use any combination of letters and numbers but the name must begin with a letter, underscore, or backslash. The name cannot begin with a number (such as 3rdQuarter) or look like a cell address (AN3). However you can precede these names with an underscore or backslash, i.e. _3rdQuarter. The only symbols that are allowed are the backslash, underscore, and period; any other symbol is not permitted.
  • Names are limited to 255 characters, but it’s a good practice to keep names as short as possible, but still meaningful.


Fastest Way to Use the Name Box
The fastest way to create a name is to use the Name box directly. Select the cell or range that you want to assign a name to, click the Name box, and type in the name. Press the Enter key to create the name.

Using the New Name Dialog Box
A more comprehensive way of assigning names to cell or range is to use the New Name dialog box, which includes assigning a name, scope (name available to entire workbook or specific worksheet only), comments for describing what the name is being used for, and a refers window, which references the cell address or cell range.

​The Name Manager
The Name Manager is a dialog box that shows a list of all of your Name box names used for that workbook or specific worksheet by showing its scope. It also shows the value and the range or cell address referenced. You can edit a name if you want to change it to a different name, change the range of cell(s) that the name refers to, increase/decrease its scope, or simply delete the name entirely.

​Assigning Names to Ranges
When you assign a name to a range, you can substitute that name for a range of cells in a formula.
A meaningful range name, such as Total Income, is much easier to remember than a cell address such as A24.
Names make your formulas more understandable and easier to use. A formula such as:  = Income – Taxes is more intuitive than =D20-D40.
Rules for Creating Names

  • Names cannot have any spaces.
  • Use the underscore in place of a space (Shift + hyphen) (This_is_usage_of_an_underscore.)
  • You can use any combination of letters and numbers but the name must begin with a letter, underscore, or backslash.
  • The only symbols that are allowed are the backslash, underscore, and period; any other symbol is not permitted.
  • Do not create a name that looks like a cell address (I.E. AC3).
  • This is not a really a rule but if you relate the name to what the cells represent it will help.

 

Practice Exercise 12: Assign names to ranges and use the names in formulas.
STEP 1: Still using data file, FIRST RANGE TO TABLE EXERCISE, Click on NAME USAGE SHEET 2. Highlight the range of cells that will be assigned a name: G2:G6 (FIGURE 1)
                                                                              FIGURE 1
STEP 2: Locate the Name Box (FIGURE 2). It will contain the cell address of the current active cell. (it’s to the left of the Formula Bar).

​
STEP 3: You can widen the Name Box by moving mouse up to the 3 black dots on the right of the Name Box (FIGURE 3). When you see the mouse symbol turn into a white arrow   click and drag to the right to increase width.
FIGURE 2
FIGURE 3
STEP 4: Click on the small black arrowhead located on the right side of the Name Box.  When you click on the arrowhead, the cell address in the Name Box will highlight and the name menu will drop downward.

STEP 5: Since the cell address is highlighted, you can now enter the name, “PREVIOUS_QTR_SALES” (without the quotes).  (FIGURE 4) Select the Enter key.
FIGURE 4
STEP 6: Click on the sheet, NAME USAGE SHEET 1. Make cell B8 the active cell. Enter formula:

​
STEP 7: =AVERAGE(PREVIOUS_QTR_SALES). You can also select it from the drop-down menu by double-clicking with mouse ​(FIGURE 5). Press Enter.

​The results are shown in FIGURE 6.
                  FIGURE 5
                                                                                      FIGURE 6
This is the end of this section. To continue, go to Module 2 Section 2.6 Customizing a Table – Assigning Names to Ranges; Practice Exercise 13