eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Part I MICROSOFT WORD TRICKS
Chapter 1 Text Tricks
Chapter 2 Formatting Tricks
Chapter 3 Document Tricks
Chapter 4 Page Layout Tricks
Part II MICROSOFT EXCEL TRICKS
Chapter 5 Formula and Function Tricks
Chapter 6 Workbook and Worksheet Tricks
Chapter 7 Data Analysis Tricks
Chapter 8 Chart Tricks
III MICROSOFT POWERPOINT TRICKS
Chapter 9 Slide and Presentation Tricks
Chapter 10 Animation Tricks
Chapter 11 Slide Show Tricks
IV MICROSOFT OUTLOOK TRICKS
Chapter 12 Email Tricks
Chapter 13 Calendar and Contacts Tricks
V MICROSOFT ACCESS TRICKS
14 Table and Query Tricks
Chapter 15 Form and Report Tricks
APPENDIXES
Author Bio
Paul McFedries is well-known as a teacher of Office, Windows, and programming, particularly VBA. He is the president of Logophilia Limited, a technical writing company. Paul has been writing programs for PCs for more than 25 years and has been developing VBA applications since Microsoft first added VBA to the Office suite in 1994. Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries (Que, 2007), Formulas and Functions with Excel 2007 (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).
Product Details
| ISBN-13: | 9780132714716 |
|---|---|
| Publisher: | Pearson Education |
| Publication date: | 05/16/2007 |
| Series: | Business Solutions |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 504 |
| File size: | 21 MB |
| Note: | This product may take a few minutes to download. |
| Age Range: | 18 Years |
About the Author
Paul McFedries is the president of Logophilia Limited, a technical writing company.
Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer,
and Web site developer. He has written more than 50 books that have sold more than
three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries
(Que, 2007), Formulas and Functions with Excel 2007 (Que, 2007), VBA for the 2007
Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).
Table of Contents
Part I MICROSOFT WORD TRICKS
Chapter 1 Text Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Keeping Table Headings in View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Summing a Column in a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Calculating Tax in an Invoice Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Highlighting All Instances of a Word or Phrase . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Using AutoCorrect to Insert Your Signature . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
Using AutoCorrect to Enter Boilerplate Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
Saving Boilerplate as an AutoText Building Block . . . . . . . . . . . . . . . . . . . . . . . . . .16
Creating a “Click-and-Type”Text Placeholder . . . . . . . . . . . . . . . . . . . . . . . . . . . .18
Asking a User for Text Input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
Creating a Dynamic AutoCorrect Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
Adding Dummy Text to a Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Using the RAND Function . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . .21
Using the Repeat Command . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . .22
Inserting a Hyperlink in a Document . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . .23
Creating a Hyperlink Using AutoCorrect . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Creating a Hyperlink from Scratch . . . . . . . . . . . . . . . . . .. .. . . . . . . . . . . . . . . .25
Pasting a Hyperlink in Word . . . . . . . . . . . . . . . . . . . . . . .. .. . . . . . . . . . . . . . . .26
Displaying Sentence Word Counts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
Finding the Longest Sentence in a Document . . . . . . . . . . . . . . . . . . . . . . . . . . . .28
Toggling Hidden Codes and Text . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . .30
Creating a Custom Spell Check Dictionary . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . .31
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Chapter 2 Formatting Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Quickly Modifying the Normal Style . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . .35
Setting Up the Quick Style Gallery to Suit the Way You Work . . . . . . . . . . . . . . . .36
Changing the Document Map Formatting . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . .38
Selecting Text with Similar Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39
Copying Formatting from One Section to Another . . . . . . . . . . . . . . . . . . . . . . . . . .40
Comparing the Formatting of Two Text Selections . . . . . . . . . . . .. . . . . . . . . . . . . .40
Removing Formatting with Precision . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . .41
Replacing One Style with Another . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Applying Formatting from the Keyboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Specifying a Custom Bullet Image . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47
Creating an Inline Numbered List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49
Converting Uppercase Words to Another Case . . . . . . . . . . . . . . . . . . . . . . . . . . .51
Converting Smart Quotes to Straight Quotes . . . . . . . . . . . . . . . . .. . . . . . . . . . . . .53
Formatting a Single Column in Tab-Separated Text . . . . . . . . . . . . . . . . . . . . . . . .55
Ensuring Consistent Document Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Wrapping Text Around an Image . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Adding a Drop Cap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . .58
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
Chapter 3 Document Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
Recovering More of Your Work with a Shorter AutoRecover Interva. . . . . . . . . . .61
Automatically Saving Your Work Frequently . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Closing a Document Without Saving . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Closing All Your Open Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Making Backups as You Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . .66
Showing More Items on the Recent Documents List . . . . . . . . . . . . . . . . . . . . . . .68
Opening the Most Recently Used Document at Startup . . . . . . . . . . . . . . . . . . . .68
Clearing the Recent Documents List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69
Creating and Opening Document Workspaces . . . . . . . . . . . . . . . . . . . . . . . . . . . .70
Automatically Prompting for Document Properties . . . . . . . . . . . . . . . . . . . . . . . . . .72
Creating a Trusted Location for Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Viewing Total Editing Time Updated in Real-Time . . . . . . . . . . . . . . . . . . . . . . . . . .75
Calculating Billable Time Charges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77
Locking Document Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77
Preventing Untracked Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .79
Setting Up a Document for Structured Editing . . . . . . . . . . . . . . . . . . . . . . . . . . .. .80
Inspecting a Document for Personal Information . . . . . . . . . . . . . . . . . . . . . . . . .. .84
Viewing Two Documents Side by Side . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. .85
Updating All Fields Automatically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .86
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .87
Chapter 4 Page Layout Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89
Displaying Text in Multiple, Linked Text Boxes . . . . . . . . . . . . . . . . . . . . . . .. . . . .89
Creating Jump Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
Creating a Custom Watermark . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . .93
Giving a Document a Parchment Paper Background . . . . . . . . . . . . . . . . . . . . . . .93
Displaying a Document with a Random Texture Background . . . . . . . . . . . . . . . . .94
Displaying Text with Line Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
Using Continuous Numbers in Two Separate Numbered Lists . . . . . . . . . . . . . . . .97
Using Multiple Page Number Formats in the Same Document . . . . . . . . . . . . . . . .98
Making the First Page Header and Footer Unique . . . . . . . . . . . . . . . . . . . . . . . .100
Using Different Odd and Even Page Headers . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
Adding a Gutter for a Bound Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
Creating Custom Outline Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .102
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .103
Part II MICROSOFT EXCEL TRICKS
Chapter 5 Formula and Function Tricks . . . . . . . . . . . . . . . . . . . . . . . . . .107
Allowing Only Certain Values in a Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Looking Up a Value in a Discount Rate Schedule . . . . . . . . . . . . . . . . . . . . . . . . .109
Looking Up a Customer Account Number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
Generating Account Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Generating Random Numbers to Test Worksheet Models . . . . . . . . . . . . . . . . . .114
Removing Excess Spaces from a Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
Removing Unprintable Characters from a Cell . . . . . . . . . . . . . . . . . . . . . . . . . . .119
Extracting a Person’s First Name or Last Name . . . . . . . . . . . . . . . . . . . . . . . . . .121
Building an Accounts Receivable Aging Worksheet . . . . . . . . . . . . . . . . . . . . . .123
Converting Mainframe Dates to Excel Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . .125
Calculating Tiered Bonuses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .126
Calculating the Number of Weekdays Between Two Dates . . . . . . . . . . . . . . . . .126
Determining the Fiscal Quarter in Which a Date Falls . . . . . . . . . . . . . . . .. . . . . .128
Setting a Product Price Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Calculating the Principal and Interest for a Loan . . . . . . . . . . . . . . . . . . . . . . . . .130
Determining How Much You Can Borrow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Calculating the Future Value of an Investment . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Deciding Whether to Buy or Lease . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .136
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
Chapter 6 Workbook and Worksheet Tricks . . . . . . . . . . . . . . . .. . . . . .139
Applying Text or Formatting to Multiple Worksheets . . . . . . . . . . . . . . . . .. . . . . .139
Annotating a Worksheet with Comments . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .140
Removing All Comments from a Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
Keeping Track of Worksheet Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Sharing a Workbook with Other Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Preventing Users from Changing Parts of a Worksheet . . . . . . . . . . . . . . . . . . .148
Adding a Live Stock Price Quote to a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . .149
Automatically Sorting a Range After Data Entry . . . . . . . . . . . . . . . . . . . . . . .. . . .151
Transposing Range Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .152
Automatically Opening a Workbook at Startup . . . . . . . . . . . . . . . . . . . . . . . . . . .152
Creating a Workspace of Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .153
Creating a Workbook with a Specified Number of Sheets . . . . . . . . . . . . . . . . . . .154
Selecting A1 on All Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . .155
Selecting the “Home Cell” on All Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . .156
Selecting the Named Range that Contains the Active Cell . . . . . . . . . . . . . . . . . .157
Saving All Open Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159
Using Dialog Box Controls to Input Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .160
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .163
Chapter 7 Data Analysis Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .165
Using a Range Snapshot to Watch a Cell Value . . . . . . . . . . . . . . . . . . . . . . . . . .165
Using a Watch Window to Watch a Cell Value . . . . . . . . . . . . . . . . . . . . . . . . . . .166
Calculating Multiple Solutions to a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .167
Plugging Multiple Input Values into a Formula . . . . . . . . . . . . . . . . . . . . . . . . . . . .171
Determining the Break-Even Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .174
Optimizing Profit and Margin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .177
Highlighting Cells Above or Below a Certain Value . . . . . . . . . . . . . . . . . . . . . . . .182
Highlighting Values Below the Median . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .184
Analyzing Cell Values with Data Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .186
Applying Percentile-Based Icon Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
Chapter 8 Chart Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
Using Worksheet Text in a Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
Plotting the Average on the Value Axis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .198
Stacking a Picture as a Bar Chart Data Marker . . . . . . . . . . . . . . . . . . . . . . . . . . .201
Charting a Dynamic Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .202
Automatically Expanding a Chart to Include New Data . . . . . . . . . . . . . . . . . . . . . .206
Displaying a Second Vertical Axis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .206
Exploding a Slice from a Pie Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .207
Charting Small Values with a Bar of Pie Chart . . . . . . . . . . . . . . . . . .. . . . . . . . . . .208
Creating a Scrolling Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .209
Plotting a Best-Fit Trendline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . .213
Plotting Forecasted Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .217
III MICROSOFT POWERPOINT TRICKS
Chapter 9 Slide and Presentation Tricks . . . . . . . . . . . . . . . . . . . . . . . . . .221
Converting a Word Outline into a PowerPoint Presentation . . . . . . . .. . . . . . . . . . .222
Inserting Custom Text into the Slide Footer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .224
Creating Custom Bullets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .226
Replacing Fonts Throughout a Presentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
Recoloring a Picture to Match Your Presentation . . . . . . . . . . . . . . . . . . . . . . . . .229
Drawing Shapes at Evenly Spaced Intervals . . . . . . . . . . . . . . . . . . . . . . . . . . . .230
Using Drawing Guides to Precisely Align Objects . . . . . . . . . . . . . . . . . . . . . . . . .231
Creating a Custom Slide Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
Hiding a Slide Master Object on One Slide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .235
Using Multiple Slide Masters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
Creating a Custom Blank Presentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .237
Maintaining a Slide Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .239
Converting a Slide into an Image . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .242
Creating a Summary Slide with a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244
Using Word to Custom Format Handouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .246
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .247
Chapter 10 Animation Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .249
Applying a Custom Sound to a Transition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .250
Creating a Custom Animation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251
Using Triggers to Display and Hide a Slide Object . . . . . . . . . . . . . . . . . . . . . . . . .255
Making Bullets Display Individually . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .257
Animating Chart Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .260
Animating an Organization Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .261
Uncovering Parts of an Image, One at a Time . . . . . . . . . . . . . . . . . . . . . . . . . . . .262
Animating Separate Elements of a Clip Art Image . . . . . . . . . . . . . . . . . . . . . . .. . .263
Playing a Video to Introduce a Slide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .268
Emphasizing the Current Bullet Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .269
Adding Scrolling Credits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .272
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .273
Chapter 11 Slide Show Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .275
Rehearsing a Slide Show . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .275
Assigning Slide Times with a Macro . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . .278
Calculating the Average Slide Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
Adding Narration to a Slide Show . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . .281
Setting Up an Automatic Slide Show . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .282
Playing CD Tracks During a Slide Show . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
Creating a Custom Slide Show . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .285
Presenting a Slide Show Using Two Monitors . . . . . . . . . . . . . . . . . . . . . . . . . .286
Controlling a Slide Show from the Keyboard . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
Linking to a Hidden Slide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .290
Linking to the Last Slide Viewed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .292
Protecting a Presentation by Distributing It as a Slide Show File . . . . . . . . . . . . .292
Publishing a Slide Show to the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .294
Enabling Animations on the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .296
Copying the Presentation to a CD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .297
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .298
IV MICROSOFT OUTLOOK TRICKS
Chapter 12 Email Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .301
Customizing the Inbox Message Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .302
Setting the Junk E-mail Protection Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .305
Blocking Messages from a Particular Person or Domain . . . . . . . . . . . . . . . . . . .308
Blocking Foreign or Non-English Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . .308
Avoiding Viruses by Reading Mail in Plain Text . . . . . . . . . . . . . . . . . . . . . .. . . . .309
Allowing a Blocked File Type as an Attachment . . . . . . . . . . . . . . . . . . . . . .. . . . .311
Setting a Message Follow-Up Reminder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .315
Supplementing a Reminder with an Email Message . . . . . . . . . . . . . . . . . . . . . . .316
Creating Custom Color Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .318
Applying Colors to Messages from Specific Senders . . . . . . . . . . . . . . . . . . . . . .320
Downloading Mail for a Single Account Using Multiple Computers . . . . . . . . . . .321
Creating an Email Shortcut for a Recipient . . . . . . . . . . . . . . . . . . . . . . . . . . . . .322
Having Replies Sent to a Different Address . . . . . . . . . . . . . . . . . . . . . . . . . . . . .324
Using a Different Outgoing (SMTP) Mail Port . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Prompting to Save Messages in the Sent Items Folder . . . . . . . . . . . . . . . . . . . .327
Setting Up a Password-Protected Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .328
Cutting Your Mailbox Down to Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .331
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . .332
Chapter 13 Calendar and Contacts Tricks . . . . . . . . . . . . . . . . . . . . . .333
Starting Outlook in the Calendar Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .334
Changing the Number of Days That Calendar Displays . . . . . . . . . . . . . . . . . . .334
Customizing the Day View’s Time Scale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .337
Adding Another Time Zone to the Day and Week Views . . . . . . . . . . . . . . . . . . .337
Customizing Calendar’s Holidays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .339
Printing a Blank Calendar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .341
Using Natural-Language Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . .343
Customizing the To-Do Bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344
Color-Coding Appointments Automatically . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .346
Editing Multiple Contacts at Once . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347
Calling a Contact . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
Adding a Contact Picture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351
Displaying a Map for a Contact’s Address . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .352
Printing a Phone Directory of Your Contacts . . . . . . . . . . . . . . . . . . . . . . . . . . . .353
Sharing a Folder with Other Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .354
Sharing a Folder via Email . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .354
Sharing a Folder via Microsoft Exchange . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .356
Sharing a Folder with Delegate Access via Microsoft Exchange . . . . . . . . .. . . .358
Viewing Another User’s Shared Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . .359
Working with Another Email Account as a Delegate . . . . . . . . . . . . . . . . . . . . . .360
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .361
V MICROSOFT ACCESS TRICKS
14 Table and Query Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365
Validating Data Entry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .365
Using an Input Mask to Ensure Consistent Data Entry . . . . . . . . . . . . . . . . . . . .369
Setting a Default Value for a Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . .373
Requiring a Value for a Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .374
Ensuring That No Duplicate Values Display in a Field . . . . . . . . . . . . . . . . . . . . .374
Selecting a Field Value from a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .376
Entering a Field at the End for Easier Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . .378
Inserting Hyperlinks into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .379
Importing Data from an Outlook Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .380
Collecting Access Data via E-mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .384
Creating the Access Data Collection Message . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Working with Access Data Collection Replies . . . . . . . . . . . . . . . . . . . . . . . . . . .386
Finding Duplicate Records with a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .387
Selecting the Top Values with a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .388
Adding a Custom Calculated Column to a Query . . . . . . . . . . . . . . . . . . . . . . . . .388
Calculating Inventory Value . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .391
Calculating Order Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .392
Prompting for Query Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .394
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .397
Chapter 15 Form and Report Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .399
Opening a Form Automatically When You Load a Database . . . . . . . . . . . . . . . . .399
Automatically Moving to the Last Record When You Open a Form . . . . . . . . . . . .400
Creating a Switchboard Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .402
Using a Toggle Button for a Yes/No Field . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .403
Preventing Changes to the Form’s Data Source . . . . . . . . . . . . . . . . . . . . . . . . . .405
Customizing the Form’s Tab Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .405
Creating Shortcut Keys for Form Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406
Applying Formatting to Special Form Values . . . . . . . . . . . . . . . . . . . . . . .. . . . . . .408
Creating Calculated Form Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . .409
Creating Custom Report Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .411
Inserting Page Numbers in the Report . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .414
Inserting the Current Date in a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .415
Adding Page Breaks after Report Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .416
Preventing Widowed Report Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .417
Starting Report Sections at the Top of a Row or Column . . . . . . . . . . . . . . . . . . .417
Creating a Multiple-Column Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .418
Creating Mailing Labels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421
Distributing a Report Snapshot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .424
From Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .425
APPENDIXES
Appendix A Working with VBA Macros . . . . . . . . . . . . . . . . . . . . . . . . . .429
Activating the Ribbon’s Developer Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .430
Running a VBA Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .430
Using the Example Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .431
Using the Macro Name List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .431
Assigning Shortcut Keys to Word Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . .431
Assigning Shortcut Keys to Excel Macros . . . . . . . . . . . . . . . . . . . . . . . .. . . .433
Creating a Quick Access Toolbar Button for a Macro . . . . . . . . . . . . . . .. . . .434
Using a Function Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .435
Recording a VBA Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .436
Recording a Word Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . .437
Recording an Excel Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . .438
Working with the Visual Basic Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440
Creating a Module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .441
Opening a Module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .441
Running a Command Macro from the Visual Basic Editor . . . . . . . . . . . . . . .442
Appendix B Office 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . .443
General Office 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .444
Word 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .445
Excel 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . .451
PowerPoint 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .455
Outlook 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .457
Access 2007 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . .461