GuidePedia

0
When working with spreadsheets, naming cell ranges you need to find quickly or print routinely is a really important way to save time. So too, naming cells that are referenced in your master formulas is a really efficient way to document their functioning. Finally, assigning range names to constants that you refer to in formulas (such as a fixed discount rate or rate of growth) is a really resourceful way to have access to a stable value without having to stick it in some cell in the worksheet.
In this tutorial, you find out how to use all three of these procedures to save time in a worksheet that you access and edit on a somewhat regular basis.


Name That Range

At the most basic level, naming a range in Excel is no harder than selecting the range of cells (this can be a single cell, cell range, or nonadjacent cell selection), clicking the Name Box (on the same row as the Formula bar that contains the current cell address), typing a unique descriptive name for the range, and then pressing Enter.
Remember these range name conventions: All range names must begin with a letter of the alphabet, contain no spaces (use underscores, please), and not duplicate any other name in the workbook (keep those names unique).
In fact, I always name single-cell ranges or nonadjacent ranges from the Name Box on the Formula bar. You never see me taking the time to open the Define Name dialog box (Insert → Name → Define) and enter the descriptive range name there.
To quickly select a cell range or nonadjacent selection after naming it, click the drop-down button on the Name Box and then select the selection's name in the drop-down list. To print the cell selection, choose File → Print, click the Selection option button in the Print dialog box, and click OK.

Creating names from row and column headings

Instead of taking the time to use individual descriptive names to assign names to ranges in a standard data table, it's almost always more efficient to have Excel do all the naming for you by using a table's existing row and column headings.
To do this, select the table (including the cells with the row and column heading you want assigned) and then choose Insert → Name → Create to open the Create Names dialog box.
When you first open the Create Names dialog box, Excel automatically selects both the Top Row and Left Column check boxes:
  • When the Top Row check box is selected, Excel assigns the column headings in the first row of your cell selection to the columns of data in the table.
  • When the Left Column check box is selected, the program assigns the row headings in the first column of the cell selection to the rows of the table. (It also assigns the row heading in the top row of the leftmost column to all the rows of data in the entire table.)
If the top row of your table doesn't contain column headings, clear the Top Row check box. Likewise, if its first column doesn't contain row headings, clear the Left Column check box. Also, if your table uses an unusual layout in which the bottom row contains the column headings, clear the Top Row check box and select the Bottom Row one instead. Finally, if the rightmost column of your table contains the row headings, clear the Left Column check box and select the Right Column one in its place.
The range names you assign with the Create Names feature refer only to cells that contain data of the table and do not include the row and column headings at the top and left or bottom and right of the cell selection.
All the range names you assign with Create Names are added to the Name Box dropdown list (on the Formula bar), meaning that you can select their ranges in the worksheet simply by clicking their names on this dropdown list.
When assigning descriptive names to cell ranges in the Define Name dialog box, you never have to include the sheet name; just make sure that the descriptive names are unique. Likewise, when referring to range names in formulas, don't take time to add the sheet reference because Excel keeps track of this automatically.

Assigning range names that span different sheets

The only time that sheet names are really required as part of the range name is when the cell range it refers to spans different sheets of the workbook. In order to name these so-called 3-D references (that is, the same cell range that spans multiple adjacent worksheets), you need to specify the different worksheets involved.
The easiest way to do this is by specifying the sheets in the Define Name dialog box as follows:
  1. Make the first worksheet included in the 3-D reference active.
  2. Choose Insert → Name → Define to open the Define Name dialog box.
  3. In the Names in Workbook text box, type the descriptive name for the range that spans more than one sheet.
  4. Press the Tab key until the Refers To text box is selected and then type = (equal sign).
  5. Select the tab of the active sheet and then hold down the Shift key as you select the tab of the last worksheet to be included.
    When you select the tab of the active sheet, Excel inserts its sheet reference in the Refers To text box. When you Shift+click the tab of the last sheet in the 3-D reference, Excel inserts its sheet reference after that of the active sheet - separated by a colon.
  6. Select the range of cells in the active sheet to be included in all the sheets in the 3-D reference.
    As you drag through the cells in the active worksheet, Excel automatically collapses the Define Name dialog box so that you can see what you're doing. As you select the cell range, Excel inserts its range reference (using absolute cell references) after the sheet range.
  7. Click OK to close the Define Name dialog box.
After naming a 3-D reference, you can use its range name in formulas instead of having to go to the trouble of manually selecting the individual cell range in each sheet. This is a real timesaver when building formulas that accumulate values from different sheets.

Assigning Range Names to Constants

