book

Tricks of the Microsoft® Office Gurus

Name: Tricks of the Microsoft® Office Gurus
Author: Paul McFedries
ISBN: 9780789733696

by Paul McFedries

April 2005

Beginner

552 pages

13h 59m

English

Que

Read now

Unlock full access

Copyright
Dedication
About the Author
Acknowledgments
We Want to Hear from You!
Introduction
What’s in This BookThis Book’s Special Features
I. Office 2003 Application Tricks
1. Building Dynamic Documents in Word
AutoCorrect TricksReversing AutoCorrectMoving AutoCorrect Entries to Another ComputerEntering Boilerplate Text AutomaticallyUsing AutoCorrect to Enter Boilerplate TextUsing AutoText to Enter Boilerplate TextCreating a Customizable AutoCorrect EntryUsing AutoCorrect to Insert Your SignatureCreating Border Lines On-the-FlyCreating Table Cells On-the-FlyCreating Custom Bulleted Lists On-the-FlyUsing Custom Document PropertiesCreating a Custom Document PropertySearching via Document PropertiesUsing Fields to Insert Dynamic DataInserting a FieldUsing the Field Dialog BoxUnderstanding Field Code SyntaxInserting a Field ManuallyViewing and Navigating FieldsToggling Field CodesNavigating a Document’s FieldsUpdating a FieldUpdating All Fields with a MacroUpdating Fields When Opening a DocumentUpdating Fields by Field TypeUpdating Fields by NamePreventing Updates by Locking a FieldConverting a Field Result to TextKeyboard Shortcuts for FieldsPutting Fields to Good UseViewing Total Editing Time Updated in Real-TimeRunning a MacroCreating “Click-and-Type” Text PlaceholdersCreating a Shortcut Menu of AutoText EntriesBuilding a Formula FieldCalculating Billable Time ChargesCreating Decision-Making FieldsPrompting the User for InputPerforming Calculations in TablesReferencing Table CellsSolving a Relative Reference ProblemAdding Dummy Text to a DocumentUsing the RAND FunctionUsing the Repeat CommandFrom Here
2. Analyzing Data with Excel
Performing a What-If AnalysisPerforming What-If Analysis with a Range SnapshotSetting Up a One-Input Data TableSetting Up a Two-Input TableEditing a Data TableWorking with ScenariosSetting Up Your Worksheet for ScenariosAdding a ScenarioDisplaying a ScenarioUsing Goal Seek for What-If AnalysisRunning Goal SeekGoal Seeking with ChartsSolving Complex Problems with SolverLoading SolverUsing SolverAdding ConstraintsAnalyzing Data with ListsConverting a Range to a ListBasic List OperationsSorting a ListSorting on More Than Three KeysSorting a List in Natural OrderSorting on Part of a FieldSorting Without ArticlesFiltering List DataUsing AutoFilter to Filter a ListAutoFilter Criteria OptionsSetting Up Custom AutoFilter CriteriaShowing Filtered RecordsUsing Complex Criteria to Filter a ListSetting Up a Criteria RangeFiltering a List with a Criteria RangeEntering Compound CriteriaEntering Computed CriteriaSummarizing List DataCreating Automatic SubtotalsSetting Up a List for Automatic SubtotalsDisplaying SubtotalsAdding More SubtotalsRemoving SubtotalsExcel’s List FunctionsAbout List FunctionsList Functions That Don’t Require a Criteria RangeUsing COUNTIF()Using SUMIF()List Functions That Require a Criteria RangeUsing DAVERAGE()Using DGET()From Here
3. Constructing Knockout Presentations in PowerPoint
Organizing Your PresentationOrganization: Telling a StoryOrganizing Your Presentation with an OutlineViewing the Outline Pane and Outlining ToolbarCreating a Presentation OutlineCreating the Top LevelCreating the Second LevelCreating Lower LevelsCreating an Outline from a Text FileControlling the Display of Outline LevelsEditing the Presentation OutlineOrganizing with Custom Slide FootersDisplaying the FooterAdding Custom Footer Text with a ScriptCustomizing the Footer LayoutAdvanced Slide Formatting and DesignSlide Design GuidelinesUsing the Slide Master to Get a Consistent LookViewing and Editing the Slide MasterUsing Multiple Slide MastersEnsuring Good and Consistent DesignCreating a Custom Color SchemeReplacing FontsChanging a Picture’s ColorsSome AutoShape TricksDrawing Circles and SquaresDrawing Shapes QuicklySetting the Default Formatting for an AutoShapeCopying Object FormattingDuplicating Shapes at Evenly Spaced IntervalsSetting the Default Font for Shape TextWrapping Text Within a ShapeHiding Slide Master Shapes in a SlideAdvanced PowerPoint Animation TechniquesAnimation Do’s and Don’tsApplying Built-In Animation EffectsApplying a Slide TransitionApplying an Animation SchemeCreating a Custom AnimationMaking Bullets Appear One at a TimeAnimating a Chart by Series or CategoryAnimating Individual Chart ComponentsAnimating an Organization ChartTaking PowerPoint to the Next Level with Microsoft ProducerFrom Here
4. Taming Access Data
Creating a Totals QueryDisplaying the Total Row in the Design GridSetting Up a Totals Query on a Single FieldSetting Up a Totals Query on Multiple FieldsFiltering the Records Before Calculating TotalsCreating a Totals Query for Groups of RecordsGrouping on Multiple FieldsCreating a Totals Query Using a Calculated FieldCreating a Totals Query Using Aggregate FunctionsCombining Aggregate Functions and TotalsCalculating Units Left In StockCreating Queries That Make DecisionsMaking Decisions with the IIf FunctionDetermining Whether Stock Needs to Be ReorderedMaking Decisions with the Switch FunctionRunning Action QueriesModifying Table Data with an Update QueryRemoving Records from a Table with a Delete QueryCreating New Tables with Make-Table QueriesAdding Records to a Table with an Append QueryPreventing Form Errors by Validating DataHelping Users with Text PromptsPreventing Errors with Data Validation ExpressionsUsing Input Masks for Consistent and Accurate Data EntryUsing the Input Mask WizardCreating a Custom Input Mask ExpressionUsing Form Controls to Limit Data-Entry ChoicesWorking with Yes/No FieldsUsing Check BoxesUsing Toggle ButtonsUsing Option Buttons to Present a Limited Number of ChoicesRunning the Option Group WizardCreating an Option Group by HandUsing Lists to Present a Large Number of ChoicesStarting the List Box or Combo Box WizardGetting List Values from a Table or Query FieldSpecifying Custom List ValuesGetting List Values from the Current TableCreating a Multiple-Column ListUsing Text Boxes as Calculated Form ControlsCreating a Multiple-Column ReportSetting Up the ReportTweaking the Page SetupTroubleshooting Multiple ColumnsAdding Calculations to a ReportCreating a Shadow Effect for Report TextControlling Report OutputAdding Page Breaks After SectionsStarting Sections at the Top of a Row or ColumnAvoiding Widowed RecordsFrom Here

