Page 328 - Excel Workbook for Dummies

35_798452 ch24.qxp 3/13/06 7:47 PM Page 311
Chapter 24: Top Ten Tips for Using Excel like a Pro 311
Further, you can define custom data series whose items don’t change by a certain
increment (such as a series of company locations like Chicago, Pittsburgh, Dallas, and
Boston) and then generate the custom series in a spreadsheet by simply entering its
first item in a cell and then dragging the Fill handle as you would with any incremented
series. To create the custom list, you open the Custom Lists tab of the Options dialog
box (Tools➪Options). There, you type the individual items in the series (terminated by
the Enter key) in the List Entries list box in the order in which they are to be generated
with the Fill handle and then select the Add button. Alternatively, if this custom series
already exists in a cell range somewhere in the spreadsheet, select the cell range in the
worksheet after selecting the Import from Cells text box and then select Import.

Use Range Names

The use of range names in a spreadsheet provides several important benefits, perhaps
the most important of which is that you can use the range name to both move and
simultaneously select the cell range in a flash, no matter where this range is located in
the entire workbook. After a range is named, all you have to do to select the range is to
choose its range name from the Name box drop-down list on the Formula toolbar.

This method of selecting cell ranges can be a tremendous time-saver when you’re deal-
ing with a table or list of data that you find yourself referring to or editing on a regular
basis. This is all the more true if the named range’s data tables or lists are located in
columns and rows in some distant region of a worksheet.

Remember that you can use range names to select cell ranges with data tables or lists
that you print on a regular basis. All you have to do is select the cell range with the
Name box, and then open the Print dialog box (Ctrl+P) and select the Selection option
button in the Print What section before you select the OK button.

The second important use for range names is to define and name a constant (such as
a tax rate or interest percentage) whose value you need to refer to in the formulas
you’re about to build in a data table or list but which you don’t want to enter in the
worksheet as a separate cell entry. To do this, open the Define Name dialog box
(Insert➪Name➪Define), and enter the name for the constant in the Names in Workbook
text box. Next, set the name equal to the value or the formula that calculates the value
you want used in the Refers To text box before you select OK. Then, to refer to the
constant in a formula you’re building, select its name in the Paste Name dialog box
(Insert➪Name➪Paste) before you select OK.

The final important use for range names is in documenting the functioning of the for-
mulas in your data tables in a spreadsheet. You can do this by having Excel apply the
table’s row and column headings to the cells of the table. Simply select the table
including the cells with the row and column headings before you open the Create
Names dialog box (Insert➪Name➪Create) and then select the appropriate check boxes
(Top Row, Left Column, Bottom Row, or Right Column) that contain the row and
column headings before you select OK. Then, to replace the cell addresses in the
formulas in this table with the new range names, open the Apply Names dialog box
(Insert➪Name➪Apply) and with the Use the Row and Column Names check box
selected, select the OK button.