Home > Store

Tricks of the Microsoft Office 2007 Gurus

Add To My Wish List

Tricks of the Microsoft Office 2007 Gurus

By Paul McFedries
Published May 16, 2007 by Que.

Book

Sorry, this book is no longer in print.

Not for Sale

Description

Downloads

Sample Content

Updates

More Information

Description

Copyright 2007
Dimensions: 7" x 9-1/8"
Pages: 504
Edition: 1st

Book
ISBN-10: 0-7897-3666-7
ISBN-13: 978-0-7897-3666-6

&>Paul McFedries, an Office "insider," that will help you learn the Office applications inside and out. Designed to bridge the gap between your expectation and reality, you will go beyond the basics and learn newly discovered techniques, shortcuts, and best practices. Through real-world examples that showcase how the tips and tricks can be used in everyday business tasks, Tricks of the Microsoft Office 2007 Gurus will help you get the most out of Office 2007.

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).



Downloads

Example(s)

Visit the author's web site to access example files.



Sample Content

Online Sample Chapter

Tricks for Working with Word 2007 Documents

Downloadable Sample Chapter

Download the sample chapter

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

Index

Download the Index

Introduction

Download the Introduction



Updates

Submit Errata



More Information



InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address