Word

Just when you thought we'd shown you every timesaving keyboard shortcut

Word has to offer, we managed to find a few more jewels for you and

your keyboard-centric users.

How can you change the indentation of selected paragraphs in Word? Let

us count the ways.

1. Use the Paragraph dialog box. (Select Format | Paragraph.)

2. Use the Formatting toolbar's Increase Indent and Decrease Indent

tools.

3. Press [Ctrl]E for center justification.

4. Press [Ctrl]J for full justification (justification to both the left

and right margins).

5. Press [Ctrl]L for left justification.

6. Press [Ctrl]R for right justification.

7. Press [Ctrl]M as often as you want to indent your text from the

left. (The whole paragraph gets indented.)

8. Press [Ctrl]T as often as you want to create a hanging indent from

the left. (Every line but the first line gets indented.)

9. Press [Shift][Ctrl]M to decrease the left indent.

TROUBLESHOOTING ERRORS WHEN DELETING

In "Deleting from the Open dialog box" (Dec. 28, 2000), we reminded

novice users that Word's Open dialog box lets you perform routine file

maintenance chores such as deleting, copying, or renaming files. But

you'll need to look out for an error message that frequently befuddles

new users. Let's look at how to resolve the problem when one of your

users runs into this error.

Here's the scoop: If you use File | Save As to change an existing

file's type, you won't be able to delete the old file (from within

Word) until you close the new version. Let's look at an example:

Create a new document, type some text, and save the file. Then, go to

File | Save As and press [Tab] to select the Save As Type field. Now,

select a different type. For example, if the file is currently saved as

a Word document (.doc), change it to Rich Text Format (.rtf).

Now, try to delete the old document. Press [Ctrl]O or go to File | Open

to summon the Open dialog box. Locate and select the "old" file and

press [Delete], or right-click and select Delete from the shortcut

menu. You'd probably assume that the old document is completely closed

and able to be deleted. However, you'll get an error message.

If you positively must delete that old file, the workaround is simple:

Close the current document. Then the old document will be released, and

CREATE PHANTOM TOC ENTRIES

The typical table of contents (TOC) contains items that appear again

later in the document text--chapter titles, headings, subheadings, and

words or phrases that you "mark" as TOC entries. But if you want to use

your TOC for more than just a document map, you can beef it up with

"phantom" entries.

By phantom entries, we mean text that exists only in the TOC--and it

doesn't appear anywhere else in the document. Why would you want to use

such entries? First, if your document's section headings are on the

boring side, phantom entries can let you add some pizzazz to your TOC

without disturbing the text.

Second, if your document contains no formal chapter or section headings

on which to base a traditional TOC, you don't have to generate your

index manually. You can use phantom TOC entries to create a first-rate

document map.

To create a phantom TOC entry, go to Insert | Field and select Index

And Tables from the Categories list. Select TC from the Field Names

list, and Word will enter that field in the text box. Click in the text

box, type "your custom entry," and click OK to enter this TOC entry.

This field will be invisible in your printed documents, but it will

CREATE GRAPHICAL TOC ENTRIES

Last time, we showed you how to enhance your table of contents (TOC) with

"phantom" entries. Today, we've got another great tip for customizing

your TOC: Go graphical!

Suppose you're documenting a Web site, and you want to use the user-

friendly "left nav" buttons as the chapter headings. It's easy enough

to put those buttons in your document. Just open the Web page, right-

click the button, and choose Copy--then paste that image into your

document in its own paragraph. (That is, don't paste the image into an

existing block of text.)

Right-click the image, choose Format Picture, select the Position tab,

and deselect the Float Over Text check box. (This check box isn't

available in Word 2000; instead, choose the Layout tab of the Format

Picture dialog box and select the Inline With Text option.)

Next, click outside the left margin to select the paragraph that now

contains the image. Finally, select one of the Heading styles from the

Standard toolbar's Style drop-down list.

The next time you generate a TOC, a copy of the image

will appear as an entry. Word will indent the image based on the

heading style you assigned. The image will appear in the TOC exactly as

it appears in your document, but you can resize the TOC image if you

