Tricks of the Microsoft Office Gurus

by McFedries, Paul

• ISBN13: 9780789733696
• ISBN10: 0789733692
• Edition: 1st
• Format: Paperback
• Copyright: 2005-01-01
• Publisher: Que

Summary

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 Gurusis full of exclusive tricks fromPaul 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 Guruswill help you get the most out of Office 2003.

Table of Contents

Introduction	1	(1)
What's in This Book	1	(1)
This Book's Special Features	2	(5)
I OFFICE 2003 APPLICATION TRICKS
Building Dynamic Documents in Word	7	(46)
AutoCorrect Tricks	7	(11)
Reversing AutoCorrect	8	(1)
Moving AutoCorrect Entries to Another Computer	9	(1)
Entering Boilerplate Text Automatically	10	(3)
Creating a Customizable AutoCorrect Entry	13	(1)
Using AutoCorrect to Insert Your Signature	13	(1)
Creating Border Lines On-the-Fly	14	(2)
Creating Table Cells On-the-Fly	16	(1)
Creating Custom Bulleted Lists On-the-Fly	17	(1)
Using Custom Document Properties	18	(3)
Creating a Custom Document Property	19	(1)
Searching via Document Properties	20	(1)
Using Fields to Insert Dynamic Data	21	(23)
Inserting a Field	22	(7)
Viewing and Navigating Fields	29	(1)
Updating a Field	30	(4)
Keyboard Shortcuts for Fields	34	(1)
Putting Fields to Good Use	34	(10)
Performing Calculations in Tables	44	(5)
Referencing Table Cells	44	(2)
Solving a Relative Reference Problem	46	(3)
Adding Dummy Text to a Document	49	(4)
Using the Rand Function	50	(1)
Using the Repeat Command	50	(1)
From Here	51	(2)
Analyzing Data with Excel	53	(46)
Performing a What-If Analysis	53	(5)
Performing What-If Analysis with a Range Snapshot	54	(1)
Setting Up a One-Input Data Table	55	(1)
Setting Up a Two-Input Table	56	(2)
Editing a Data Table	58	(1)
Working with Scenarios	58	(3)
Setting Up Your Worksheet for Scenarios	59	(1)
Adding a Scenario	59	(2)
Displaying a Scenario	61	(1)
Using Goal Seek for What-If Analysis	61	(4)
Running Goal Seek	62	(2)
Goal Seeking with Charts	64	(1)
Solving Complex Problems with Solver	65	(6)
Loading Solver	67	(1)
Using Solver	67	(2)
Adding Constraints	69	(2)
Analyzing Data with Lists	71	(28)
Converting a Range to a List	72	(1)
Basic List Operations	73	(2)
Sorting a List	75	(4)
Sorting Without Articles	79	(1)
Filtering List Data	80	(8)
Summarizing List Data	88	(1)
Creating Automatic Subtotals	89	(3)
Excel's List Functions	92	(5)
From Here	97	(2)
Constructing Knockout Presentations in PowerPoint	99	(38)
Organizing Your Presentation	100	(12)
Organization: Telling a Story	100	(1)
Organizing Your Presentation with an Outline	101	(6)
Organizing with Custom Slide Footers	107	(5)
Advanced Slide Formatting and Design	112	(11)
Slide Design Guidelines	112	(2)
Using the Slide Master to Get a Consistent Look	114	(2)
Ensuring Good and Consistent Design	116	(2)
Creating a Custom Color Scheme	118	(1)
Replacing Fonts	119	(1)
Changing a Picture's Colors	119	(1)
Some AutoShape Tricks	120	(3)
Advanced PowerPoint Animation Techniques	123	(12)
Animation Do's and Don'ts	123	(1)
Applying Built-In Animation Effects	123	(3)
Creating a Custom Animation	126	(9)
Taking PowerPoint to the Next Level with Microsoft Producer	135	(2)
From Here	135	(2)
Taming Access Data	137	(44)
Creating a Totals Query	137	(10)
Displaying the Total Row in the Design Grid	138	(1)
Setting Up a Totals Query on a Single Field	139	(1)
Setting Up a Totals Query on Multiple Fields	140	(1)
Filtering the Records Before Calculating Totals	140	(1)
Creating a Totals Query for Groups of Records	141	(1)
Grouping on Multiple Fields	141	(1)
Creating a Totals Query Using a Calculated Field	142	(2)
Creating a Totals Query Using Aggregate Functions	144	(1)
Combining Aggregate Functions and Totals	145	(2)
Creating Queries That Make Decisions	147	(3)
Making Decisions with the IIf Function	147	(1)
Making Decisions with the Switch Function	148	(2)
Running Action Queries	150	(5)
Modifying Table Data with an Update Query	150	(2)
Removing Records from a Table with a Delete Query	152	(1)
Creating New Tables with Make-Table Queries	153	(1)
Adding Records to a Table with an Append Query	154	(1)
Preventing Form Errors by Validating Data	155	(5)
Helping Users with Text Prompts	155	(1)
Preventing Errors with Data Validation Expressions	156	(1)
Using Input Masks for Consistent and Accurate Data Entry	157	(3)
Using Form Controls to Limit Data-Entry Choices	160	(11)
Working with Yes/No Fields	161	(2)
Using Option Buttons to Present a Limited Number of Choices	163	(3)
Using Lists to Present a Large Number of Choices	166	(4)
Using Text Boxes as Calculated Form Controls	170	(1)
Creating a Multiple-Column Report	171	(3)
Setting Up the Report	171	(1)
Tweaking the Page Setup	172	(1)
Troubleshooting Multiple Columns	173	(1)
Adding Calculations to a Report	174	(3)
Creating a Shadow Effect for Report Text	177	(1)
Controlling Report Output	177	(4)
Adding Page Breaks After Sections	177	(1)
Starting Sections at the Top of a Row or Column	178	(1)
Avoiding Widowed Records	178	(1)
From Here	179	(2)
Getting the Most Out of Outlook	181	(40)
Getting the Most Out of Email	181	(19)
Customizing the Inbox Message Fields	182	(1)
Changing the Folder View	183	(1)
Sorting the Messages	184	(1)
Grouping the Messages	185	(3)
Filtering the Messages	188	(1)
Defining a Custom View	189	(1)
Incoming Message Tricks	190	(7)
Outgoing Message Tricks	197	(3)
Getting the Most Out of the Calendar	200	(12)
Starting Outlook in the Calendar Folder	201	(1)
Using the Calendar Folder	201	(6)
Displaying a Second Time Zone	207	(1)
Adding Custom Holidays to Your Calendar	208	(2)
Printing a Blank Calendar	210	(1)
Taking Advantage of AutoDate	211	(1)
Color-Coding Appointments	212	(1)
Getting the Most Out of Contacts	212	(9)
Working with the Contacts Folder's Views	212	(1)
Editing Data for Multiple Contacts	213	(1)
Phoning a Contact	214	(1)
Quick Connections with Speed Dial	215	(1)
Adding a Picture for a Contact	216	(1)
Displaying Contact Activity	217	(1)
From Here	218	(3)
II OFFICE 2003 SHARING AND COLLABORATION TRICKS
Office in Overdrive: Sharing Data Between Applications	221	(40)
Using the Office Clipboard	221	(4)
Setting Office Clipboard Options	223	(1)
Pasting Data in a Different Format	224	(1)
Using the Clipboard Contents in a Replace Operation	225	(1)
Inserting an Object from Another Application	225	(9)
Understanding Compound Documents	226	(3)
Linking an Object	229	(3)
Embedding an Object	232	(1)
Editing a Linked or Embedded Object	233	(1)
More Office Tools for Sharing Data	234	(8)
Converting a Word Outline into a PowerPoint Presentation	234	(1)
Using Word to Custom Format PowerPoint Handouts	235	(1)
Importing Excel Data into Access	236	(2)
Exporting a Word Table to Access	238	(1)
Analyzing Access Data in Excel	239	(2)
Publishing Access Data in Word	241	(1)
Merging Data from Access, Excel, and Outlook	242	(19)
Step 1: Opening the Main Document	242	(1)
Step 2: Selecting the Mail-Merge Document Type	243	(1)
Step 3: Opening the Data Source	244	(2)
Step 4: Selecting, Sorting, and Filtering the Recipients	246	(2)
Step 5: Adding Text and Merge Fields	248	(9)
Step 6: Previewing the Results	257	(1)
Step 7: Completing the Mail Merge	258	(1)
From Here	259	(2)
Working as a Team: Collaborating with Other Users	261	(42)
Collaborating on a Word Document	261	(13)
Inserting Comments	261	(3)
Tracking Word Document Changes	264	(2)
Working with Comments and Changes	266	(3)
Working with Document Versions	269	(1)
Creating a Master Document and Subdocuments	270	(4)
Embedding Fonts in Shared Documents	274	(1)
Sharing Excel Workbooks	274	(6)
Inserting Comments in Cells	274	(1)
Tracking Worksheet Changes	275	(2)
Sharing a Workbook	277	(3)
Collaborating via Outlook	280	(13)
Sharing Office Documents via Email	281	(3)
Routing Documents	284	(1)
Preparing Documents for Review	284	(2)
Sharing Your Outlook Folders	286	(4)
Requesting a Meeting	290	(3)
Using SharePoint to Collaborate on Office Documents	293	(10)
Sharing Documents in a Document Library	294	(5)
Collaborating with a Shared Workspace	299	(1)
Sending a Shared Attachment	300	(2)
From Here	302	(1)
Office Without Borders: Using Office Documents on the Web	303	(18)
Converting Office Documents to Web Pages	303	(6)
Converting a Word Document to a Web Page	303	(2)
Publishing an Excel Range, Sheet, or Workbook to the Web	305	(1)
Publishing a PowerPoint Presentation to the Web	306	(2)
Publishing an Outlook Calendar to the Web	308	(1)
Displaying Web Pages in Excel	309	(2)
Opening a Web Page in Excel	309	(1)
Excel's HTML Extension: The formula Attribute	309	(1)
Adding a Live Stock Price Quote to a Worksheet	310	(1)
Office and FTP	311	(2)
Inserting Hyperlinks into Office Documents	313	(8)
Hyperlinks and Word	313	(3)
Hyperlinks and Excel	316	(1)
Hyperlinks and Access	317	(1)
Hyperlinks and PowerPoint	318	(1)
From Here	319	(2)
Collaborating with a Tablet PC and OneNote	321	(22)
Office and Windows XP Tablet PC Edition	322	(4)
Understanding Ink Integration	322	(1)
Entering Text with the Tablet PC Input Panel	323	(3)
Using the Office Ink Tools	326	(4)
Adding Ink to a Document	326	(2)
Editing Ink	328	(1)
Converting Ink to Text	329	(1)
Inking an Email Message	329	(1)
Collaborating with Ink	330	(3)
Adding Ink Annotations	330	(1)
Adding Ink Comments in Word	331	(1)
Working with Ink Annotations and Comments	331	(2)
Collaborating with OneNote 2003	333	(10)
Collaborating with OneNote and Outlook	333	(3)
Sharing a Note-Taking Session	336	(2)
Sharing Notes with Other People	338	(2)
From Here	340	(3)
III OFFICE 2003 CUSTOMIZATION TRICKS
Customizing Office to Suit Your Style	343	(18)
Displaying, Moving, and Sizing Toolbars	343	(2)
Menu and Toolbar Customization Options	345	(2)
Creating Custom Menus	347	(6)
First, a Game Plan	348	(1)
Customizing an Existing Menu	348	(1)
Creating a New Menu	349	(1)
Getting Easy Document Access with Word's Work Menu	350	(1)
Creating a New Submenu	351	(1)
Adding Menu Commands	351	(1)
Creating Custom Commands for Macros	351	(2)
Deleting Menus and Menu Commands	353	(1)
Creating Custom Toolbars	353	(4)
Customizing an Existing Toolbar	353	(1)
Creating a New Toolbar	353	(1)
Adding a Toolbar Submenu or Toolbar Button	354	(1)
Working with Button Images	354	(2)
Attaching a Toolbar to an Excel Workbook	356	(1)
Creating Custom Keyboard Shortcuts in Word	357	(1)
Customizing the Office Common Dialog Boxes	358	(3)
Adding a Folder to the My Places Bar	358	(1)
Customizing the My Places Bar Icons	359	(1)
Removing Icons from the My Places Bar	359	(1)
From Here	360	(1)
Maximizing Office with VBA Macros	361	(34)
Using a VBA Macro	362	(2)
Running a Command Macro	362	(1)
Using a Function Macro	363	(1)
Using the Example Code	364	(1)
Recording a VBA Macro	364	(3)
Viewing the Resulting Module	366	(1)
Editing a Recorded Macro	367	(1)
Working with the Visual Basic Editor	367	(2)
Creating a Module	368	(1)
Opening a Module	369	(1)
Working with Macros	369	(3)
The Structure of Macro	369	(1)
Writing Your Own Macro	370	(2)
Running a Command Macro from the Visual Basic Editor	372	(1)
VBA Programming Basics	372	(23)
Understanding Program Variables	372	(2)
Building VBA Expressions	374	(4)
Working with Objects	378	(5)
Code That Makes Decisions	383	(10)
From Here	393	(2)
Putting VBA to Good Use: Practical Macros Everyone Can Use	395	(30)
Word Macros	395	(11)
Saving Frequently	396	(1)
Making Backups as You Work	397	(2)
Opening the Most Recently Used Document at Startup	399	(1)
Creating and Opening a Word Workspace	399	(2)
Displaying Sentence Word Counts	401	(2)
Finding the Longest Sentence	403	(2)
Toggling Hidden Codes and Text	405	(1)
Excel Macros	406	(10)
Assigning Shortcut Keys to Excel Macros	406	(3)
Toggling Gridlines On and Off	409	(1)
Creating a Workbook with a Specified Number of Sheets	409	(1)
Automatically Sorting a Range After Data Entry	410	(1)
Selecting A1 on All Worksheets	411	(1)
Selecting the ``Home Cell'' on All Worksheets	412	(1)
Selecting the Named Range That Contains the Active Cell	413	(2)
Saving All Open Workbooks	415	(1)
Outlook Macros	416	(9)
Creating Advanced Rules for Handling Incoming Messages	416	(2)
Supplementing a Reminder with an Email Message	418	(2)
Prompting to Save Messages in the Sent Items Folder	420	(1)
Setting Up a Password-Protected Folder	421	(2)
From Here	423	(2)
Taking Advantage of Access Macros	425	(24)
Writing Access Macros	426	(8)
Example: Opening a Report	428	(1)
Running Your Macro	429	(1)
Modifying Existing Macros	429	(1)
Using Names to Create Macro Groups	430	(1)
Example: Creating Access Shortcut Keys	431	(1)
Adding Macro Conditions	432	(2)
Associating Macros with Events	434	(9)
Adding a Macro to a Form	434	(5)
Adding a Macro to a Report	439	(4)
Troubleshooting Macros	443	(1)
Summary of Macro Actions	444	(5)
From Here	446	(3)
IV OFFICE 2003 SECURITY TRICKS
Securing Office 2003	449	(32)
Setting Document Security Options	449	(16)
Preventing Changes by Opening a Document as Read-Only	450	(1)
Using File Passwords and Encryption	451	(1)
More Options for Protecting Word Documents	452	(3)
More Options for Protecting Excel Workbooks	455	(5)
Assigning a Password to Your Outlook Personal Folders	460	(1)
Protecting Access Data with Passwords and Permissions	461	(4)
Protecting Your Privacy	465	(10)
Setting Document Privacy Options	465	(1)
Using the Remove Hidden Data Tool	466	(2)
Removing Other Private Data	468	(7)
Controlling VBA Security	475	(6)
Setting the Macro Security Level	475	(2)
Self-Certifying Your VBA Projects	477	(1)
Locking a VBA Project	478	(1)
From Here	479	(2)
Enhancing Outlook Email Security and Privacy	481	(26)
Guarding Against Email Viruses	481	(12)
Working with Security Zones	482	(2)
Disabling HTML and Rich Text	484	(2)
Handling Attachments	486	(4)
Controlling Third-Party Access to Your Contacts	490	(2)
Controlling Third-Party Access to Sending Messages	492	(1)
Blocking Spam Messages	493	(6)
Setting the Junk E-mail Protection Level	493	(2)
Specifying Safe Senders	495	(2)
Specifying Safe Recipients	497	(1)
Blocking Senders	497	(1)
Blocking Countries and Languages	498	(1)
Maintaining Your Privacy While Reading Email	499	(3)
Controlling Read Receipts	499	(1)
Squashing Web Bugs	500	(2)
Sending and Receiving Secure Email	502	(5)
Setting Up an Email Account with a Digital ID	503	(1)
Obtaining Another Person's Public Key	504	(1)
Sending a Secure Message	505	(1)
From Here	506	(1)
APPENDIX A Working with the Windows Registry	507	(14)
Understanding the Registry	507	(1)
Taking a Tour of the Registry	508	(5)
Navigating the Keys Pane	508	(1)
Understanding Registry Settings	509	(1)
Getting to Know the Registry's Root Keys	510	(2)
Understanding Hives and Registry Files	512	(1)
Keeping the Registry Safe	513	(2)
Backing Up the Registry	513	(1)
Saving the Current Registry State with System Restore	514	(1)
Protecting Keys by Exporting Them to Disk	514	(1)
Working with Registry Keys and Settings	515	(5)
Changing the Value of a Registry Entry	515	(4)
Renaming a Key or Setting	519	(1)
Creating a New Key or Setting	519	(1)
Deleting a Key or Setting	520	(1)
Finding Registry Entries	520	(1)
Index	521

