Cover image for Using Microsoft Excel 2000
Using Microsoft Excel 2000
Kelly, Julia.
Personal Author:
Publication Information:
Indianapolis, Ind. : Que, [1999]

Physical Description:
x, 645 pages : illustrations ; 24 cm
General Note:
Includes index.
Format :


Call Number
Material Type
Home Location
Item Holds
HF5548.4.M523 K45 1999 Adult Non-Fiction Central Closed Stacks

On Order



Using Microsoft Excel 2000 is structured in content and approach to fill the needs of the intermediate user, showing you how to get a task done and get on with your job. It provides enough information to be a through reference for all but the most advanced users while not intimidating newer users. This book brings you real-world, practical content that can not only increase the ability to use the software, but also increase your actual productivity level.

Author Notes

Julia Kelly is a writer, teacher, and consultant for desktop applications, including Excel, Access, Word, Publisher, and Outlook. She's been working in Excel for seven years (since Excel 3), and writing about Excel since Excel 5 was released. She's taught classroom courses in Excel at major corporations, and teachers all applications to clients on an individual level. In addition, she develops customized Excel and Access applications for a wide variety of clients. Julia has written or coauthored more than 17 computer books.

Table of Contents

Introductionp. 1
Why This Book?p. 2
Who Should Use This Book?p. 4
How This Book Is Organizedp. 5
Conventions Used in This Bookp. 7
I Workbook Basics
1 Starting, Quitting, and Getting Helpp. 11
Starting and Quitting Excelp. 12
Quitting Excelp. 15
The Excel Environmentp. 15
Getting Helpp. 18
Using the Office Assistantp. 19
Using the Contents, Index, and Answer Wizardp. 20
Changing the Office Assistant's Behavior (a Bit)p. 24
Asking What's This?p. 26
2 Opening, Searching For, and Closing Filesp. 29
Opening Filesp. 30
Opening Non-Excel Files in Excelp. 34
Opening More Than One File at a Timep. 35
Searching for a Filep. 36
Finding a File by Searching for File Propertiesp. 36
Locating the File You Want with Quick Viewp. 39
Using Hyperlinksp. 40
Connecting Files with a Hyperlinkp. 40
Opening Files Using Hyperlinksp. 42
Closing Workbook Filesp. 42
3 Saving Files and Using Templatesp. 45
Saving Files as Workbooksp. 46
Saving a Filep. 46
Saving File Propertiesp. 47
Saving a File in a Different Formatp. 48
Saving an Identical Copy of a Workbookp. 49
Creating and Using Templatesp. 50
Opening a Copy of a Custom Templatep. 51
Editing a Templatep. 52
Using Excel's Built-In Templatesp. 53
4 Saving Excel Data as a Web Pagep. 59
Saving a Worksheet as a Web Pagep. 60
Saving a Static Web Pagep. 60
Saving an Interactive Web Pagep. 67
Using an Interactive Web Pagep. 72
5 Selecting and Navigating Worksheetsp. 79
Naming Worksheetsp. 80
Selecting Worksheetsp. 81
Selecting a Single Worksheetp. 81
Selecting Several Sheets at Oncep. 82
Deselecting a Group of Worksheetsp. 82
Moving Around in a Worksheetp. 84
Selecting Cells in a Worksheetp. 85
Adding and Deleting Worksheetsp. 87
Deleting Worksheetsp. 87
Adding More Worksheetsp. 88
Changing the Default Number of Sheets in a New Workbookp. 89
Rearranging Worksheetsp. 89
Moving Worksheets to Rearrange a Workbookp. 89
Copying Worksheets Within a Workbookp. 90
Moving and Copying Worksheets Between Workbooksp. 92
6 Changing Your View of the Worksheetp. 95
Freezing Worksheet Panes to Keep Rows or Columns in Viewp. 96
Freezing Panesp. 96
Unfreezing Panesp. 98
Splitting Worksheet Panes to View Multiple Areas in a Worksheetp. 98
Using Multiple Windowsp. 101
Arranging Multiple Windows to See Several Worksheets at Oncep. 101
Arranging Multiple Windows to See Several Workbooks at the Same Timep. 104
Arranging Multiple Windows Manuallyp. 106
II Building Worksheets
7 Entering Datap. 109
Entering Data Directly in a Worksheetp. 110
Entering Data in a Single Cellp. 111
AutoFilling a Text Entryp. 111
Creating Your Own Custom Listp. 112
AutoFilling a Number Seriesp. 113
AutoFilling a Growth Seriesp. 114
Repeating the Entry in the Cell Abovep. 114
Entering the Same Data in Several Cellsp. 114
Repeating an Entry in a Text List with AutoCompletep. 116
Entering Data with a Data Entry Formp. 117
Validating Data During Entryp. 118
8 Copying and Importing Data from Other Filesp. 121
Trading Data with Other Programsp. 122
Copying Data from Wordp. 122
Sending Data Over from Accessp. 124
Sending Excel Data to Accessp. 128
Importing a Text Filep. 129
9 Editing Datap. 133
Editing Cell Entriesp. 134
Finding and Replacing Datap. 136
Deleting Datap. 139
Undoing Mistakesp. 139
Moving and Copying Cells on the Same Worksheetp. 141
Moving and Copying Cells to a Different Worksheetp. 144
Inserting and Deleting Rows and Columnsp. 146
Inserting and Deleting Cellsp. 148
Separating a Single Column into Multiple Columnsp. 150
Adding Worksheet Commentsp. 152
Using AutoCorrectp. 154
Letting Excel Check Your Spellingp. 155
10 Simple Calculationsp. 159
Simple Calculations, Quick Answersp. 160
Quickly Calculating Numbers Without a Formulap. 160
Using AutoSum to Enter a Formulap. 161
About Cell Referencesp. 162
About Cell Reference Typesp. 162
Changing References to Relative, Absolute, or Mixedp. 164
Writing Your Own Formulasp. 165
Writing a Simple Formulap. 165
Using AutoFill to Copy a Formulap. 166
Writing a Formula That Links Workbooksp. 169
Editing a Formulap. 171
Auditing a Formulap. 172
11 Complex Calculationsp. 175
Understanding Functionsp. 176
Using Functionsp. 176
Writing a Formula Using Functionsp. 177
Some Common and Useful Functionsp. 179
Using Cell Names and Labelsp. 201
Naming Cellsp. 202
Writing Formulas with Named Cellsp. 206
Writing Formulas with Labelsp. 207
Converting Formula Results to Valuesp. 208
Locating Worksheet Errorsp. 209
Locating Errors in Formulasp. 211
Finding Invalid Data in a Worksheetp. 211
III Formatting
12 Formatting Cellsp. 215
The Formatting Toolbarp. 216
Formatting the Fontp. 217
Making the Cell Fit the Text (and Vice Versa)p. 221
Aligning Entriesp. 221
Changing Column Width and Row Heightp. 224
Best-Fittingp. 224
Matching Precise Width or Heightp. 226
Hiding and Unhiding Rows and Columnsp. 226
Shrinking Entries to Fit in a Cellp. 228
Wrapping Text to Multiple Lines in a Cellp. 229
Transposing a Range from Vertical to Horizontal (or Vice Versa)p. 229
Merging Several Cells into a Single Cell (and Unmerging)p. 231
Formatting Bordersp. 232
Formatting Colorsp. 235
Copying Formatting with Format Painterp. 237
Using AutoFormat to Create a Table with Built-In Formatsp. 239
Adding a Graphic Image to a Worksheetp. 241
13 Formatting Numbersp. 243
Applying Number Formats to Cellsp. 244
Formatting Dates and Timesp. 246
Formatting Text Entriesp. 248
Creating Conditional Number Formatsp. 251
Converting Actual Values to Displayed Valuesp. 255
Hiding Zeroesp. 256
14 Formatting with Styles for Less Effortp. 257
Applying a Built-In Stylep. 258
Changing a Style Definitionp. 259
Creating a Custom Style by Examplep. 262
Copying Styles from Other Workbooksp. 264
Deleting a Stylep. 265
IV Sorting, Filtering, and Summarizing Data
15 Sorting Listsp. 269
Sorting a List by a Single Columnp. 270
Sorting a List by Two or Three Columnsp. 272
Sorting a List by More Than Three Columnsp. 275
Sorting According to a Custom Listp. 277
Sorting a List Randomlyp. 279
Undoing a Sort and Returning the List to Its Former Arrangementp. 281
16 Filtering Listsp. 283
Filtering Recordsp. 284
Filtering a List on a Single Criterionp. 284
Filtering a List on Multiple Criteriap. 286
Calculating Filtered Recordsp. 295
The Query Wizard: A Better Filterp. 298
Using the Query Wizard to Extract Datap. 298
Saving and Reusing a Queryp. 307
Querying a Web Pagep. 309
17 Finding Answers and Making Decisionsp. 311
Goal Seeking for Answersp. 312
Goal Seeking an Answer to a Simple Questionp. 312
Goal Seeking in a More Complex Problemp. 316
Creating and Comparing Scenariosp. 318
18 Summarizing Details with Consolidation or Subtotalsp. 327
Understanding Consolidations and Subtotalsp. 328
Consolidating a Tablep. 328
Updating a Consolidationp. 332
Selecting Consolidation Source Areasp. 332
Subtotaling a Tablep. 333
Hiding Subtotal Levelsp. 336
Showing Subtotal Levelsp. 338
Removing Subtotalingp. 338
Outlining a Table Manuallyp. 339
Summarizing Details with PivotTables and PivotChartsp. 343
Working with PivotTablesp. 344
PivotTable Termsp. 344
Creating a PivotTablep. 346
Using the PivotTable and PivotChart Wizardp. 347
Changing the Data Source Rangep. 351
Refreshing Datap. 355
The PivotTable Toolbarp. 355
Changing PivotTable Arrangementp. 356
Grouping Items in a Fieldp. 357
Pivoting the Tablep. 358
Adding Fieldsp. 360
Showing and Hiding Inner Field Detailsp. 360
Creating Page Fieldsp. 361
Creating Separate Pagesp. 362
Removing Fieldsp. 364
Changing PivotTable Calculationsp. 365
Showing Underlying Detailsp. 367
Formatting a PivotTablep. 367
Formatting Numbersp. 368
Formatting Tablesp. 368
Changing Label Textp. 370
Working with PivotChartsp. 370
PivotCharts Versus Normal Chartsp. 371
Creating PivotChartsp. 372
Creating a PivotChart from Raw Datap. 372
Creating a PivotChart from a PivotTablep. 376
Creating a Normal Chart from a PivotTablep. 377
Rearranging PivotChartsp. 378
Formatting PivotChartsp. 379
Embedding a PivotChart on a Worksheetp. 381
Formatting Colors and Fontsp. 382
V Charts
20 Creating a Chartp. 385
Creating a Chart with the Chart Wizardp. 386
Types of Chartsp. 390
The Chart Toolbarp. 393
Changing the Chart Typep. 394
Changing Chart Datap. 395
Adding More Data to a Source Rangep. 395
Changing the Data Source Rangep. 397
Changing Chart Orientationp. 397
Moving and Resizing a Chartp. 398
Changing the Chart Locationp. 399
21 Formatting a Chartp. 403
Formatting Chart Colors, Lines, and Fontsp. 404
Deleting a Chart Elementp. 405
Changing Axis Scalep. 406
Reordering the Seriesp. 408
Resizing and Exploding a Pie Chartp. 410
Making the Pie Biggerp. 410
Exploding the Piep. 411
Changing 3D Perspectivep. 413
Changing Perspective by Draggingp. 413
Changing Perspective Using a Dialog Boxp. 414
Adding a Trendlinep. 415
Adding a Trendline to Scientific Datap. 415
Adding a Moving Average to Business Datap. 418
Adding a Secondary Axisp. 419
Adding Data Labelsp. 422
Creating Picture Markersp. 423
Saving and Reusing Your Custom Chart Formatp. 427
22 Putting Data on a Mapp. 431
Introducing Microsoft Mapp. 432
Creating a Geographical Data Mapp. 433
Adding Features to Your Mapp. 436
Labeling Map Itemsp. 437
Repositioning and Enlarging a Mapp. 439
Using the Map Controlp. 440
Creating Custom Pin Mapsp. 442
VI Printing
23 Deciding What to Printp. 449
Previewing Printed Pagesp. 450
Printing a Quick Copy of a Worksheetp. 452
Printing Part of a Worksheetp. 452
Setting a Print Areap. 453
Removing a Print Areap. 454
Printing a Selected Rangep. 454
Printing Specific Pagesp. 455
Printing a Chartp. 456
Printing a Chart Sheetp. 457
Printing an Entire Workbookp. 459
24 Formatting the Printed Pagep. 461
Changing Page Breaksp. 462
Changing Page Breaks in the Worksheetp. 462
Using Page Break Previewp. 464
Changing the Page Layoutp. 465
Changing the Page Orientationp. 465
Changing the Paging Orderp. 466
Changing the Centeringp. 468
Changing Page Marginsp. 468
Printing a Worksheet on a Specific Number of Pagesp. 470
Printing Row and Column Labels on Every Pagep. 471
Creating a Custom Header or Footerp. 472
Printing Worksheet Gridlinesp. 474
Printing in Black and Whitep. 475
Formatting Printed Chartsp. 475
Resizing a Printed Chartp. 475
Printing Data Markers in Black and Whitep. 478
VII Sharing Data with Other Users and Other Applications
25 Sharing a Workbook with Other Usersp. 481
Sharing a Workbookp. 482
Who's Using a Shared Workbook?p. 484
Saving a Shared Workbookp. 484
Reviewing Changes in a Shared Workbookp. 486
Resolving Conflicts in a Shared Workbookp. 486
Tracking Changes in a Shared Workbookp. 487
Accepting and Rejecting Tracked Changesp. 488
Merging Shared Workbooksp. 490
Printing a History of Changesp. 491
Unsharing a Workbookp. 493
Saving a Workbook as Read-Only (the Opposite of Sharing)p. 493
Protecting Workbooks and Worksheetsp. 494
26 Sharing Data Between Applicationsp. 499
Sharing Data: Pasting, Embedding, and Linking Objectsp. 500
Sending Data from Excel to Wordp. 501
Copying and Pasting Datap. 501
Embedding an Objectp. 502
Linking to the Source Filep. 505
Bringing Data from Word into Excelp. 507
Copying and Pasting Word Text to Excelp. 507
Embedding a Word Object in Excelp. 508
Linking a Word Object into Excelp. 509
Changing a Link's Sourcep. 509
Dragging a Hyperlink or Shortcut Icon into Excelp. 511
Pasting Excel Data to a PowerPoint Slidep. 512
VIII Customizing and Automating
27 Customizing Your Excel Screenp. 517
Customizing Screen Elementsp. 518
Changing Your Screen Displayp. 518
Using the Full Screenp. 519
Zooming In and Outp. 520
Personalizing Your Toolbarsp. 522
Moving Toolbarsp. 523
Hiding and Showing Toolbarsp. 524
Changing Buttons in Built-In Toolbarsp. 526
Creating Personal Toolbarsp. 528
Customizing Your Menu Barp. 530
28 Automating Repetitive Work with Macrosp. 535
Introducing Macrosp. 536
Recording a Macrop. 536
Running a Macrop. 539
Editing a Macrop. 540
Attaching a Macro to a Toolbar Button or Menu Commandp. 543
Creating a Toggle Macrop. 545
29 Using Excel's Built-In Templatesp. 549
Working with Templatesp. 550
Installing the Template Wizard Add-Inp. 550
Entering Automated Information in the Templatep. 551
Setting Up the Collection Databasep. 553
Setting Up a Database While You Set Up a New Templatep. 553
Setting Up a Database for an Existing Templatep. 555
Saving Copies of the Template to Collect Summary Datap. 558
Using the Collection Databasep. 559
IX Creative Excel
30 Drawing Objectsp. 563
Basic Drawingp. 564
Creating AutoShapesp. 567
Drawing Text Boxesp. 568
Converting Objects to 3Dp. 570
Creating WordArt Objectsp. 571
31 Creative Worksheetsp. 575
Creative Approaches to Worksheet Applicationsp. 576
Kurt's Invoice--a Creative Invoice Solutionp. 576
How Kurt's Invoice Worksp. 576
Setting Up the Invoice Worksheetp. 578
Automating the Invoice with Macrosp. 581
Editing the Macrop. 583
Making the Macros Handyp. 584
Theatre Seatingp. 587
How the Seating Chart Worksp. 587
Creating the Seating Chart Workbookp. 588
32 Creative Chartsp. 593
Creative Approaches to Chartingp. 594
Giff's Chartp. 594
Stacked Columnsp. 600
Dynamic Chart Displayp. 601
Glossaryp. 615
Indexp. 629