Mastering Microsoft Excel 2002

✍ Scribed by Gini Courter, Annette Marquis

• Publisher: Sybex Inc
• Year: 2001
• Tongue: English
• Leaves: 929
• Edition: 1st
• Category: Library

✦ Synopsis

--This book delivers all the basic concepts plus expanded coverage of advanced topics such as VBA macros and programming, Web publishing, and Office integration. --From fundamental to advanced topics, this comprehensive guide appeals to Excel producti

✦ Table of Contents

Contents......Page 11
Introduction......Page 22
PART I: PUTTING EXCEL 2002 TO WORK FOR YOU......Page 30
1 Working in Excel 2002......Page 32
What’s New in Excel 2002......Page 33
New Features in the Excel Interface......Page 36
Interacting with Excel......Page 50
Collaboration with Excel 2002......Page 55
Moving Beyond the Standard Keyboard......Page 56
Minimizing the Impact of Crashes......Page 67
Closing Workbooks and Excel......Page 69
2 Creating Excel Worksheets......Page 70
Entering and Editing Cell Entries......Page 71
Working with Numbers......Page 81
Filling Formulas, Numbers, and Dates......Page 88
Formatting Numbers......Page 91
Previewing and Printing......Page 102
3 Formatting Text and Worksheets......Page 116
Formatting Text......Page 117
Applying AutoFormats and Styles......Page 127
Adjusting Worksheet Layout......Page 130
Correcting Worksheet Spelling......Page 142
4 Using Names and External References......Page 148
Working with Worksheets......Page 149
Naming Ranges......Page 153
Using Names......Page 157
Linking to Other Data......Page 162
Linking with Hyperlinks......Page 166
Enabling and Using Smart Tags......Page 171
PART II: USING EXCEL’S FUNCTIONS......Page 174
5 Calculating with Functions......Page 176
Using Functions......Page 177
Finding and Fixing Formula Errors......Page 187
Automatic and Manual Calculation......Page 196
6 Manipulating Text and Dates......Page 198
Working with Text in Excel......Page 199
Working with Dates and Times......Page 209
7 Using Decision Making Functions......Page 226
Using IF Functions......Page 227
Using Excel’s Lookup Functions......Page 233
Trapping Errors with Logical Functions......Page 243
8 Using Financial Functions......Page 246
Working with Present and Future Value......Page 247
Using the Depreciation Functions......Page 261
Rounding Numbers in Excel......Page 263
Using the QUOTIENT and MOD Functions......Page 264
Generating Random Numbers......Page 267
PART III: USING CHARTS AND GRAPHICS......Page 268
9 Creating Easy-to-Understand Charts......Page 270
A Quick Overview of Chart Types......Page 271
Creating a Chart......Page 275
Editing and Formatting Charts......Page 281
10 Advanced Charting in Excel 2002......Page 292
Creating Specialized Charts......Page 293
Creating and Saving Chart Types......Page 311
Additional Charting Techniques......Page 312
11 Using Drawing and Graphics Tools......Page 324
Using Microsoft Draw......Page 325
Accessing Clips from Microsoft Clip Organizer......Page 345
Inserting Pictures into Worksheets......Page 359
Inserting Pictures from Other Sources......Page 364
PART IV: MANAGING DATA WITH EXCEL 2002......Page 366
12 Using an Excel Database......Page 368
Basic Database Concepts......Page 369
Sorting an Excel Database......Page 374
Filtering a Database......Page 378
Creating Subtotals......Page 389
Using Data Forms......Page 394
Using Dfunctions to Summarize a Database......Page 396
Applying Conditional Formatting......Page 399
Using the Conditional Sum Wizard......Page 408
13 Using Excel 2002 to Present Information......Page 416
Grouping and Outlining......Page 417
Creating Pivot Tables and Pivot Charts......Page 426
14 Creating Workbooks for Other Users......Page 456
Using and Creating Templates......Page 457
Protecting Workbooks, Worksheets, and Ranges......Page 464
Creating Worksheets for Nonusers......Page 471
15 Creating Forms in Excel 2002......Page 478
Creating Printable Forms......Page 479
Understanding ActiveX and Form Controls......Page 482
Adding Form Controls to Worksheets......Page 487
PART V: OUT OF THE BOX WITH EXCEL 2002......Page 502
16 Using Excel with Other Databases......Page 504
Using the Template Wizard with Data Tracking......Page 506
Working with Access and Excel......Page 512
Importing Data......Page 520
Retrieving External Data with Microsoft Query......Page 527
3D Pivot Analysis with OLAP......Page 541
17 Importing and Exporting with Excel 2002......Page 552
Converting, Linking, and Embedding......Page 553
Using Excel in Office XP......Page 565
Importing Data from Other Applications......Page 574
18 Using Excel 2002 with the Web......Page 580
Working with Web Folders......Page 581
Publishing Excel Documents on the Web......Page 584
The L’s: Excel, HTML, XML, and MHTML......Page 598
Creating Web Queries......Page 602
Using the Camera......Page 604
Bringing It Full Circle......Page 607
PART VI: ANALYSIS AND COLLABORATION WITH EXCEL 2002......Page 610
19 Solving Business Problems Using the Analysis Tools......Page 612
Creating Data Tables......Page 613
Forecasting and Modeling......Page 616
Using Goal Seek......Page 621
Using Solver......Page 623
Using Scenarios......Page 632
Creating Views......Page 637
Creating Reports......Page 638
20 Using Excel’s Workgroup Tools......Page 640
Working with Workgroups......Page 641
Using SharePoint Team Services......Page 656
Holding Virtual Meetings to Share Excel Documents......Page 667
PART VII: AUTOMATING EXCEL 2002 WITH MACROS AND VBA......Page 670
21 Automating Excel with Macros......Page 672
Creating Macros in Excel......Page 673
Working in the Visual Basic IDE......Page 680
Making Macros Easy to Use......Page 685
22 Using Visual Basic with Excel......Page 698
What Is Visual Basic?......Page 700
Making Use of Visual Basic......Page 704
The Big Picture......Page 705
Hello World!......Page 707
Going Further with Visual Basic......Page 721
23 Do-It-Yourself Functions......Page 724
Creating User-Defined Functions......Page 725
Using Custom Functions......Page 731
Testing and Debugging In-Depth......Page 735
Windows API Functions......Page 743
24 Creating an Excel Application......Page 744
Getting the Help You Need......Page 745
Integrating Excel with the Other Office Applications......Page 753
An Example: Retirement Countdown Application......Page 758
Creating the Retirement Countdown Form......Page 760
Creating the Code......Page 766
Life after Retirement Countdown......Page 792
PART VIII: APPENDICES......Page 794
A: Customizing Excel 2002......Page 796
Customizing Excel Options in the Options Dialog Box......Page 797
Recovering Files Manually in Excel 2002......Page 815
B: VBA Quick Reference......Page 820
Using Comments, Blank Lines, and Indentation......Page 821
Understanding VBA Syntax......Page 822
VBA Operators......Page 835
VBA Programming Structures......Page 837
The Excel Object Model......Page 848
Working with Excel Events......Page 857
C: Using MapPoint 2002 with Excel 2002......Page 880
MapPoint 2002......Page 882
D: Creating Simple Smart Tags for Excel 2002......Page 888
Tools for Creating Smart Tags......Page 889
Creating the List File in Notepad......Page 891
Creating the File Using SmartTagMOSTLGenerator......Page 895
A......Page 897
C......Page 899
D......Page 902
E......Page 905
F......Page 906
G......Page 909
H......Page 910
I......Page 911
L......Page 912
M......Page 913
O......Page 915
P......Page 917
R......Page 919
S......Page 920
T......Page 923
V......Page 925
W......Page 927
Z......Page 929