Not only are range names great for selecting and referring to cell ranges in the worksheet, but you can also use them to good advantage by naming constant values that you need to use in your formulas. For example, in the sample spring sale furniture table instead of listing the 15% and 25% discount rates in column D, you can create a range name that holds these constants and then refer to them in formulas that compute the dollar amount of the discount in the cell range D3:D8.
To assign a constant to a range name, follow these steps:
  1. Choose Insert → Name → Define to open the Define Name dialog box.
  2. Type the descriptive name for the new constant (observing the same range-naming conventions) in the Names in Workbook text box.
  3. Press Tab until the Refers To text box is selected. Then type = (equal sign) and enter the constant value you want to assign to this name.
  4. Click OK to close the Define Name dialog box.
If you need to define several constants at once, click Add instead of OK in Step 4 to insert each name in the Names in Workbook list box. When you're finished defining the constants, click OK.

Using Your Range Names in Formulas

The great thing about using range names in your formulas is that they automatically document their function. This is especially helpful to coworkers who have to use your spreadsheets but who had no part in their design. When using names in your formulas, you can assign the names as you construct new formulas in the worksheet, or you can add them after the fact to existing formulas.
Following these sections:
Using range names in new formulas and Assigning range names to existing formulas

Using range names in new formulas

If you named a cell range or constant that you need to refer to in a new formula, you can use its name when building a formula. To enter the name, you can either type it (risky business if you have any trouble remembering the exact name), or you select its name from the Paste Name dialog box.
Selecting the name from the Paste Name dialog box is much easier - especially when dealing with constants that don't show up anywhere on the worksheet - unless you've pasted a list of range names somewhere within it (Insert → Name → Paste, Paste List).

Assigning range names to existing formulas

Excel doesn't automatically replace cell references with the range names that you assign to them. To replace cell references with their names, you need to use the Insert → Name → Apply command. Then in the Apply Names dialog box that appears, you select the range names that you want applied in your worksheet formulas by selecting them in the Apply Names list box.
When you first open the Apply Names dialog box, it contains just two check boxes: Ignore Relative/Absolute and Use Row and Column Names (both of which are checked). When you click the Options button, Excel expands the Apply Names dialog box to display additional options that you can use when applying your range names.
The complete Apply Names options include the following:
  • Ignore Relative/Absolute: Select this check box to replace cell references with the names that you've selected in the Apply Names list box regardless of the type of reference used in their formulas. Clear this check box if you want to replace only those cell references that use the same type of references as your names (absolute for absolute, mixed for mixed, and relative for relative).
    Most often, you want to leave this check box selected because Excel automatically assigns absolute cell references to the names that you define and relative cell references in the formulas that you build.
  • Use Row and Column Names: Select this check box to have the names appear in your formula that you created from row and column headings with the Create Names feature. Clear this check box if you don't want these row and column names to appear in the formulas in your worksheet.
  • Omit Column Name if Same Column: Select this check box to prevent Excel from repeating the column name when the formula is in the same column. Clear this check box if you want the program to display the column name even in formulas in the same column as the heading used to create the column name.
  • Omit Row Name if Same Row: Select this check box to prevent Excel from repeating the row name when the formula is in the same row. Clear this check box if you want the program to display the row name even in formulas in the same row as the heading used to create the row name.
  • Name Order: You have two choices here:
    • Row Column: Click this option button (the default) to have the row name precede the column name when both names are displayed in the formulas.
    • Column Row: Click this option button to have the column name precede the row name.
For example I created range names for the sales table by selecting the range A2:E8, opening the Create Name dialog box, and using the Top Row and Left Column defaults (see "Creating names from row and column headings" earlier in this tutorial). Next, I selected the cell range D3:E8, which contains all the cells with formulas in this table, and then opened the Apply Names dialog box (Insert → Name → Apply).
Because I wanted the row headings included in the formula's range, I clicked the Options button to expand the Apply Names dialog box and then cleared the Omit Row Name if Same Row check box and clicked OK. As a result, Excel inserted the code range name in the formulas. For example, the formula in cell D3 now contains
=_12_305 Retail_Price*discount_25 If I had left the Omit Row Name if Same Row check box selected in the Apply Names dialog box, the formula in D3 would instead read
=Retail_Price*discount_25 The problem with omitting the row heading from the formulas is that all the formulas in column D would then read the same as the one in D3. By adding the row heading, anyone using the spreadsheet can easily verify the precise function of each discount formula.
The only problem with including the row or column headings is that, in formulas that refer to more than one cell in the same row or column, the repeating of these headings can make the formulas long and cumbersome to decipher (thus defeating the goal of using range names to document their function).
For example, the formula in cell E3 that computes the sales price for the 36-inch round table with the code 12-305 now reads
=_10_235 Retail_Price-_10_235 Discount In such cases, you may be better off using the Apply Names default settings that omit all repeated headings to create a much cleaner, albeit generic form of the formula. In the case of E3, the sales price formula created by omitting the row heading would be much simpler:
=Retail_Price-Discount Just keep in mind that this is how all the rest of the sales price formulas in cells D4, D5, D6, D7, and D8 appear.





Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks

Visitors

Cloud Power For You

Website Hosting At Low Price

Contatc

Empire Views
 
Top