Tricks of the Microsoft Office 2007 Gurus

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