Microsoft Office Tips and Tricks

These Microsoft Office Tips and Tricks will help you increase productivity and expertise with the software applications you use each day!

Access

Excel

FrontPage

Outlook

PowerPoint

Word

Access

Customize the query designer for better readability (Access 2003)
In the interest of allowing you to see more text when constructing queries, the query designer uses an 8-point font. If you'd rather sacrifice some characters for easier readability, you can finally do so in Access 2003. To do so, choose Tools | Options from the menu bar and click on the Tables/Queries tab. Simply set the font properties you want in the Query Design Font section and click OK.

Search our course schedule for upcoming Microsoft Access training

TOP

Excel

Use a graphic file as a worksheet background (Excel 97/2000/2002/v. X/2003)
You can easily replace the default white background that Excel applies to all worksheet cells with a picture. To do so, choose Format | Sheet | Background from the menu bar. Then, locate and select a graphic file. Most common file types are supported, including BMP, TIF, JPG, and GIF. Click the Insert button to apply the background. Excel displays the image in a tiled format. Note that the background is only visible onscreen--it won't appear on printouts. To remove a background graphic, choose Format | Sheet | Delete Background from the menu bar.

Refresh PivotTable data automatically (Excel 97/2000/2002/v. X/2003)
You want to ensure that a PivotTable always reflects the most current information if the data on which it's based is volatile. Fortunately, you don't need to remember to refresh the PivotTable if it's been a while since you've used its workbook. Instead, you can configure Excel to automatically refresh the PivotTable when you open the file. To do so:
1. Select a cell in the PivotTable.
2. Open the PivotTable menu on the PivotTable toolbar.
3. Select Table Options.
4. Select the Refresh On Open check box.
5. Click OK.

Easily enter numeric characters as text strings (Excel 97/2000/2002/v. X/2003)
You'll occasionally find that seemingly simple entries cause problems because Excel tries to interpret numeric values as a date. For instance, say that you have a column of product codes with values like 9-12, 10-22, and 8-2099, or you have data labels that describe the age ranges 1-3, 4-6, 7-9, and 10-12. Excel automatically converts the codes to 12-Sep, 22-Oct, and Aug-99. Likewise, Excel converts the range labels to 3-Jan, 6-Apr, 9-Jul, and 12-Oct.

