Microsoft Office Tutorials and References
In Depth Information
Entering and Sorting Data with Custom Series
have common characteristics. For example, you can click Blanks to find all blank cells in a
range and replace their contents with an error-checking formula or a default value. You
can also find only cells that contain constants or formulas (with options to refine the latter
selection to show only formulas that produce numbers, text, or logical results).
One especially useful expert trick you can perform with this dialog box is to copy data from
a range that includes one or more hidden columns or rows. Normally, copying the current
selection also copies hidden cells, which can lead to unexpected results. Select the range
you want to copy, open the Go To Special dialog box, choose Visible Cells Only, and click
OK. When you paste the Clipboard contents into the destination cells, you’ll see exactly
what you copied and nothing extra.
Entering and Sorting Data with Custom Series
Several chapters ago, we explained how to automatically ill a range with a series of
numbers or dates or with a repeated block of text values. You can also ill in dates using months
or days of the week (in full or abbreviated) and then sort on those dates, with Jan and Feb
coming before Mar and Apr instead of being sorted alphabetically: Apr, Feb, Jan, Mar. (If
you need a refresher course, see “Entering and Filling in Data and Series” on page 352.)
You can add your own custom lists to the collection, specifying the members of the list and
their sort order. You can create a list from any text-based values: department names,
budget categories, countries, geographical regions, and suppliers, to name just a few examples.
Custom lists are stored in the Windows registry and are available for filling in data by row
or column and for sorting data in any workbook. You can add as many custom lists as you
need, entering them directly or copying them from an existing worksheet.
You can view and edit existing custom lists or create new ones by using an obscure
dialog box. Click File, click Options, select the Advanced tab, and scroll nearly to the bottom.
Under the General heading, click Edit Custom Lists. That opens the Custom Lists dialog box
shown in Figure 14-8.
To create a custom list that includes only a few items, select New List from the Custom Lists
box on the left and then type the list directly in the List Entries box, pressing Enter after
each entry. Click Add when you’re finished. To remove an existing list, select its entry in the
Custom Lists box and click Delete.
To create a custom list from a worksheet range, first make sure the range is arranged in
the exact order you want to use for your new custom list. Then click New List, click in the
Import List From Cells box, select the range in the worksheet, and click Import.
Search JabSto ::

Custom Search