How to Do Everything with Microsoft Office Excel 2003

TeamLib......Page 0
Cover......Page 1
Contents......Page 4
Acknowledgments......Page 15
Introduction......Page 16
PART I Get Started with Excel and Create Worksheets......Page 20
CHAPTER 1 Get Started with Excel......Page 22
Start Excel......Page 23
Use or Hide the Task Pane......Page 26
Understand the Excel Screen......Page 28
Understand Worksheets and Workbooks......Page 29
Open a Workbook from the Getting Started Task Pane......Page 30
Open a Workbook from the Open Dialog Box......Page 31
Open a Workbook from Windows Explorer or Your Desktop......Page 32
Navigate to the Worksheet You Need......Page 33
Navigate to Cells and Ranges in a Worksheet......Page 34
Select Cells and Ranges of Cells......Page 35
Get Help with Excel......Page 40
CHAPTER 2 Configure Excel to Suit Your Working Needs......Page 44
Split the Excel Window to Show Separate Parts at Once......Page 45
Open ExtraWindows toWork in Different Areas of aWorksheet......Page 46
Arrange Open Windows......Page 47
Zoom In and Out......Page 49
Use Freezing to Keep Key Rows and Columns Visible......Page 50
Set Options to Make Excel Easier to Use......Page 51
Choose View Options to Customize Excel¡¯s Visual Appearance......Page 52
Understand (and Maybe Choose) Calculation Options......Page 54
Set Edit Options to Fine-Tune Editing Maneuvers......Page 56
Choose General Options......Page 58
Choose Suitable Save Options......Page 59
Set Spelling Options......Page 61
Configure AutoCorrect to Save Time and Effort......Page 63
Configure AutoCorrect¡¯s Basic Settings......Page 64
Create and Delete AutoCorrect Entries......Page 66
Undo an AutoCorrect Correction......Page 67
CHAPTER 3 Create Spreadsheets and Enter Data......Page 70
Create a New Workbook......Page 71
Create a New Workbook Based on an Existing Workbook......Page 72
Create a New Workbook Based on a Template......Page 73
Save a Workbook for the First Time......Page 77
Save a Workbook Under a Different Name......Page 79
Enter Property Information for a Workbook......Page 80
Create Your Own Templates......Page 83
Create and Save a Template......Page 84
Enter Data Manually......Page 85
Undo an Action......Page 86
Enter Data Using Drag and Drop......Page 88
Enter Data with Paste, Paste Options, and Paste Special......Page 89
Link Data Across Worksheets or Across Workbooks......Page 91
Use AutoFill to Enter Data Series Quickly......Page 92
Create Custom AutoFill Lists......Page 93
Use Find and Replace......Page 94
Recover Your Work If Excel Crashes......Page 95
Recover a Workbook from an AutoRecover File......Page 98
CHAPTER 4 Format Worksheets for Best Effect......Page 100
Add, Delete, Hide, and Redisplay Worksheets......Page 101
Move and Copy Worksheets......Page 102
Rename a Worksheet......Page 103
Format Cells and Ranges......Page 104
Understand Excel¡¯s Number Formats......Page 105
Apply Visual Formatting......Page 112
Format Rows and Columns......Page 114
Use Conditional Formatting......Page 116
Use AutoFormat to Apply Canned Formatting Quickly......Page 117
Use Styles......Page 118
CHAPTER 5 Add Graphics and Drawings to Worksheets......Page 122
Understand How Excel Handles Graphical Objects......Page 123
Insert Clip Art in Worksheets......Page 124
Work with Shapes, AutoShapes, and WordArt......Page 128
Add AutoShapes......Page 129
Add WordArt Objects to Worksheets......Page 131
Format a Drawing Object......Page 133
Position Drawing Objects......Page 138
Use Text Boxes to Position Text Wherever You Need It......Page 139
Crop a Picture......Page 140
Import Pictures from Scanners and Cameras......Page 141
Add Diagrams to Worksheets......Page 142
Create Basic Diagrams with the Diagram Applet......Page 143
Create Organization Charts......Page 145
CHAPTER 6 Check, Lay Out, and Print Worksheets......Page 148
Check the Spelling in Worksheets......Page 149
Run a Spell Check......Page 150
Set the Print Area......Page 152
Set the Print Area from the Page Setup Dialog Box......Page 153
Scale the Printout to Fit the Paper......Page 154
Use Print Preview to See How the Printout Will Look......Page 155
Add Effective Headers and Footers to Worksheets......Page 157
Remove All Page Breaks from the Active Worksheet......Page 159
Check and Change Margins......Page 160
Choose Which Items to Include in the Printout......Page 162
Print Instantly with the Default Settings......Page 163
Control Printing Using the Print Dialog Box......Page 164
PART II Calculate, Manipulate, and Analyze Data......Page 166
CHAPTER 7 Perform Calculations with Functions......Page 168
Understand the Components of a Function......Page 169
Type a Function Directly into a Cell......Page 170
Use the AutoSum Drop-Down Menu......Page 172
Use the Insert Function Dialog Box......Page 173
Nest One Function Inside Another Function......Page 174
Monitor Calculations with the Watch Window......Page 175
Date and Time Functions......Page 176
Financial Functions......Page 177
Logical Functions......Page 178
Information Functions......Page 179
Statistical Functions......Page 180
Text Functions......Page 181
CHAPTER 8 Create Formulas to Perform Custom Calculations......Page 184
Operands......Page 185
Operators......Page 186
Understand and Change Operator Precedence......Page 187
Refer to Cells and Ranges in Formulas......Page 188
Refer to Other Worksheets and Other Workbooks in Formulas......Page 189
Try Entering a Formula......Page 190
Use Range Names and Labels in Formulas......Page 191
Use Absolute, Relative, and Mixed References in Formulas......Page 192
Work with Array Formulas......Page 193
Hide Formulas from Other Users......Page 194
Troubleshoot Formulas......Page 195
Fix Formatting, Operator Precedence, and Range-Change Errors......Page 196
Configure Error-Checking Options......Page 197
Audit Formulas and Check for Errors Manually......Page 198
CHAPTER 9 Organize Data with Excel Databases......Page 206
Understand What an Excel Database Is......Page 207
Enter Data in a Database......Page 208
Enter and Edit Data with Data Entry Forms......Page 209
Perform a Quick Sort by a Single Field......Page 211
Sort by a Custom Sort Order......Page 212
Perform Quick Filtering with AutoFilter......Page 213
Create Custom Filters......Page 215
Link an Excel Worksheet to an External Database......Page 217
Link to a Database with the Query Wizard......Page 218
Customize a Query with MS Query......Page 223
Perform Web Queries......Page 225
CHAPTER 10 Outline and Consolidate Worksheets......Page 228
Use Outlining to Create Collapsible Worksheets......Page 229
Chose Custom Settings for Outlining......Page 231
Create an Outline Manually......Page 232
Change the Outlined Area After Adding or Deleting Material......Page 233
Toggle the Display of the Outline Symbols......Page 234
Consolidate Multiple Worksheets into One Worksheet......Page 235
Consolidate Worksheets by Their Position......Page 236
Consolidate Worksheets by Category......Page 239
Update an Existing Consolidation......Page 240
Change an Existing Consolidation......Page 241
CHAPTER 11 Analyze Data Using PivotTables and PivotCharts......Page 242
Understand PivotTables......Page 243
Create a PivotTable Framework Using the PivotTable and PivotChart Wizard......Page 244
Create the PivotTable on the Framework......Page 247
Change the PivotTable......Page 249
Use the PivotTable Toolbar......Page 250
Change a Field to a Different Function......Page 251
Choose PivotTable Options to Configure a PivotTable......Page 252
Create PivotCharts from PivotTables......Page 255
Create a Conventional Chart from PivotTable Data......Page 256
CHAPTER 12 Solve Problems by Performing What-If Analysis......Page 258
Create a Single-Variable Data Table......Page 259
Add Further Formulas to a Data Table......Page 262
Create a Two-Variable Data Table......Page 263
Change, Copy, or Move a Data Table......Page 264
Create the Worksheet You Want to Manipulate with Scenarios......Page 265
Open the Scenario Manager Dialog Box......Page 266
Create a Scenario for Your Starting Point......Page 267
Edit and Delete Existing Scenarios......Page 269
Merge Scenarios into a Single Worksheet......Page 270
Create Reports from Scenarios......Page 271
Solve Problems with Goal Seek......Page 273
Use the Solver to Manipulate Two or More Values......Page 274
PART III Share, Publish, and Present Data......Page 278
CHAPTER 13 Create Effective Charts to Present Data Visually......Page 280
Create a Chart with the Chart Wizard......Page 283
Understand the Basics of Excel Charts......Page 281
Choose the Right Type of Chart for Your Data......Page 286
Use the Chart Toolbar......Page 287
Configure Chart Options......Page 288
Change the Chart Type......Page 289
Change the Plotting Order of the Data Series......Page 290
Configure and Change the Scale of an Axis......Page 291
Format the Chart Area......Page 293
Liven Up Charts with Patterns, Fills, and Pictures......Page 295
Format Different Data Series Using Different Chart Types......Page 298
Format Individual Chart Elements......Page 299
Copy Formatting from One Chart to Another......Page 300
Unlink a Chart from Its Data Source......Page 301
Print Charts......Page 302
Create Custom Chart Types for Easy Reuse......Page 303
CHAPTER 14 Share Workbooks and Collaborate with Colleagues......Page 306
Share a Workbook by Placing It on a Shared Drive......Page 307
Configure Sharing on a Workbook......Page 309
Resolve Conflicts in Shared Workbooks......Page 312
Turn Off Sharing and Remove a User from a Shared Workbook......Page 313
Check Data Entry for Invalid Entries......Page 314
Protect Cells, a Worksheet, or a Workbook......Page 317
Allow Users to Edit Ranges in a Protected Worksheet......Page 320
Protect a Workbook with Passwords......Page 322
Add a Comment to a Cell......Page 323
Edit and Format Comments......Page 324
Use Excel¡¯s Reviewing Toolbar to Navigate Among Comments......Page 325
Send a Workbook for Review......Page 326
Receive and Return a Workbook Sent for Review......Page 327
Receive a Workbook Sent As an Attachment......Page 328
Route a Workbook Around a Group of People......Page 329
Receive a Routed Workbook......Page 331
Turn On and Configure Change Tracking......Page 332
Work with Change Tracking On......Page 333
Review Tracked Changes......Page 334
Prepare Workbooks for Merging......Page 335
Merge the Workbooks with the Master Workbook......Page 336
CHAPTER 15 Using Excel¡¯s Web Capabilities......Page 338
Understand Saving Directly to an Intranet Site or Internet Server......Page 339
Choose Options on the Browsers Tab......Page 340
Choose Options on the Files Tab......Page 341
Choose Options on the Encoding Tab......Page 342
Understand HTML, Round Tripping, and Web File Formats......Page 343
Save a Worksheet or Workbook As a Web Page......Page 344
Work in an Interactive Web Workbook......Page 349
What XML Is For......Page 351
The Benefits XML Offers......Page 352
Work with XML Files in Excel......Page 353
Create XML Files in Excel......Page 355
CHAPTER 16 Use Excel with the Other Office Applications......Page 364
Transfer Data Using the Clipboard and Office Clipboard......Page 365
Understand the Differences Between Embedding and Linking......Page 366
Choose When to Embed and When to Link......Page 367
Embed or Link an Object......Page 368
Edit a Linked Object......Page 372
Edit, Update, and Break Links......Page 373
Insert a Chart in a Word Document......Page 376
Insert Cells in a Word Document......Page 378
Use an Excel Database As the Data Source for a Word Mail Merge......Page 380
Insert a Range of Cells in a PowerPoint Slide......Page 382
Insert PowerPoint Objects in Worksheets......Page 384
PART IV Customize and Automate Excel......Page 386
CHAPTER 17 Customize Excel¡¯s Interface......Page 388
Choose Toolbar and Menu Options......Page 389
Understand Excel¡¯s Many Toolbars......Page 391
Customize a Toolbar......Page 392
Copy a Custom Toolbar to a Workbook......Page 398
Customize a Menu or a Menu Bar by Using the Customize Dialog Box......Page 399
Customize a Menu or a Menu Bar by Using the Rearrange Commands Dialog Box......Page 400
Reset a Menu to Its Default Settings......Page 402
Change the Appearance of a Toolbar Button, Menu Item, or Menu......Page 403
CHAPTER 18 Use Macros to Automate Tasks......Page 406
Understand What Macros Are and What They¡¯re For......Page 407
Understand and Set Security Levels......Page 408
Understand Digital Signatures......Page 413
Record a Macro Using the Macro Recorder......Page 415
Run a Macro from the Macro Dialog Box......Page 418
Create a Toolbar Button or Menu Item to Run a Macro......Page 419
Assigning a Macro to an Object......Page 421
Delete a Macro......Page 422
APPENDIX Keyboard Shortcuts......Page 424
Index......Page 430