There are a number of ways to avoid the problem, but the easiest is to take advantage of a simple entry shortcut. Simply precede your data with an apostrophe character ('). Doing so forces Excel to treat the entry as text data. For example, enter '8-2099. When you complete the entry, Excel doesn't display the apostrophe in the cell, although you can still see it in the Formula bar.

Note that Excel aligns the entry along the cell's left edge. That's because the entry actually becomes a text value. Because of this, you generally don't want to use this shortcut simply to left-align numeric values. Any formulas that depend on the numeric values could return errors or incorrect results if you do.

Tell a story with pictures (Excel 2007)
One of the most dramatic additions to the Excel 2007 Conditional Formatting menu is the addition of icon sets. These icons are illustrative pictures that you can assign to data values for fast data analysis. The icons range from stop lights to flags to check marks and x’s.

For example, you can assign green, yellow, and red flags to your sales reps’ monthly sales. Green flags indicate sales in the top third percentile, yellow flags indicate sales in the middle percentile, and the red flags designate sales in the bottom third percentile.

Zoom in on your data in seconds (Excel 2000/2002/2003)
If your mouse has a wheel, you probably use it to scroll up and down through lengthy worksheets. You may not realize that you can also use it to change the zoom percentage for your view of the worksheet.

To do so, hold down the [Ctrl] key and move the scroll wheel. Rolling the wheel down decreases the zoom percentage and rolling it up increases the zoom percentage. Note that if you’re using Excel 2000, the zoom will only go up to 100 percent.

If you’d rather have your mouse wheel zoom by default, choose Tools | Options from the menu bar, switch to the General tab, and then select the Zoom On Roll With IntelliMouse check box. After you click OK, rolling the wheel zooms and rolling the wheel while holding the [Ctrl] key scrolls up and down the worksheet.

Search our course schedule for upcoming Microsoft Excel training

TOP

FrontPage

Finding stealth line breaks in FrontPage (FrontPage 2002/2003)
If you paste formatted text into FrontPage 2002 or 2003, a smart tag pops up, asking whether you want to retain the source formatting or not. Chances are, you’ll choose the Keep Text Only option, which strips away all of Word’s extraneous formatting codes.
Unfortunately, when you do so, FrontPage replaces all your paragraph marks (which you ordinarily get by pressing [Enter]) with double line breaks (which you ordinarily get by pressing [Shift][Enter] twice).
What’s the difference? Visually, the result is the same. But when you try to apply formatting to one of the paragraphs, you’ll find that both paragraphs are affected. For best results, then, you’ll want to replace all the double line breaks with paragraph marks.

Publish your website via FTP (FrontPage 2000/2002/2003)

Search our course schedule for upcoming Microsoft FrontPage training

Outlook

Use "Do Not Deliver Before" to postpone the sending of a message. (Outlook 2003)
Often it happens that you have information (and inclination) enough to draft a letter, but don't need to send it just yet. Now Outlook allows you to control exactly when you send out a given message. This may be especailly helpful around holidays or when you go on vacation.
To postpone sending your message:
1. Compose your note
2. Click Options button (above To: field)
3. Select "Do Not Deliver Before" check box
4. Select a date and time from dropdown boxes
5. Click Close and Send the message

Remove a sender from your Outlook junk email list (Outlook 2003)
Sometimes senders that we legitimately want to receive email from get banished to our Junk E-mail folder. Here's an easy way to put a sender back on your safe list.
1. Click on the Mail icon in the Navigation Pane
2. Click on the Junk E-mail folder in the All Mail Folders List
3. In the Message list, click on a message from the sender that you want to deem as safe
4. Choose Actions | Junk E-mail | Add Sender To Safe Senders List
Future messages from this sender should now remain out of the Junk E-mail folder.

TOP

Save a series of Outlook messages in a text file. (Outlook 2000/2002/2003)
Perhaps you often receive information, such as new product numbers, in email. Now several of these emails are clogging up your Inbox, but you can't get rid of them since you repeatedly reference them. You can, however, save all these emails in one text file.
To save the series as a text file:
1. Select the messages you want to save from the Message list (Ctrl-click to select several items)
2. If possible, select the items in the order you'd like them to appear in the text file.
3. Choose File>Save As
4. Enter a name for the file in the Name text box of the Save As dialog box.
5. Browse to the location you'd like to save the file.
6. Click Save.
7. Click Delete to get rid of the selected email messages; you no longer need them.

Search our course schedule for upcoming Microsoft Outlook training

PowerPoint

Making sure your audience can read your slides in PowerPoint. (PowerPoint 2003)
Worried about whether your images and text will be easy enough to read when you give your presentation? A quick way to test if the fonts and sizes you've used are actually legible is to switch to Slide Show view and then stand back 8-10 feet from your computer. Any elements that are hard to read or make out in acceptable detail should be adjusted before it's too late. It's always a good idea to have another person's opinion when testing in this way, since two sets of eyes are better than one!

Easily apply slide transitions to multiple slides (PowerPoint 2000/2002/2003/2004)
You can use the Slide Miniatures pane to select any number of slides and apply transition effects to them. If you're grouping certain related slides together, it's helpful if they share the same type of transition. This gives your audience another cue as to information groupings. Sometimes, however, it might not be apparent which slides go together until you've finished creating your show. For this reason, it's best to make adding transitions one of your final steps. To add a transition to multiple slides, arrange your slides in the order you wish to present. Use the [Ctrl] key to select multiple slides that don't follow one another, or hold down the [Shift] key to select a range of slides. Choose Slide Show | Slide Transition to open the Slide Transition task pane (Slide Transition window in PowerPoint 2000). The transition effects you choose will be applied to the selected slides.

Search our course schedule for upcoming Microsoft PowerPoint training

Word

Change paragraph spacing without leaving the keyboard (Word 2000/2002/2003)
To quickly format a paragraph with single spacing, place the insertion point in the paragraph and press [Ctrl]1. Similarly, you can double-space a paragraph by pressing [Ctrl]2. To achieve the setting in between (1.5 spacing), press [Ctrl]5.

To format multiple consecutive paragraphs at one time using these shortcuts, simply select the entire block of text and press the appropriate keys.

TOP

Avoid the My Documents default (Word 97/2000/2002/2003)
When you choose Save As or Open, Word automatically opens the My Documents folder in the resulting dialog box. If you tend to work from a folder other than My Documents, you’ll save valuable time by setting that primary folder as your default.

To do this, choose Tools | Options from the menu bar, and click on the File Locations tab. Select Documents from the File Types list box and click Modify. In the Modify Location dialog box, open your preferred folder, then click OK. Click OK to close the Options dialog box. Each time you start Word, it opens the folder you designated as the default working folder the first time you access the Open or Save As dialog box.
Regardless of which folder you’ve selected as the default, Word remembers which folder you navigate to during your Word session and sends you there for subsequent saves or opens. For example, if your default is the C: drive, that folder opens when you first launch Word. But if you navigate to a folder on your network to open or save a document, Word opens that folder next time you click Save As or Open. When you close and reopen Word, the Save As or Open dialog box again defaults to the C: drive.

Changing your default font (Word 97/2000/2002/v. X/2003)
If you consistently find yourself changing the font of your Word documents from Times New Roman to another font, consider saving yourself some time by changing the default font. This affects all documents you create based on the Normal.dot template (or another template, if you have another template open when you make this change).
To change the default font, select Format | Font from the menu bar. Make all the changes you’d like to font, style, color, size, and any other category you find in the Font dialog box. Instead of clicking OK when you're done, click the Default button. A message box pops up, informing you of the changes you've requested. Select Yes to make the change. If you select No, Word automatically returns you to the Font dialog box where you can make different selections or click OK and save the changes to the current document only.

Duplicate a drawing object quickly (Word 97/2000/2002/v. X/2003)
Once you've created and formatted a graphic object (such as clip art, WordArt, or a drawing), it's much faster to duplicate that object than it is to create and format another object to match it. Though you can use Word's familiar Copy and Paste features or [Ctrl]-drag ([option]-drag in v. X) to duplicate your graphic objects, there’s an even faster way. Click once on the object to select it, and press [Ctrl]D ([command]D in v. X) to duplicate it. Hold down [Ctrl] ([command] in v. X) and repeatedly press D if you want to create multiple duplicates. If you forget to select the object first, you'll notice that this keyboard shortcut opens the Font dialog box. Also, the object must be located in the drawing layer, not inline, in order for this to work.

TOP

Creating electronic forms in Word
If you're still using Word's bullet symbols to create check boxes for printed forms, it's time to kick it up a notch. Word enables you to create interactive forms that can easily be shared with others and completed electronically.

To create an electronic form, first create a new, blank template or document. (Tip: Frequently used forms are best saved as templates in a network folder or in the user templates folder on your hard drive. Single-use forms or forms that will be distributed via email are best saved as documents.) Next, choose View | Toolbars | Forms from the menu bar to display the Forms toolbar. Design your electronic form just as you would a printed form; however, instead of using tabbed underlines or the like for textual form blanks, click the Text Form Field button on the Forms toolbar to insert a text form field. Similarly, instead of using bullets or symbols to simulate check boxes, click the Check Box Form Field button on the Forms toolbar. You can even add a dropdown list that contains answers to multiple-choice style questions. To do so, on the Forms toolbar, first click the Drop-Down Form Field button, and then click the Form Field Options button. In the Drop-Down Form Field Options dialog box, type one of the choices you wish to appear in the dropdown list in the Drop-Down Item text box, and then click Add. Repeat this process for each item you'd like to add. Use the Move buttons to reorganize the list order if necessary, and then click OK when you've finished.

Once your form is completed, you must protect it so that other users can fill it in. To protect the form, click the Protect Form button on the Forms toolbar. This protects your form elements so that users can add or modify the contents of the form's form fields, but they can't modify any of the form's other information. (Tip: If you'd like to password-protect your form, choose Tools | Protect Document from the menu bar, choose the Forms option button in the Protect Document dialog box, and then provide and confirm your password as prompted.) Save the form as you would any other template or document, and then distribute it as desired.

Print multiple documents from the same folder quickly
(Word 97/2000/2002/2003/Macintosh v. X)
If you need to print multiple Word documents that reside in the same folder, you can send them all to the printer in record time. In fact, you don't even need to open the documents. Simply press and hold the [Ctrl] key (press and hold [command][control] in Macintosh v. X) while selecting each file. (If your files are consecutive, you can press and hold [Shift] while selecting the first and last file.) Once you've selected all the files you want to print, right-click one of the files and select Print from the shortcut menu.

Format the same document in two different ways (Word 2000/2002/2003)
If you’re working on a long document and want parts of the document to have Landscape page orientation, you can do so by following the steps outlined below:

1. Separate the sections that you want to apply different page orientations to by a Next Page Section Break. You can apply a Next Page Section Break by choosing Insert | Break from the menu bar and selecting the Next Page option button under the Section Break Types panel of the Break dialog box.
2. Select the text/pages you want in Landscape orientation and choose File | Page Setup from the menu bar.
3. Choose the Landscape orientation and then, in the Apply To dropdown box, choose Selected Text.

TOP

Search our course schedule for upcoming Microsoft Word training