How to Do Everything with Microsoft Office Excel 2003

Guy Hart-Davis9780072230710, 0-07-223071-1

Here is the ideal resource for anyone who wants to get the most out of all the new and enhanced features Excel has to offer. Learn the best methods for formatting, printing, and sharing worksheets, adding graphics, importing and exporting data to and from the Web – and much more.Kate J. Chase (Marshfield, VT) is an author, journalist, and online technical support consultant who has written, contributed to, or edited more than a dozen PC reference books, including PC Disaster and Recovery (Sybex, Christmas 2002) and Instant E-Commerce! (Sybex, 2001), as well as Windows XP Power Tools (Sybex, 2002), Office XP Complete (Sybex, 2002), Microsoft Office 2000 Troubleshooting and Problem Solving (Course Technology, 2000), and The IRQ Book (McGraw-Hill, 1999).Kate has been an active participant in Microsoft’s Office beta program and has been using the Office 11 beta since November. Her work has appeared on Microsoft.com’s Office Web site as well on the Microsoft Network, America Online and ZDnet in addition to her books in print.

---

Table of contents :
TeamLib……Page 1
Cover……Page 2
Contents……Page 5
Acknowledgments……Page 16
Introduction……Page 17
PART I Get Started with Excel and Create Worksheets……Page 21
CHAPTER 1 Get Started with Excel……Page 23
Start Excel……Page 24
Use or Hide the Task Pane……Page 27
Understand the Excel Screen……Page 29
Understand Worksheets and Workbooks……Page 30
Open a Workbook from the Getting Started Task Pane……Page 31
Open a Workbook from the Open Dialog Box……Page 32
Open a Workbook from Windows Explorer or Your Desktop……Page 33
Navigate to the Worksheet You Need……Page 34
Navigate to Cells and Ranges in a Worksheet……Page 35
Select Cells and Ranges of Cells……Page 36
Get Help with Excel……Page 41
CHAPTER 2 Configure Excel to Suit Your Working Needs……Page 45
Split the Excel Window to Show Separate Parts at Once……Page 46
Open ExtraWindows toWork in Different Areas of aWorksheet……Page 47
Arrange Open Windows……Page 48
Zoom In and Out……Page 50
Use Freezing to Keep Key Rows and Columns Visible……Page 51
Set Options to Make Excel Easier to Use……Page 52
Choose View Options to Customize Excel¡¯s Visual Appearance……Page 53
Understand (and Maybe Choose) Calculation Options……Page 55
Set Edit Options to Fine-Tune Editing Maneuvers……Page 57
Choose General Options……Page 59
Choose Suitable Save Options……Page 60
Set Spelling Options……Page 62
Configure AutoCorrect to Save Time and Effort……Page 64
Configure AutoCorrect¡¯s Basic Settings……Page 65
Create and Delete AutoCorrect Entries……Page 67
Undo an AutoCorrect Correction……Page 68
CHAPTER 3 Create Spreadsheets and Enter Data……Page 71
Create a New Workbook……Page 72
Create a New Workbook Based on an Existing Workbook……Page 73
Create a New Workbook Based on a Template……Page 74
Save a Workbook for the First Time……Page 78
Save a Workbook Under a Different Name……Page 80
Enter Property Information for a Workbook……Page 81
Create Your Own Templates……Page 84
Create and Save a Template……Page 85
Enter Data Manually……Page 86
Undo an Action……Page 87
Enter Data Using Drag and Drop……Page 89
Enter Data with Paste, Paste Options, and Paste Special……Page 90
Link Data Across Worksheets or Across Workbooks……Page 92
Use AutoFill to Enter Data Series Quickly……Page 93
Create Custom AutoFill Lists……Page 94
Use Find and Replace……Page 95
Recover Your Work If Excel Crashes……Page 96
Recover a Workbook from an AutoRecover File……Page 99
CHAPTER 4 Format Worksheets for Best Effect……Page 101
Add, Delete, Hide, and Redisplay Worksheets……Page 102
Move and Copy Worksheets……Page 103
Rename a Worksheet……Page 104
Format Cells and Ranges……Page 105
Understand Excel¡¯s Number Formats……Page 106
Apply Visual Formatting……Page 113
Format Rows and Columns……Page 115
Use Conditional Formatting……Page 117
Use AutoFormat to Apply Canned Formatting Quickly……Page 118
Use Styles……Page 119
CHAPTER 5 Add Graphics and Drawings to Worksheets……Page 123
Understand How Excel Handles Graphical Objects……Page 124
Insert Clip Art in Worksheets……Page 125
Work with Shapes, AutoShapes, and WordArt……Page 129
Add AutoShapes……Page 130
Add WordArt Objects to Worksheets……Page 132
Format a Drawing Object……Page 134
Position Drawing Objects……Page 139
Use Text Boxes to Position Text Wherever You Need It……Page 140
Crop a Picture……Page 141
Import Pictures from Scanners and Cameras……Page 142
Add Diagrams to Worksheets……Page 143
Create Basic Diagrams with the Diagram Applet……Page 144
Create Organization Charts……Page 146
CHAPTER 6 Check, Lay Out, and Print Worksheets……Page 149
Check the Spelling in Worksheets……Page 150
Run a Spell Check……Page 151
Set the Print Area……Page 153
Set the Print Area from the Page Setup Dialog Box……Page 154
Scale the Printout to Fit the Paper……Page 155
Use Print Preview to See How the Printout Will Look……Page 156
Add Effective Headers and Footers to Worksheets……Page 158
Remove All Page Breaks from the Active Worksheet……Page 160
Check and Change Margins……Page 161
Choose Which Items to Include in the Printout……Page 163
Print Instantly with the Default Settings……Page 164
Control Printing Using the Print Dialog Box……Page 165
PART II Calculate, Manipulate, and Analyze Data……Page 167
CHAPTER 7 Perform Calculations with Functions……Page 169
Understand the Components of a Function……Page 170
Type a Function Directly into a Cell……Page 171
Use the AutoSum Drop-Down Menu……Page 173
Use the Insert Function Dialog Box……Page 174
Nest One Function Inside Another Function……Page 175
Monitor Calculations with the Watch Window……Page 176
Date and Time Functions……Page 177
Financial Functions……Page 178
Logical Functions……Page 179
Information Functions……Page 180
Statistical Functions……Page 181
Text Functions……Page 182
CHAPTER 8 Create Formulas to Perform Custom Calculations……Page 185
Operands……Page 186
Operators……Page 187
Understand and Change Operator Precedence……Page 188
Refer to Cells and Ranges in Formulas……Page 189
Refer to Other Worksheets and Other Workbooks in Formulas……Page 190
Try Entering a Formula……Page 191
Use Range Names and Labels in Formulas……Page 192
Use Absolute, Relative, and Mixed References in Formulas……Page 193
Work with Array Formulas……Page 194
Hide Formulas from Other Users……Page 195
Troubleshoot Formulas……Page 196
Fix Formatting, Operator Precedence, and Range-Change Errors……Page 197
Configure Error-Checking Options……Page 198
Audit Formulas and Check for Errors Manually……Page 199
CHAPTER 9 Organize Data with Excel Databases……Page 207
Understand What an Excel Database Is……Page 208
Enter Data in a Database……Page 209
Enter and Edit Data with Data Entry Forms……Page 210
Perform a Quick Sort by a Single Field……Page 212
Sort by a Custom Sort Order……Page 213
Perform Quick Filtering with AutoFilter……Page 214
Create Custom Filters……Page 216
Link an Excel Worksheet to an External Database……Page 218
Link to a Database with the Query Wizard……Page 219
Customize a Query with MS Query……Page 224
Perform Web Queries……Page 226
CHAPTER 10 Outline and Consolidate Worksheets……Page 229
Use Outlining to Create Collapsible Worksheets……Page 230
Chose Custom Settings for Outlining……Page 232
Create an Outline Manually……Page 233
Change the Outlined Area After Adding or Deleting Material……Page 234
Toggle the Display of the Outline Symbols……Page 235
Consolidate Multiple Worksheets into One Worksheet……Page 236
Consolidate Worksheets by Their Position……Page 237
Consolidate Worksheets by Category……Page 240
Update an Existing Consolidation……Page 241
Change an Existing Consolidation……Page 242
CHAPTER 11 Analyze Data Using PivotTables and PivotCharts……Page 243
Understand PivotTables……Page 244
Create a PivotTable Framework Using the PivotTable and PivotChart Wizard……Page 245
Create the PivotTable on the Framework……Page 248
Change the PivotTable……Page 250
Use the PivotTable Toolbar……Page 251
Change a Field to a Different Function……Page 252
Choose PivotTable Options to Configure a PivotTable……Page 253
Create PivotCharts from PivotTables……Page 256
Create a Conventional Chart from PivotTable Data……Page 257
CHAPTER 12 Solve Problems by Performing What-If Analysis……Page 259
Create a Single-Variable Data Table……Page 260
Add Further Formulas to a Data Table……Page 263
Create a Two-Variable Data Table……Page 264
Change, Copy, or Move a Data Table……Page 265
Create the Worksheet You Want to Manipulate with Scenarios……Page 266
Open the Scenario Manager Dialog Box……Page 267
Create a Scenario for Your Starting Point……Page 268
Edit and Delete Existing Scenarios……Page 270
Merge Scenarios into a Single Worksheet……Page 271
Create Reports from Scenarios……Page 272
Solve Problems with Goal Seek……Page 274
Use the Solver to Manipulate Two or More Values……Page 275
PART III Share, Publish, and Present Data……Page 279
CHAPTER 13 Create Effective Charts to Present Data Visually……Page 281
Create a Chart with the Chart Wizard……Page 284
Understand the Basics of Excel Charts……Page 282
Choose the Right Type of Chart for Your Data……Page 287
Use the Chart Toolbar……Page 288
Configure Chart Options……Page 289
Change the Chart Type……Page 290
Change the Plotting Order of the Data Series……Page 291
Configure and Change the Scale of an Axis……Page 292
Format the Chart Area……Page 294
Liven Up Charts with Patterns, Fills, and Pictures……Page 296
Format Different Data Series Using Different Chart Types……Page 299
Format Individual Chart Elements……Page 300
Copy Formatting from One Chart to Another……Page 301
Unlink a Chart from Its Data Source……Page 302
Print Charts……Page 303
Create Custom Chart Types for Easy Reuse……Page 304
CHAPTER 14 Share Workbooks and Collaborate with Colleagues……Page 307
Share a Workbook by Placing It on a Shared Drive……Page 308
Configure Sharing on a Workbook……Page 310
Resolve Conflicts in Shared Workbooks……Page 313
Turn Off Sharing and Remove a User from a Shared Workbook……Page 314
Check Data Entry for Invalid Entries……Page 315
Protect Cells, a Worksheet, or a Workbook……Page 318
Allow Users to Edit Ranges in a Protected Worksheet……Page 321
Protect a Workbook with Passwords……Page 323
Add a Comment to a Cell……Page 324
Edit and Format Comments……Page 325
Use Excel¡¯s Reviewing Toolbar to Navigate Among Comments……Page 326
Send a Workbook for Review……Page 327
Receive and Return a Workbook Sent for Review……Page 328
Receive a Workbook Sent As an Attachment……Page 329
Route a Workbook Around a Group of People……Page 330
Receive a Routed Workbook……Page 332
Turn On and Configure Change Tracking……Page 333
Work with Change Tracking On……Page 334
Review Tracked Changes……Page 335
Prepare Workbooks for Merging……Page 336
Merge the Workbooks with the Master Workbook……Page 337
CHAPTER 15 Using Excel¡¯s Web Capabilities……Page 339
Understand Saving Directly to an Intranet Site or Internet Server……Page 340
Choose Options on the Browsers Tab……Page 341
Choose Options on the Files Tab……Page 342
Choose Options on the Encoding Tab……Page 343
Understand HTML, Round Tripping, and Web File Formats……Page 344
Save a Worksheet or Workbook As a Web Page……Page 345
Work in an Interactive Web Workbook……Page 350
What XML Is For……Page 352
The Benefits XML Offers……Page 353
Work with XML Files in Excel……Page 354
Create XML Files in Excel……Page 356
CHAPTER 16 Use Excel with the Other Office Applications……Page 365
Transfer Data Using the Clipboard and Office Clipboard……Page 366
Understand the Differences Between Embedding and Linking……Page 367
Choose When to Embed and When to Link……Page 368
Embed or Link an Object……Page 369
Edit a Linked Object……Page 373
Edit, Update, and Break Links……Page 374
Insert a Chart in a Word Document……Page 377
Insert Cells in a Word Document……Page 379
Use an Excel Database As the Data Source for a Word Mail Merge……Page 381
Insert a Range of Cells in a PowerPoint Slide……Page 383
Insert PowerPoint Objects in Worksheets……Page 385
PART IV Customize and Automate Excel……Page 387
CHAPTER 17 Customize Excel¡¯s Interface……Page 389
Choose Toolbar and Menu Options……Page 390
Understand Excel¡¯s Many Toolbars……Page 392
Customize a Toolbar……Page 393
Copy a Custom Toolbar to a Workbook……Page 399
Customize a Menu or a Menu Bar by Using the Customize Dialog Box……Page 400
Customize a Menu or a Menu Bar by Using the Rearrange Commands Dialog Box……Page 401
Reset a Menu to Its Default Settings……Page 403
Change the Appearance of a Toolbar Button, Menu Item, or Menu……Page 404
CHAPTER 18 Use Macros to Automate Tasks……Page 407
Understand What Macros Are and What They¡¯re For……Page 408
Understand and Set Security Levels……Page 409
Understand Digital Signatures……Page 414
Record a Macro Using the Macro Recorder……Page 416
Run a Macro from the Macro Dialog Box……Page 419
Create a Toolbar Button or Menu Item to Run a Macro……Page 420
Assigning a Macro to an Object……Page 422
Delete a Macro……Page 423
APPENDIX Keyboard Shortcuts……Page 425
Index……Page 431