Tricks of the Microsoft Office Gurus

Table of Contents

Part 1 — Office 2003 Application Tricks

Chapter 1—Building Dynamic Documents in Word

AutoCorrect Tricks

Reversing AutoCorrect

Moving AutoCorrect Entries to Another Computer

Entering Boilerplate Text Automatically

Using AutoCorrect to Enter Boilerplate Text

Using AutoText to Enter Boilerplate Text

Creating a Customizable AutoCorrect Entry

Using AutoCorrect to Insert Your Signature

Creating Border Lines On-the-Fly

Creating Table Cells On-the-Fly

Creating Custom Bulleted Lists On-the-Fly

Using Custom Document Properties

Creating a Custom Document Property

Searching Via Document Properties

Using Fields to Insert Dynamic Data

Inserting a Field

Using the Field Dialog Box

Understanding Field Code Syntax

Inserting a Field Manually

Viewing and Navigating Fields

Toggling Field Codes

Navigating a Document's Fields

Updating a Field

Updating All Fields with a Macro

Updating Fields When Opening a Document

Updating Fields By Field Type

Updating Fields By Name

Preventing Updates By Locking a Field

Converting a Field Result to Text

Keyboard Shortcuts for Fields

Putting Fields to Good Use

Viewing Total Editing Time Updated in Real-Time

Running a Macro

Creating "Click-and-Type" Text Placeholders

Creating a Shortcut Menu of AutoText Entries

Building a Formula Field

Calculating Billable Time Charges

Creating Decision-Making Fields

Prompting the User for Input

Performing Calculations in Tables

Referencing Table Cells

Solving a Relative Reference Problem

Adding Dummy Text to a Document

Using the RAND Function

Using the Repeat Command

Chapter 2—Analyzing Data with Excel

Performing a What-If Analysis

Performing What-If Analysis with a Range Snapshot

Setting Up a One-Input Data Table

Setting Up a Two-Input Table

Editing a Data Table

Working with Scenarios

Setting Up Your Worksheet for Scenarios

Adding a Scenario

Displaying a Scenario

Using Goal Seek for What-If Analysis

Running Goal Seek

Goal Seeking with Charts

Solving Complex Problems with Solver

Loading Solver

Using Solver

Adding Constraints

Analyzing Data with Lists

Converting a Range to a List

Basic List Operations

Sorting a List

Sorting on More Than Three Keys

Sorting a List in Natural Order

Sorting on Part of a Field

Sorting Without Articles

Filtering List Data

Using AutoFilter to Filter a List

AutoFilter Criteria Options

Setting Up Custom AutoFilter Criteria

Showing Filtered Records

Using Complex Criteria to Filter a List

Setting Up a Criteria Range

Filtering a List with a Criteria Range

Entering Compound Criteria

Entering Computed Criteria

Summarizing List Data

Creating Automatic Subtotals

Setting Up a List for Automatic Subtotals

Displaying Subtotals

Adding More Subtotals

Removing Subtotals

Excel's List Functions

About List Functions

List Functions That Don't Require a Criteria Range

Using COUNTIF()

Using SUMIF()

List Functions That Require a Criteria Range

Using DAVERAGE()

Using DGET()

Chapter 3—Constructing Knockout Presentations in PowerPoint

Organizing Your Presentation

Organization: Telling a Story

Organizing Your Presentation with an Outline

Viewing the Outline Pane and Outlining Toolbar

Creating a Presentation Outline

Creating the Top Level

Creating the Second Level

Creating Lower Levels

Creating an Outline From a Text File

Controlling the Display of Outline Levels

Editing the Presentation Outline

Organizing with Custom Slide Footers

Displaying the Footer

Adding Custom Footer Text with a Script

Customizing the Footer Layout

Advanced Slide Formatting and Design

Slide Design Guidelines

Using the Slide Master to Get a Consistent Look

Viewing and Editing the Slide Master

Using Multiple Slide Masters

Ensuring Good and Consistent Design

Creating a Custom Color Scheme

Replacing Fonts

Changing a Picture's Colors

Some AutoShape Tricks

Drawing Circles and Squares

Drawing Shapes Quickly

Setting the Default Formatting For an AutoShape

Copying Object Formatting

Duplicating Shapes at Evenly Spaced Intervals

Setting the Default Font for Shape Text

Wrapping Text Within a Shape

Hiding Slide Master Shapes in a Slide

Advanced PowerPoint Animation Techniques

Animation Dos and Don'ts

Applying Built-In Animation Effects

Applying a Slide Transition

Applying an Animation Scheme

Creating a Custom Animation

Making Bullets Appear One at a Time

Animating a Chart by Series or Category

Animating Individual Chart Components

Animating an Organization Chart

Taking PowerPoint to the Next Level with Microsoft Producer

Chapter 4—Taming Access Data

Creating a Totals Query

Displaying the Total Row in the Design Grid

Setting Up a Totals Query On a Single Field

Setting Up a Totals Query On Multiple Fields

Filtering the Records Before Calculating Totals

Creating a Totals Query for Groups of Records

Grouping on Multiple Fields

Creating a Totals Query Using a Calculated Field

Creating a Totals Query Using Aggregate Functions

Combining Aggregate Functions and Totals

Calculating Units Left In Stock

Creating Queries that Make Decisions

Making Decisions with the IIf Function

Determining Whether Stock Needs to Be Reordered

Making Decisions with the Switch Function

Running Action Queries

Modifying Table Data with an Update Query

Removing Records from a Table with a Delete Query

Creating New Tables with Make-Table Queries

Adding Records to a Table with an Append Query

Preventing Form Errors by Validating Data