Excerpts

= 0) {slash = '\\';} else {slash = '/';}openLoc = figLoc.substring(0, figLoc.lastIndexOf(slash) + 1);while (pPage.substring(0,3) == '../') {openLoc = openLoc.substring(0, openLoc.lastIndexOf(slash, openLoc.length - 2)+ 1);pPage = pPage.substring(3, pPage.length + 1);}popUpWin =window.open('','popWin','resizable=1,scrollbars=1,location=0,toolbar=0,width=525,height=394');figDoc = popUpWin.document;zhtm= ' ' + pPage + ' ';zhtm += ' ';zhtm += ' ';zhtm += ' ';zhtm += '' + pPage.substring(pPage.lastIndexOf('/') + 1, pPage.length) + '';zhtm += ' ';figDoc.write(zhtm);figDoc.close();}// modified 3.1.99 RWE v4.1 --> Introduction IntroductionIn this introductionWhat's in This BookThis Book's Special FeaturesIt has been estimated that although Microsoft Office 2003 contains more than 10,000 features, the average user is familiar with only about 150 of them. That means that most people have left a whopping 98.5 percent of Office territory unexplored. In practical terms, it also means that most people aren't taking advantage of the power of the Office suite. It means that most people are working inefficiently by trying to make the techniques they know serve a wide range of situations, and it means that most people are working ineffectively because they aren't aware of techniques that could solve their problems and add sophistication to their documents.You'll no doubt be happy to hear that the goal of this book isnotto give you a tour of the 98.5 percent (or whatever) of Office features that you may be unfamiliar with. I don't know anyone who wants to learnallof Office.Instead, my purpose here is to share with you the tips and shortcuts and little-known techniques--in short, thetricks--that I've amassed in my more than 15 years of wrestling with the Office programs. You'll also no doubt be happy to hear that this book shuns what I call "stunt tricks": those arcane and useless tips that have no purpose in the real world and serve only to show off the knowledge and smarts of the writers. A pox on their houses! This book is grounded firmly in the real world of business and other practical concerns, and the tricks I offer are designed to help you work better, faster, safer, and smarter. What's in This BookThis book isn't meant to be read from cover to cover, although you're certainly free to do just that if the mood strikes you. Instead, most of the chapters are set up as self-contained units that you can dip into at will to extract whatever nuggets of information you need. The book is divided into four main parts. To give