need to. (If you rebuild the index later, Word will copy the original

Excel

Trailing spaces can wreak havoc on the results of your vertical

lookups. Here's a case study illustrating how to identify and fix those

problems. A user calls and gives this history: "My VLOOKUP formulas

looked right, but when I spot-checked the results, I had lots of #N/A

errors. And they weren't all caused by the same lookup value."

To troubleshoot and resolve this problem, eyeball the entries in the

lookup table and compare them to the values being looked up. Are words

spelled and punctuated consistently? (If not, fix the typos.)

Next, select one of the cells containing an entry that generated the

#N/A error. Press [F2] to edit the contents of the cell. If the

insertion point (the flashing cursor) isn't immediately to the right of

the last visible character in the cell, then this entry contains dirty

data in the form of trailing spaces. (If the cell generating the error

isn't the culprit, it's probably the corresponding entry in the lookup

table itself.)

So how do you clean up that dirty data and remove the trailing spaces,

either in the lookup range or in the lookup table (or both)? One

solution is to use the TRIM worksheet function. Follow these steps:

1. Insert a "dummy" column beside the column of dirty data.

2. Copy the formula =TRIM(cellref) down the dummy column, where cellref

is the first cell of the lookup range. The TRIM function removes all

leading and trailing spaces from the strings in the target cells.

3. Overwrite the corrupt lookup column with the trimmed data from the

dummy column. (To do so, select and copy the clean data. Click the cell

at the top of the old data, and go to Edit | Paste Special | Values.)

CLEANING UP DIRTY TEXT

Have you ever pasted text into an Excel sheet, only to discover that it

contains "garbage" characters? Typically, those characters appear as

small, hollow squares intermixed with alphanumeric characters, and you

see them frequently when you paste content you've copied from a Web

page into your spreadsheet.

To get rid of those unwanted characters, you could edit each cell and

delete them manually, but that approach takes time. And you can't copy

and paste one of those characters into the Replace dialog box's Find

What field, either. (If you could, you could globally replace them with

"nothing" to clean up your text.)

The answer is the Clean function, which removes unprintable characters

from text strings. To use it, insert a new column next to the column

that contains the dirty text.

Let's say your text starts in cell B2. In cell A2, enter the formula

=CLEAN(B2), and then copy that formula down column A. The formula

returns a copy of the text without the garbage characters. To overwrite

the bad text with the clean text, copy the formulas in column A by

selecting Edit | Paste Special | Values. Then you can safely discard

TARGETED SEARCHING AND REPLACING

If you need to make global changes to a worksheet, you probably press

[Ctrl]H to open the Replace dialog box, enter the appropriate

characters in the Find What and Replace With text boxes, and click

Replace All. Doing so changes every occurrence of the "find" string in

your worksheet, even in formulas.

But what if you want to make the replacements in a specific column or

range of cells? You could summon the Replace dialog box and use the

Find Next and Replace buttons to cycle through each occurrence of the

"find" string in your sheet. That approach takes time, however, because

you must carefully scrutinize each item that Excel finds before you

click the Replace button.

To save time, select the column, row, or block of cells that contain

the entries you want to change. Then open the Replace dialog box, enter

the find and replace strings, and click Replace All. When you do, Excel

will make the replacements only in the cells you selected, ignoring the

SAVE TIME WITH THE STATUS BAR

Performing mathematical operations on columns or rows of cells is easy.

You enter the appropriate formula, supply the appropriate range, and

Excel does the calculations for you.

Sometimes, though, you just want to sum (or average or count) the items

in a small subset of cells in your worksheet. You could eyeball the

sheet and do your calculations manually, or you could copy the cells

you're interested in to a new sheet and perform your calculations

there. Fortunately, the status bar in Excel 97 and 2000 makes "custom

math" operations easy. Here's how it works.

Open the View menu and make sure that Status Bar is selected. Next,

select any block of cells in your worksheet. Look at the bottom-right

side of your Excel window. If you don't see any text (other than Ready

on the bottom-left side), right-click the status bar.

When you do, Excel will display these options: None, Average, Count,

Count Nums, Max, Min, and Sum. Select any of these options, and Excel

will display the results of that calculation in the status bar. Select

a different range of cells, and watch the value in the status bar

change.