Helping Users with Text Prompts

Preventing Errors with Data Validation Expressions

Using Input Masks for Consistent and Accurate Data Entry

Using the Input Mask Wizard

Creating a Custom Input Mask Expression

Using Form Controls to Limit Data-Entry Choices

Working with Yes/No Fields

Using Check Boxes

Using Toggle Buttons

Using Option Buttons to Present a Limited Number of Choices

Running the Option Group Wizard

Creating an Option Group By Hand

Using Lists to Present a Large Number of Choices

Starting the List Box or Combo Box Wizard

Getting List Values from a Table or Query Field

Specifying Custom List Values

Getting List Values from the Current Table

Creating a Multiple-Column List

Using Text Boxes as Calculated Form Controls

Creating a Multiple-Column Report

Setting Up the Report

Tweaking the Page Setup

Troubleshooting Multiple Columns

Adding Calculations to a Report

Controlling Report Output

Adding Page Breaks After Sections

Starting Sections at the Top of a Row or Column

Avoiding Widowed Records

Chapter 5—Getting the Most Out of Outlook

Getting the Most Out of E-Mail

Customizing the Inbox Message Fields

Changing the Folder View

Sorting the Messages

Grouping the Messages

Defining a New Grouping

Easier Groupings with the Group By Box

Filtering the Messages

Defining a Custom View

Incoming Message Tricks

Using Rules to Process Messages Automatically

Creating a Rule from Scratch

Creating a Rule from a Message

Applying Colors to Messages from Specific Senders

Setting a Message Follow-Up Reminder

Cutting Your Mailbox Down to Size

Checking the Same Account from Two Different Computers

Outgoing Message Tricks

Creating an E-Mail Shortcut for a Recipient

Having Replies Sent to a Different Address

Using a Different SMTP Port

Getting the Most Out of the Calendar

Starting Outlook in the Calendar Folder

Using the Calendar Folder

Using the Date Navigator

Changing the Number of Days Displayed

Working with Calendar's Views

Other Navigation Techniques

Displaying a Second Time Zone

Adding Custom Holidays to Your Calendar

Printing a Blank Calendar

Taking Advantage of AutoDate

Color-Coding Appointments

Getting the Most Out of Contacts

Working with the Contacts Folder's Views

Editing Data for Multiple Contacts

Phoning a Contact

Quick Connections with Speed Dial

Adding a Picture for a Contact

Displaying Contact Activity

Part 2 — Office 2003 Sharing and Collaboration Tricks

Chapter 6—Office in Overdrive: Sharing Data Between Applications

Using the Office Clipboard

Setting Office Clipboard Options

Pasting Data in a Different Format

Using the Clipboard Contents in a Replace Operation

Inserting an Object from Another Application

Understanding Compound Documents

Understanding Linking

Understanding Embedding

Should You Link or Embed?

Linking an Object

Linking via the Clipboard

Inserting a File as a Linked Object

Managing Links

Embedding an Object

Embedding via the Clipboard

Inserting a New Embedded Object

Inserting an Embedded File

Editing a Linked or Embedded Object

More Office Tools for Sharing Data

Converting a Word Outline into a PowerPoint Presentation

Using Word to Custom Format PowerPoint Handouts

Importing Excel Data into Access

Exporting a Word Table to Access

Analyzing Access Data in Excel

Transferring Records Via the Clipboard

Using Analyze It with Microsoft Office Excel

Using Excel's Get External Data Feature

Publishing Access Data in Word

Merging Data from Access, Excel, and Outlook

Step 1: Opening the Main Document

Step 2: Selecting the Mail Merge Document Type

Step 3: Opening the Data Source

Selecting the Outlook Contacts Folder as the Data Source

Defining a New Data Source

Step 4: Selecting, Sorting, and Filtering the Recipients

Selecting Recipients

Filtering Recipients

Sorting Recipients

Step 5: Adding Text and Merge Fields

Inserting an Address Block

Matching Fields

Creating Letters and Envelopes at the Same Time

Inserting a Greeting Line

Inserting Data Source Fields

Inserting Word Fields

Personalizing Mail Merges with Fill-in Fields

Intelligent Merging I: The If Field

Step 6: Previewing the Results

Step 7: Completing the Mail Merge

Chapter 7—Working as a Team: Collaborating with Other Users

Collaborating on a Word Document

Inserting Comments

Tracking Word Document Changes

Working with Comments and Changes

Viewing Comments and Changes

Navigating Comments and Changes

Accepting or Rejecting Comments and Changes

Customizing Markup

Working with Document Versions

Creating a Master Document and Subdocuments

Creating a Master Document and Subdocuments from an Outline

Creating Subdocuments from Existing Documents

Working with Subdocuments

Embedding Fonts in Shared Documents

Sharing Excel Workbooks

Inserting Comments in Cells

Tracking Worksheet Changes

Sharing a Workbook

Updating a Shared Workbook

Working with Reviewers

Handling Conflicts

Collaborating Via Outlook

Sharing Office Documents Via E-Mail

Sending a Document as an E-mail Message

Sending a Document as an Attachment

Sending a Document with a Review Request

Routing Documents

Preparing Documents for Review

Sharing Your Outlook Folders

Sharing Your Folders with Permissions

Sharing Your Folders with Delegate Access

Accessing Shared Folders

Working with Another E-Mail Account as a Delegate

Requesting a Meeting

Sending Out a New Meeting Request

Planning a Meeting

Using SharePoint to Collaborate on Office Documents

Sharing Documents in a Document Library

Creating a New Document Library

Uploading an Existing Document

Creating a New Document

Opening a Document