MasteringTM Microsoft® Excel 2002

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 productivity seekers looking to get the most out of the program. Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file. 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