The cells you select don't even have to be contiguous. Hold down [Ctrl]

while you click cells to select them, and the status bar will reflect

Access

ZOOMING IN ON DATE RANGES

Access offers a number of useful date functions you can use to create

expressions to isolate records with date stamps that fall within a

particular period of calendar time. Of course, you can always hard-code

your expressions with the actual dates involved or write a routine that

prompts the user for a beginning and ending date.

But if your users routinely run queries that are relative to the

current date, here are six expressions you can use, based on a specific

date field in your table, to locate records from a given period of

time.

* To find all records date-stamped in the previous seven days

(excluding today), try this expression:

Between Date()-1 And Date()-7

* To access all records date-stamped last week, use this:

Between Date()-6-WeekDay(Date()) And Date()-WeekDay(Date())

* To locate all records from the current month, try this expression:

Month([myDateField])=Month(Date()) And Year([myDateField])=Year(Date())

* To access all records from the previous month, use this:

Month([myDateField]) =IIf(Month(Date())=1, 12, Month(Date())-1) And

Year([myDateField]) =IIf(Month(Date())=1, Year([myDateField])-

1,Year(Date())

* To locate all records from the current year, try this expression:

Year([myDateField])=Year(Date())

* To find all records from the previous year, use this:

Year([myDateField])=Year(Date())-1

In the expression to find records from a previous month, we used the

"immediate if" function to set the month value to 12 when the current

month is January. In all other cases, we simply subtract one from the

current month. We included the And clause so that, when the current

RESOLVING IMPORT PROBLEMS

"[Access] won't let me import the records into an existing table,"

Kevin wrote. "I go to File | Get External Data | Import to start the

wizard. When I would normally expect to be able to choose an existing

table, Access 2000 'grays out' that option and keeps prompting me to

import the records into a new table. What am I doing wrong?"

Fortunately, there was an easy explanation and fix for Kevin's problem.

It turns out that there were two issues with the spreadsheet. First,

there were fewer columns of data in the sheet than there were fields in

the Access table. Second, Kevin had not entered column labels in the

spreadsheet.

Here's the lesson learned: Access 2000 (and 97 as well) will let you

import data from a source file that contains fewer columns than the

table contains fields. But the catch is that you must label those

columns of data. Otherwise, Access doesn't know which fields to use for

storing the external data.

So if you want to import records from an Excel spreadsheet, make sure

the first row contains column labels that match the field names in your

PROVIDE CUSTOMIZED TIP TEXT

Do you support users who have trouble remembering what the fields (or

other objects) on your forms mean? If so, here's an easy way to help

those users: Teach them to mouse over a field (or any other control on

a form) and wait for the tip text to appear.

You can refer to this kind of tip text as a "control tip," and you can

customize any control to display custom help text. Just open your form

in Design view, right-click any control, and choose Properties. You can

find the ControlTip Text property on the Other tab. Enter the text you

want to appear. When the user parks the mouse over the control for at

least a second or so, the tip text will appear in a box with a light

GO WILD WITH SELECT QUERIES

When you create a simple select query, you aren't limited to entering

literal values in the Criteria row. Access 97 and 2000 support a number

of wildcards you can use, along with the Like operator, to ferret out

groups of related records.

To begin, create a new query in Design view, add a table, and select at

least one destination field. In the Criteria field, enter the string

you want to use as the criteria for the query.

If you've used DOS, you're probably familiar with two wildcards

supported by Access: ? and *. The question mark represents any single

alphanumeric character, while the asterisk represents any number of

characters.

For example, the expression Like "M?." locates records containing Mr.

or Ms., but the expression Like "M*." locates records containing Mr.,

Ms., Mrs., or Messrs. Then there's the pound sign [#], which is similar

to the question mark wildcard--except that it represents digits only

(i.e., 0 to 9).

One of the most powerful tools for select queries is the open and close

brackets, which will find any characters listed inside the brackets.

When you use this operator, Access locates records that match any of

the characters in the list. You typically use this operator in

conjunction with other wildcards. For example, the expression Like 

"[A-C]*" locates records where the entry starts with A, B, or C.

Use the exclamation point [!] to create a "not like" expression. For

instance, Like "[!A-C]*" locates all records that start with anything