Tricks of the Microsoft® Office Gurus

Tricks of the Microsoft® Office Gurus

by
Released April 2005
Publisher(s): Que
ISBN: 9780789733696

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

We all have those "go-to" people in our lives. The one you go-to for advice, the one you go-to for comfort, the one you go-to with technical questions...now you can be the one that others go-to for help with Microsoft Office 2003. Tricks of the Microsoft Office Gurus is full of exclusive tricks from 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, including:

  • Using Excel's "Camera Tool" for what-if analysis

  • Embedding fonts in shared documents

  • Adding a live stock price quote to a worksheet

  • Using dummy text to layout and format a document

  • Publishing Outlook Calendar to the web

  • Using Word to customize Power Point handouts

    • Through real-world examples that showcase how the tips and tricks can be used in everyday business tasks, Tricks of the Microsoft Office Gurus will help you get the most out of Office 2003.

    Table of contents

    1. Copyright
      1. Dedication
    2. About the Author
    3. Acknowledgments
    4. We Want to Hear from You!
    5. Introduction
      1. What’s in This Book
      2. This Book’s Special Features
    6. I. Office 2003 Application Tricks
      1. 1. Building Dynamic Documents in Word
        1. AutoCorrect Tricks
          1. Reversing AutoCorrect
          2. Moving AutoCorrect Entries to Another Computer
          3. Entering Boilerplate Text Automatically
            1. Using AutoCorrect to Enter Boilerplate Text
            2. Using AutoText to Enter Boilerplate Text
          4. Creating a Customizable AutoCorrect Entry
          5. Using AutoCorrect to Insert Your Signature
          6. Creating Border Lines On-the-Fly
          7. Creating Table Cells On-the-Fly
          8. Creating Custom Bulleted Lists On-the-Fly
        2. Using Custom Document Properties
          1. Creating a Custom Document Property
          2. Searching via Document Properties
        3. Using Fields to Insert Dynamic Data
          1. Inserting a Field
            1. Using the Field Dialog Box
            2. Understanding Field Code Syntax
            3. Inserting a Field Manually
          2. Viewing and Navigating Fields
            1. Toggling Field Codes
            2. Navigating a Document’s Fields
          3. Updating a Field
            1. Updating All Fields with a Macro
            2. Updating Fields When Opening a Document
            3. Updating Fields by Field Type
            4. Updating Fields by Name
            5. Preventing Updates by Locking a Field
            6. Converting a Field Result to Text
          4. Keyboard Shortcuts for Fields
          5. Putting Fields to Good Use
            1. Viewing Total Editing Time Updated in Real-Time
            2. Running a Macro
            3. Creating “Click-and-Type” Text Placeholders
            4. Creating a Shortcut Menu of AutoText Entries
            5. Building a Formula Field
            6. Calculating Billable Time Charges
            7. Creating Decision-Making Fields
            8. Prompting the User for Input
        4. Performing Calculations in Tables
          1. Referencing Table Cells
          2. Solving a Relative Reference Problem
        5. Adding Dummy Text to a Document
          1. Using the RAND Function
          2. Using the Repeat Command
          3. From Here
      2. 2. Analyzing Data with Excel
        1. Performing a What-If Analysis
          1. Performing What-If Analysis with a Range Snapshot
          2. Setting Up a One-Input Data Table
          3. Setting Up a Two-Input Table
          4. Editing a Data Table
        2. Working with Scenarios
          1. Setting Up Your Worksheet for Scenarios
          2. Adding a Scenario
          3. Displaying a Scenario
        3. Using Goal Seek for What-If Analysis
          1. Running Goal Seek
          2. Goal Seeking with Charts
        4. Solving Complex Problems with Solver
          1. Loading Solver
          2. Using Solver
          3. Adding Constraints
        5. Analyzing Data with Lists
          1. Converting a Range to a List
          2. Basic List Operations
          3. Sorting a List
            1. Sorting on More Than Three Keys
            2. Sorting a List in Natural Order
            3. Sorting on Part of a Field
          4. Sorting Without Articles
          5. Filtering List Data
            1. Using AutoFilter to Filter a List
            2. AutoFilter Criteria Options
            3. Setting Up Custom AutoFilter Criteria
            4. Showing Filtered Records
            5. Using Complex Criteria to Filter a List
              1. Setting Up a Criteria Range
              2. Filtering a List with a Criteria Range
              3. Entering Compound Criteria
            6. Entering Computed Criteria
          6. Summarizing List Data
          7. Creating Automatic Subtotals
            1. Setting Up a List for Automatic Subtotals
            2. Displaying Subtotals
            3. Adding More Subtotals
            4. Removing Subtotals
          8. Excel’s List Functions
            1. About List Functions
            2. List Functions That Don’t Require a Criteria Range
              1. Using COUNTIF()
              2. Using SUMIF()
            3. List Functions That Require a Criteria Range
              1. Using DAVERAGE()
              2. Using DGET()
          9. From Here
      3. 3. Constructing Knockout Presentations in PowerPoint
        1. Organizing Your Presentation
          1. Organization: Telling a Story
          2. Organizing Your Presentation with an Outline
            1. Viewing the Outline Pane and Outlining Toolbar
            2. Creating a Presentation Outline
              1. Creating the Top Level
              2. Creating the Second Level
              3. Creating Lower Levels
            3. Creating an Outline from a Text File
            4. Controlling the Display of Outline Levels
            5. Editing the Presentation Outline
          3. Organizing with Custom Slide Footers
            1. Displaying the Footer
            2. Adding Custom Footer Text with a Script
            3. Customizing the Footer Layout
        2. Advanced Slide Formatting and Design
          1. Slide Design Guidelines
          2. Using the Slide Master to Get a Consistent Look
            1. Viewing and Editing the Slide Master
            2. Using Multiple Slide Masters
          3. Ensuring Good and Consistent Design
          4. Creating a Custom Color Scheme
          5. Replacing Fonts
          6. Changing a Picture’s Colors
          7. Some AutoShape Tricks
            1. Drawing Circles and Squares
            2. Drawing Shapes Quickly
            3. Setting the Default Formatting for an AutoShape
            4. Copying Object Formatting
            5. Duplicating Shapes at Evenly Spaced Intervals
            6. Setting the Default Font for Shape Text
            7. Wrapping Text Within a Shape
            8. Hiding Slide Master Shapes in a Slide
        3. Advanced PowerPoint Animation Techniques
          1. Animation Do’s and Don’ts
          2. Applying Built-In Animation Effects
            1. Applying a Slide Transition
            2. Applying an Animation Scheme
          3. Creating a Custom Animation
            1. Making Bullets Appear One at a Time
            2. Animating a Chart by Series or Category
            3. Animating Individual Chart Components
            4. Animating an Organization Chart
        4. Taking PowerPoint to the Next Level with Microsoft Producer
          1. From Here
      4. 4. Taming Access Data
        1. Creating a Totals Query
          1. Displaying the Total Row in the Design Grid
          2. Setting Up a Totals Query on a Single Field
          3. Setting Up a Totals Query on Multiple Fields
          4. Filtering the Records Before Calculating Totals
          5. Creating a Totals Query for Groups of Records
          6. Grouping on Multiple Fields
          7. Creating a Totals Query Using a Calculated Field
          8. Creating a Totals Query Using Aggregate Functions
          9. Combining Aggregate Functions and Totals
            1. Calculating Units Left In Stock
        2. Creating Queries That Make Decisions
          1. Making Decisions with the IIf Function
            1. Determining Whether Stock Needs to Be Reordered
          2. Making Decisions with the Switch Function
        3. Running Action Queries
          1. Modifying Table Data with an Update Query
          2. Removing Records from a Table with a Delete Query
          3. Creating New Tables with Make-Table Queries
          4. Adding Records to a Table with an Append Query
        4. Preventing Form Errors by Validating Data
          1. Helping Users with Text Prompts
          2. Preventing Errors with Data Validation Expressions
          3. Using Input Masks for Consistent and Accurate Data Entry
            1. Using the Input Mask Wizard
            2. Creating a Custom Input Mask Expression
        5. Using Form Controls to Limit Data-Entry Choices
          1. Working with Yes/No Fields
            1. Using Check Boxes
            2. Using Toggle Buttons
          2. Using Option Buttons to Present a Limited Number of Choices
            1. Running the Option Group Wizard
            2. Creating an Option Group by Hand
          3. Using Lists to Present a Large Number of Choices
            1. Starting the List Box or Combo Box Wizard
            2. Getting List Values from a Table or Query Field
            3. Specifying Custom List Values
            4. Getting List Values from the Current Table
            5. Creating a Multiple-Column List
          4. Using Text Boxes as Calculated Form Controls
        6. Creating a Multiple-Column Report
          1. Setting Up the Report
          2. Tweaking the Page Setup
          3. Troubleshooting Multiple Columns
        7. Adding Calculations to a Report
        8. Creating a Shadow Effect for Report Text
        9. Controlling Report Output
          1. Adding Page Breaks After Sections
          2. Starting Sections at the Top of a Row or Column
          3. Avoiding Widowed Records
          4. From Here
      5. 5. Getting the Most Out of Outlook
        1. Getting the Most Out of Email
          1. Customizing the Inbox Message Fields
          2. Changing the Folder View
          3. Sorting the Messages
          4. Grouping the Messages
            1. Defining a New Grouping
            2. Easier Groupings with the Group By Box
          5. Filtering the Messages
          6. Defining a Custom View
          7. Incoming Message Tricks
            1. Using Rules to Process Messages Automatically
              1. Creating a Rule from Scratch
              2. Creating a Rule from a Message
            2. Applying Colors to Messages from Specific Senders
            3. Setting a Message Follow-Up Reminder
            4. Cutting Your Mailbox Down to Size
            5. Checking the Same Account from Different Computers
          8. Outgoing Message Tricks
            1. Creating an Email Shortcut for a Recipient
            2. Having Replies Sent to a Different Address
            3. Using a Different SMTP Port
        2. Getting the Most Out of the Calendar
          1. Starting Outlook in the Calendar Folder
          2. Using the Calendar Folder
            1. Using the Date Navigator
            2. Changing the Number of Days Displayed
            3. Working with Calendar’s Views
            4. Other Navigation Techniques
          3. Displaying a Second Time Zone
          4. Adding Custom Holidays to Your Calendar
          5. Printing a Blank Calendar
          6. Taking Advantage of AutoDate
          7. Color-Coding Appointments
        3. Getting the Most Out of Contacts
          1. Working with the Contacts Folder’s Views
          2. Editing Data for Multiple Contacts
          3. Phoning a Contact
          4. Quick Connections with Speed Dial
          5. Adding a Picture for a Contact
          6. Displaying Contact Activity
          7. From Here
    7. II. Office 2003 Sharing and Collaboration Tricks
      1. 6. Office in Overdrive: Sharing Data Between Applications
        1. Using the Office Clipboard
          1. Setting Office Clipboard Options
          2. Pasting Data in a Different Format
          3. Using the Clipboard Contents in a Replace Operation
        2. Inserting an Object from Another Application
          1. Understanding Compound Documents
            1. Understanding Linking
            2. Understanding Embedding
            3. Should You Link or Embed?
          2. Linking an Object
            1. Linking via the Clipboard
            2. Inserting a File as a Linked Object
            3. Managing Links
          3. Embedding an Object
            1. Embedding via the Clipboard
            2. Inserting a New Embedded Object
            3. Inserting an Embedded File
          4. Editing a Linked or Embedded Object
        3. More Office Tools for Sharing Data
          1. Converting a Word Outline into a PowerPoint Presentation
          2. Using Word to Custom Format PowerPoint Handouts
          3. Importing Excel Data into Access
          4. Exporting a Word Table to Access
          5. Analyzing Access Data in Excel
            1. Transferring Records via the Clipboard
            2. Using Analyze It with Microsoft Office Excel
            3. Using Excel’s Get External Data Feature
          6. Publishing Access Data in Word
        4. Merging Data from Access, Excel, and Outlook
          1. Step 1: Opening the Main Document
          2. Step 2: Selecting the Mail-Merge Document Type
          3. Step 3: Opening the Data Source
            1. Selecting the Outlook Contacts Folder as the Data Source
            2. Defining a New Data Source
          4. Step 4: Selecting, Sorting, and Filtering the Recipients
            1. Selecting Recipients
            2. Filtering Recipients
            3. Sorting Recipients
          5. Step 5: Adding Text and Merge Fields
            1. Inserting an Address Block
            2. Matching Fields
            3. Creating Letters and Envelopes at the Same Time
            4. Inserting a Greeting Line
            5. Inserting Data Source Fields
            6. Inserting Word Fields
            7. Personalizing Mail Merges with Fill-In Fields
            8. Intelligent Merging I: The If Field
          6. Step 6: Previewing the Results
          7. Step 7: Completing the Mail Merge
          8. From Here
      2. 7. Working as a Team: Collaborating with Other Users
        1. Collaborating on a Word Document
          1. Inserting Comments
          2. Tracking Word Document Changes
          3. Working with Comments and Changes
            1. Viewing Comments and Changes
            2. Navigating Comments and Changes
            3. Accepting or Rejecting Comments and Changes
            4. Customizing Markup
          4. Working with Document Versions
          5. Creating a Master Document and Subdocuments
            1. Creating a Master Document and Subdocuments from an Outline
            2. Creating Subdocuments from Existing Documents
            3. Working with Subdocuments
          6. Embedding Fonts in Shared Documents
        2. Sharing Excel Workbooks
          1. Inserting Comments in Cells
          2. Tracking Worksheet Changes
          3. Sharing a Workbook
            1. Updating a Shared Workbook
            2. Working with Reviewers
            3. Handling Conflicts
        3. Collaborating via Outlook
          1. Sharing Office Documents via Email
            1. Sending a Document as an Email Message
            2. Sending a Document as an Attachment
            3. Sending a Document with a Review Request
          2. Routing Documents
          3. Preparing Documents for Review
          4. Sharing Your Outlook Folders
            1. Sharing Your Folders with Permissions
            2. Sharing Your Folders with Delegate Access
            3. Accessing Shared Folders
            4. Working with Another Email Account as a Delegate
          5. Requesting a Meeting
            1. Sending Out a New Meeting Request
            2. Planning a Meeting
        4. Using SharePoint to Collaborate on Office Documents
          1. Sharing Documents in a Document Library
            1. Creating a New Document Library
            2. Uploading an Existing Document
            3. Creating a New Document
            4. Opening a Document
            5. Other Document Actions
          2. Collaborating with a Shared Workspace
          3. Sending a Shared Attachment
          4. From Here
      3. 8. Office Without Borders: Using Office Documents on the Web
        1. Converting Office Documents to Web Pages
          1. Converting a Word Document to a Web Page
          2. Publishing an Excel Range, Sheet, or Workbook to the Web
          3. Publishing a PowerPoint Presentation to the Web
          4. Publishing an Outlook Calendar to the Web
        2. Displaying Web Pages in Excel
          1. Opening a Web Page in Excel
          2. Excel’s HTML Extension: The formula Attribute
          3. Adding a Live Stock Price Quote to a Worksheet
        3. Office and FTP
        4. Inserting Hyperlinks into Office Documents
          1. Hyperlinks and Word
            1. Creating a Hyperlink Using AutoCorrect
            2. Creating a Hyperlink from Scratch
            3. Pasting a Hyperlink in Word
          2. Hyperlinks and Excel
          3. Hyperlinks and Access
          4. Hyperlinks and PowerPoint
          5. From Here
      4. 9. Collaborating with a Tablet PC and OneNote
        1. Office and Windows XP Tablet PC Edition
          1. Understanding Ink Integration
          2. Entering Text with the Tablet PC Input Panel
            1. Using the Writing Pad
            2. Using the Character Pad
            3. Using the Onscreen Keyboard
        2. Using the Office Ink Tools
          1. Adding Ink to a Document
          2. Editing Ink
          3. Converting Ink to Text
          4. Inking an Email Message
        3. Collaborating with Ink
          1. Adding Ink Annotations
          2. Adding Ink Comments in Word
          3. Working with Ink Annotations and Comments
        4. Collaborating with OneNote 2003
          1. Collaborating with OneNote and Outlook
            1. Sending Email via OneNote
            2. Creating Outlook Items
            3. Inserting the Details of an Outlook Meeting
          2. Sharing a Note-Taking Session
            1. Starting a Shared Session
            2. Joining a Shared Session
            3. Working in a Shared Session
          3. Sharing Notes with Other People
            1. Moving Notes to a Shared Network Folder
            2. Publishing to a Shared Network Folder
          4. From Here
    8. III. Office 2003 Customization Tricks
      1. 10. Customizing Office to Suit Your Style
        1. Displaying, Moving, and Sizing Toolbars
        2. Menu and Toolbar Customization Options
        3. Creating Custom Menus
          1. First, a Game Plan
          2. Customizing an Existing Menu
          3. Creating a New Menu
          4. Getting Easy Document Access with Word’s Work Menu
          5. Creating a New Submenu
          6. Adding Menu Commands
          7. Creating Custom Commands for Macros
            1. Custom Macro Commands in Word, PowerPoint, and Access
            2. Custom Macro Commands in Excel
          8. Deleting Menus and Menu Commands
        4. Creating Custom Toolbars
          1. Customizing an Existing Toolbar
          2. Creating a New Toolbar
          3. Adding a Toolbar Submenu or Toolbar Button
          4. Working with Button Images
            1. Copying a Button Image
            2. Assigning a Predefined Button Image
            3. Using the Button Editor
          5. Attaching a Toolbar to an Excel Workbook
        5. Creating Custom Keyboard Shortcuts in Word
        6. Customizing the Office Common Dialog Boxes
          1. Adding a Folder to the My Places Bar
          2. Customizing the My Places Bar Icons
          3. Removing Icons from the My Places Bar
          4. From Here
      2. 11. Maximizing Office with VBA Macros
        1. Using a VBA Macro
          1. Running a Command Macro
          2. Using a Function Macro
          3. Using the Example Code
        2. Recording a VBA Macro
          1. Viewing the Resulting Module
          2. Editing a Recorded Macro
        3. Working with the Visual Basic Editor
          1. Creating a Module
          2. Opening a Module
        4. Working with Macros
          1. The Structure of Macro
          2. Writing Your Own Macro
          3. Running a Command Macro from the Visual Basic Editor
        5. VBA Programming Basics
          1. Understanding Program Variables
            1. Declaring Variables
            2. Variable Data Types
          2. Building VBA Expressions
            1. Understanding Expression Structure
            2. VBA Operators
          3. Working with Objects
            1. Working with Object Properties
              1. Setting the Value of a Property
              2. Returning the Value of a Property
            2. Working with Object Methods
            3. Working with Object Collections
            4. Assigning an Object to a Variable
            5. Working with Multiple Properties or Methods
          4. Code That Makes Decisions
            1. Using If...Then to Make True/False Decisions
            2. Using If...Then...Else to Handle a False Result
            3. Using the Select Case Statement
            4. Code That Loops
            5. Using Do...Loop Structures
            6. Using For...Next Loops
            7. Using For Each...Next Loops
            8. Using Exit For or Exit Do to Exit a Loop
          5. From Here
      3. 12. Putting VBA to Good Use: Practical Macros Everyone Can Use
        1. Word Macros
          1. Saving Frequently
          2. Making Backups as You Work
          3. Opening the Most Recently Used Document at Startup
          4. Creating and Opening a Word Workspace
          5. Displaying Sentence Word Counts
          6. Finding the Longest Sentence
          7. Toggling Hidden Codes and Text
        2. Excel Macros
          1. Assigning Shortcut Keys to Excel Macros
          2. Toggling Gridlines On and Off
          3. Creating a Workbook with a Specified Number of Sheets
          4. Automatically Sorting a Range After Data Entry
          5. Selecting A1 on All Worksheets
          6. Selecting the “Home Cell” on All Worksheets
          7. Selecting the Named Range That Contains the Active Cell
          8. Saving All Open Workbooks
        3. Outlook Macros
          1. Creating Advanced Rules for Handling Incoming Messages
          2. Supplementing a Reminder with an Email Message
          3. Prompting to Save Messages in the Sent Items Folder
          4. Setting Up a Password-Protected Folder
          5. From Here
      4. 13. Taking Advantage of Access Macros
        1. Writing Access Macros
          1. Example: Opening a Report
          2. Running Your Macro
          3. Modifying Existing Macros
          4. Using Names to Create Macro Groups
          5. Example: Creating Access Shortcut Keys
          6. Adding Macro Conditions
        2. Associating Macros with Events
          1. Adding a Macro to a Form
            1. Creating a Macro Command Button
            2. Example: Confirming Changes to a Record
            3. Example: Transferring Data from One Form to Another
          2. Adding a Macro to a Report
            1. Report Section Events
            2. Example: Calculating Page Totals
        3. Troubleshooting Macros
        4. Summary of Macro Actions
          1. From Here
    9. IV. Office 2003 Security Tricks
      1. 14. Securing Office 2003
        1. Setting Document Security Options
          1. Preventing Changes by Opening a Document as Read-Only
          2. Using File Passwords and Encryption
          3. More Options for Protecting Word Documents
            1. Locking Document Formatting
            2. Preventing Untracked Changes
          4. More Options for Protecting Excel Workbooks
            1. Protecting Individual Cells, Objects, and Scenarios
            2. Setting Up Protection Formatting for Cells
            3. Protecting a Range with a Password
            4. Setting Up Protection Formatting for Objects
            5. Setting Up Protection Formatting for Scenarios
            6. Protecting a Worksheet
            7. Protecting Windows and Workbook Structures
          5. Assigning a Password to Your Outlook Personal Folders
          6. Protecting Access Data with Passwords and Permissions
            1. Setting a Database Password
            2. Setting User-Level Database Permissions
        2. Protecting Your Privacy
          1. Setting Document Privacy Options
          2. Using the Remove Hidden Data Tool
          3. Removing Other Private Data
            1. Removing Hidden Text
            2. Removing Hyperlinks
            3. Remove Document Variables
            4. Removing Field Codes with Links
        3. Controlling VBA Security
          1. Setting the Macro Security Level
          2. Self-Certifying Your VBA Projects
          3. Locking a VBA Project
          4. From Here
      2. 15. Enhancing Outlook Email Security and Privacy
        1. Guarding Against Email Viruses
          1. Working with Security Zones
            1. Checking the Outlook Security Zone
            2. Viewing a Restricted Message Using the Internet Zone
          2. Disabling HTML and Rich Text
          3. Handling Attachments
          4. Controlling Third-Party Access to Your Contacts
          5. Controlling Third-Party Access to Sending Messages
        2. Blocking Spam Messages
          1. Setting the Junk E-mail Protection Level
          2. Specifying Safe Senders
          3. Specifying Safe Recipients
          4. Blocking Senders
          5. Blocking Countries and Languages
        3. Maintaining Your Privacy While Reading Email
          1. Controlling Read Receipts
          2. Squashing Web Bugs
        4. Sending and Receiving Secure Email
          1. Setting Up an Email Account with a Digital ID
          2. Obtaining Another Person’s Public Key
          3. Sending a Secure Message
          4. From Here
    10. A. Working with the Windows Registry
      1. Understanding the Registry
      2. Taking a Tour of the Registry
        1. Navigating the Keys Pane
        2. Understanding Registry Settings
        3. Getting to Know the Registry’s Root Keys
          1. HKEY_CLASSES_ROOT (HKCR)
          2. HKEY_CURRENT_USER (HKCU)
          3. HKEY_LOCAL_MACHINE (HKLM)
          4. HKEY_USERS (HKU)
          5. HKEY_CURRENT_CONFIG (HKCC)
        4. Understanding Hives and Registry Files
      3. Keeping the Registry Safe
        1. Backing Up the Registry
        2. Saving the Current Registry State with System Restore
        3. Protecting Keys by Exporting Them to Disk
          1. Exporting a Key to a Hive File
          2. Importing a Hive File
      4. Working with Registry Keys and Settings
        1. Changing the Value of a Registry Entry
          1. Editing a String Value
          2. Editing a DWORD Value
          3. Editing a Binary Value
        2. Renaming a Key or Setting
        3. Creating a New Key or Setting
        4. Deleting a Key or Setting
      5. Finding Registry Entries

    Product information

    • Title: Tricks of the Microsoft® Office Gurus
    • Author(s):
    • Release date: April 2005
    • Publisher(s): Que
    • ISBN: 9780789733696