5. Getting the Most Out of Outlook
Getting the Most Out of EmailCustomizing the Inbox Message FieldsChanging the Folder ViewSorting the MessagesGrouping the MessagesDefining a New GroupingEasier Groupings with the Group By BoxFiltering the MessagesDefining a Custom ViewIncoming Message TricksUsing Rules to Process Messages AutomaticallyCreating a Rule from ScratchCreating a Rule from a MessageApplying Colors to Messages from Specific SendersSetting a Message Follow-Up ReminderCutting Your Mailbox Down to SizeChecking the Same Account from Different ComputersOutgoing Message TricksCreating an Email Shortcut for a RecipientHaving Replies Sent to a Different AddressUsing a Different SMTP PortGetting the Most Out of the CalendarStarting Outlook in the Calendar FolderUsing the Calendar FolderUsing the Date NavigatorChanging the Number of Days DisplayedWorking with Calendar’s ViewsOther Navigation TechniquesDisplaying a Second Time ZoneAdding Custom Holidays to Your CalendarPrinting a Blank CalendarTaking Advantage of AutoDateColor-Coding AppointmentsGetting the Most Out of ContactsWorking with the Contacts Folder’s ViewsEditing Data for Multiple ContactsPhoning a ContactQuick Connections with Speed DialAdding a Picture for a ContactDisplaying Contact ActivityFrom Here
II. Office 2003 Sharing and Collaboration Tricks
6. Office in Overdrive: Sharing Data Between Applications
Using the Office ClipboardSetting Office Clipboard OptionsPasting Data in a Different FormatUsing the Clipboard Contents in a Replace OperationInserting an Object from Another ApplicationUnderstanding Compound DocumentsUnderstanding LinkingUnderstanding EmbeddingShould You Link or Embed?Linking an ObjectLinking via the ClipboardInserting a File as a Linked ObjectManaging LinksEmbedding an ObjectEmbedding via the ClipboardInserting a New Embedded ObjectInserting an Embedded FileEditing a Linked or Embedded ObjectMore Office Tools for Sharing DataConverting a Word Outline into a PowerPoint PresentationUsing Word to Custom Format PowerPoint HandoutsImporting Excel Data into AccessExporting a Word Table to AccessAnalyzing Access Data in ExcelTransferring Records via the ClipboardUsing Analyze It with Microsoft Office ExcelUsing Excel’s Get External Data FeaturePublishing Access Data in WordMerging Data from Access, Excel, and OutlookStep 1: Opening the Main DocumentStep 2: Selecting the Mail-Merge Document TypeStep 3: Opening the Data SourceSelecting the Outlook Contacts Folder as the Data SourceDefining a New Data SourceStep 4: Selecting, Sorting, and Filtering the RecipientsSelecting RecipientsFiltering RecipientsSorting RecipientsStep 5: Adding Text and Merge FieldsInserting an Address BlockMatching FieldsCreating Letters and Envelopes at the Same TimeInserting a Greeting LineInserting Data Source FieldsInserting Word FieldsPersonalizing Mail Merges with Fill-In FieldsIntelligent Merging I: The If FieldStep 6: Previewing the ResultsStep 7: Completing the Mail MergeFrom Here
7. Working as a Team: Collaborating with Other Users
Collaborating on a Word DocumentInserting CommentsTracking Word Document ChangesWorking with Comments and ChangesViewing Comments and ChangesNavigating Comments and ChangesAccepting or Rejecting Comments and ChangesCustomizing MarkupWorking with Document VersionsCreating a Master Document and SubdocumentsCreating a Master Document and Subdocuments from an OutlineCreating Subdocuments from Existing DocumentsWorking with SubdocumentsEmbedding Fonts in Shared DocumentsSharing Excel WorkbooksInserting Comments in CellsTracking Worksheet ChangesSharing a WorkbookUpdating a Shared WorkbookWorking with ReviewersHandling ConflictsCollaborating via OutlookSharing Office Documents via EmailSending a Document as an Email MessageSending a Document as an AttachmentSending a Document with a Review RequestRouting DocumentsPreparing Documents for ReviewSharing Your Outlook FoldersSharing Your Folders with PermissionsSharing Your Folders with Delegate AccessAccessing Shared FoldersWorking with Another Email Account as a DelegateRequesting a MeetingSending Out a New Meeting RequestPlanning a MeetingUsing SharePoint to Collaborate on Office DocumentsSharing Documents in a Document LibraryCreating a New Document LibraryUploading an Existing DocumentCreating a New DocumentOpening a DocumentOther Document ActionsCollaborating with a Shared WorkspaceSending a Shared AttachmentFrom Here
8. Office Without Borders: Using Office Documents on the Web
Converting Office Documents to Web PagesConverting a Word Document to a Web PagePublishing an Excel Range, Sheet, or Workbook to the WebPublishing a PowerPoint Presentation to the WebPublishing an Outlook Calendar to the WebDisplaying Web Pages in ExcelOpening a Web Page in ExcelExcel’s HTML Extension: The formula AttributeAdding a Live Stock Price Quote to a WorksheetOffice and FTPInserting Hyperlinks into Office DocumentsHyperlinks and WordCreating a Hyperlink Using AutoCorrectCreating a Hyperlink from ScratchPasting a Hyperlink in WordHyperlinks and ExcelHyperlinks and AccessHyperlinks and PowerPointFrom Here
9. Collaborating with a Tablet PC and OneNote
Office and Windows XP Tablet PC EditionUnderstanding Ink IntegrationEntering Text with the Tablet PC Input PanelUsing the Writing PadUsing the Character PadUsing the Onscreen KeyboardUsing the Office Ink ToolsAdding Ink to a DocumentEditing InkConverting Ink to TextInking an Email MessageCollaborating with InkAdding Ink AnnotationsAdding Ink Comments in WordWorking with Ink Annotations and CommentsCollaborating with OneNote 2003Collaborating with OneNote and OutlookSending Email via OneNoteCreating Outlook ItemsInserting the Details of an Outlook MeetingSharing a Note-Taking SessionStarting a Shared SessionJoining a Shared SessionWorking in a Shared SessionSharing Notes with Other PeopleMoving Notes to a Shared Network FolderPublishing to a Shared Network FolderFrom Here
III. Office 2003 Customization Tricks
10. Customizing Office to Suit Your Style
Displaying, Moving, and Sizing ToolbarsMenu and Toolbar Customization OptionsCreating Custom MenusFirst, a Game PlanCustomizing an Existing MenuCreating a New MenuGetting Easy Document Access with Word’s Work MenuCreating a New SubmenuAdding Menu CommandsCreating Custom Commands for MacrosCustom Macro Commands in Word, PowerPoint, and AccessCustom Macro Commands in ExcelDeleting Menus and Menu CommandsCreating Custom ToolbarsCustomizing an Existing ToolbarCreating a New ToolbarAdding a Toolbar Submenu or Toolbar ButtonWorking with Button ImagesCopying a Button ImageAssigning a Predefined Button ImageUsing the Button EditorAttaching a Toolbar to an Excel WorkbookCreating Custom Keyboard Shortcuts in WordCustomizing the Office Common Dialog BoxesAdding a Folder to the My Places BarCustomizing the My Places Bar IconsRemoving Icons from the My Places BarFrom Here
11. Maximizing Office with VBA Macros
Using a VBA MacroRunning a Command MacroUsing a Function MacroUsing the Example CodeRecording a VBA MacroViewing the Resulting ModuleEditing a Recorded MacroWorking with the Visual Basic EditorCreating a ModuleOpening a ModuleWorking with MacrosThe Structure of MacroWriting Your Own MacroRunning a Command Macro from the Visual Basic EditorVBA Programming BasicsUnderstanding Program VariablesDeclaring VariablesVariable Data TypesBuilding VBA ExpressionsUnderstanding Expression StructureVBA OperatorsWorking with ObjectsWorking with Object PropertiesSetting the Value of a PropertyReturning the Value of a PropertyWorking with Object MethodsWorking with Object CollectionsAssigning an Object to a VariableWorking with Multiple Properties or MethodsCode That Makes DecisionsUsing If...Then to Make True/False DecisionsUsing If...Then...Else to Handle a False ResultUsing the Select Case StatementCode That LoopsUsing Do...Loop StructuresUsing For...Next LoopsUsing For Each...Next LoopsUsing Exit For or Exit Do to Exit a LoopFrom Here
12. Putting VBA to Good Use: Practical Macros Everyone Can Use
Word MacrosSaving FrequentlyMaking Backups as You WorkOpening the Most Recently Used Document at StartupCreating and Opening a Word WorkspaceDisplaying Sentence Word CountsFinding the Longest SentenceToggling Hidden Codes and TextExcel MacrosAssigning Shortcut Keys to Excel MacrosToggling Gridlines On and OffCreating a Workbook with a Specified Number of SheetsAutomatically Sorting a Range After Data EntrySelecting A1 on All WorksheetsSelecting the “Home Cell” on All WorksheetsSelecting the Named Range That Contains the Active CellSaving All Open WorkbooksOutlook MacrosCreating Advanced Rules for Handling Incoming MessagesSupplementing a Reminder with an Email MessagePrompting to Save Messages in the Sent Items FolderSetting Up a Password-Protected FolderFrom Here
13. Taking Advantage of Access Macros
Writing Access MacrosExample: Opening a ReportRunning Your MacroModifying Existing MacrosUsing Names to Create Macro GroupsExample: Creating Access Shortcut KeysAdding Macro ConditionsAssociating Macros with EventsAdding a Macro to a FormCreating a Macro Command ButtonExample: Confirming Changes to a RecordExample: Transferring Data from One Form to AnotherAdding a Macro to a ReportReport Section EventsExample: Calculating Page TotalsTroubleshooting MacrosSummary of Macro ActionsFrom Here
IV. Office 2003 Security Tricks
14. Securing Office 2003
Setting Document Security OptionsPreventing Changes by Opening a Document as Read-OnlyUsing File Passwords and EncryptionMore Options for Protecting Word DocumentsLocking Document FormattingPreventing Untracked ChangesMore Options for Protecting Excel WorkbooksProtecting Individual Cells, Objects, and ScenariosSetting Up Protection Formatting for CellsProtecting a Range with a PasswordSetting Up Protection Formatting for ObjectsSetting Up Protection Formatting for ScenariosProtecting a WorksheetProtecting Windows and Workbook StructuresAssigning a Password to Your Outlook Personal FoldersProtecting Access Data with Passwords and PermissionsSetting a Database PasswordSetting User-Level Database PermissionsProtecting Your PrivacySetting Document Privacy OptionsUsing the Remove Hidden Data ToolRemoving Other Private DataRemoving Hidden TextRemoving HyperlinksRemove Document VariablesRemoving Field Codes with LinksControlling VBA SecuritySetting the Macro Security LevelSelf-Certifying Your VBA ProjectsLocking a VBA ProjectFrom Here
15. Enhancing Outlook Email Security and Privacy
Guarding Against Email VirusesWorking with Security ZonesChecking the Outlook Security ZoneViewing a Restricted Message Using the Internet ZoneDisabling HTML and Rich TextHandling AttachmentsControlling Third-Party Access to Your ContactsControlling Third-Party Access to Sending MessagesBlocking Spam MessagesSetting the Junk E-mail Protection LevelSpecifying Safe SendersSpecifying Safe RecipientsBlocking SendersBlocking Countries and LanguagesMaintaining Your Privacy While Reading EmailControlling Read ReceiptsSquashing Web BugsSending and Receiving Secure EmailSetting Up an Email Account with a Digital IDObtaining Another Person’s Public KeySending a Secure MessageFrom Here
A. Working with the Windows Registry
Understanding the RegistryTaking a Tour of the RegistryNavigating the Keys PaneUnderstanding Registry SettingsGetting to Know the Registry’s Root KeysHKEY_CLASSES_ROOT (HKCR)HKEY_CURRENT_USER (HKCU)HKEY_LOCAL_MACHINE (HKLM)HKEY_USERS (HKU)HKEY_CURRENT_CONFIG (HKCC)Understanding Hives and Registry FilesKeeping the Registry SafeBacking Up the RegistrySaving the Current Registry State with System RestoreProtecting Keys by Exporting Them to DiskExporting a Key to a Hive FileImporting a Hive FileWorking with Registry Keys and SettingsChanging the Value of a Registry EntryEditing a String ValueEditing a DWORD ValueEditing a Binary ValueRenaming a Key or SettingCreating a New Key or SettingDeleting a Key or SettingFinding Registry Entries

Overview

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.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft® Office Outlook® 2007 Inside Out

Publisher Resources

ISBN: 0789733692Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills