• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL



In this introduction

New Features and Enhancements in Excel 2002

How This Book Is Organized

Conventions Used in This Book

Just as Microsoft Excel 2002 is the most comprehensive and powerful tool for creating spreadsheets, this book, Special Edition Using Microsoft Excel 2002, is the most comprehensive and powerful reference of its kind.

This book represents decades of experience using and developing spreadsheet solutions with Excel.

Whether you'll use this book as a reference to solve problems and research new features or you plan to read it from cover to cover, this book is designed for people who've used Excel before. This book doesn't exclude the new or self-taught user, however, the first chapter has been designed to provide you with a great foundation in spreadsheet concepts and the basics of Excel.

If you're in a bookstore, give this book the flip test against the competitors. You'll find that this book contains more sophisticated and real-world examples than any other Excel book on the market today. This is due to my years of experience using Microsoft Excel to develop and deploy solutions for some of the most successful and respected companies in America.

I know you'll find this book an indispensable resource on Excel.

New Features and Enhancements in Excel 2002

There are a variety of new and enhanced features in Excel 2002. Some are the result of Office XP changes and additions, and others are found solely in Excel.

Enhancements and new features you'll find in Excel fall into the following categories:

  • The new worksheet task pane allows you to do several things from one pane. From retrieving last-used workbooks to creating and storing Web templates that refresh on open.

  • By right-clicking sheet tabs, you can access the tab color option. Choose a color from the template and the sheets tab is colored with the selected color.

  • Access to Enterprise Data enables you to create OLAP PivotTables, and use Excel's OLE DB, ActiveX Data Objects (ADO), DAO Data Access Objects, and data access technologies in an SQL Server environment.

  • When a file becomes corrupt or crashes, the files are analyzed for errors and all recoverable documentation is saved with Excel's new Document Recovery feature.

  • The new encryption feature allows you to select a CryptoAPI-based encryption type with which you can create passwords up to 255 characters in length. This provides an ultrasecure workbook.

  • The new PivotTable task pane takes the place of the PivotTable toolbar found in Excel 2000. Although the PivotTable toolbar allowed for simple drag and drop to drop zones, you could never see the full title of the field. With the task pane, you can stretch the pane horizontally to allow for full viewing of the Field.

  • Excel 2002 has embedded LH's Lernout & Hauspie intelligent speech agent that recognizes text to speech. This enables Excel 2002 to read your numbers back to you. Excel 2002 also has voice commands and dictation that enables you to dictate messages and select menus, toolbars, and dialog box options simply by speaking. However, this technology is still in its infancy and is somewhat laborious to use.

  • You can now edit spreadsheets in any language with Excel's new Multilingual editing, including Right to Left languages such as Arabic and Hebrew.

  • The new Smart Tags are buttons that appear on your worksheet and allow you to perform additional operations with the data that was entered. They allow you to add names to your contacts in Outlook, send mail, and retrieve additional information from the Web. Other Smart Tag buttons that appear are AutoCorrect, Paste, AutoFill, Error Checking Options, and Insert Options.

  • The new Clipboard viewer is similar to the Excel 2002 Clipboard, except that the viewer is in the form of the task pane. The Clipboard allows you to copy and paste items, whereas the Clipboard viewer maintains the items within its window so you can see and select the items at will.

  • Excel 2002 allows you to create or run a Web query and populate an Excel worksheet with data from a Web source. Pictures are not included, only tables defined with the HTML tags. You start the query by following the step by step with the Query Wizard. The available queries on the Web page show up as yellow tags with arrows. If there are no yellow tags, click Show Icons at the top of the dialog box and the Icons will be displayed. Select the tag and a check appears meaning this data will be included in your query to Excel.

How This Book Is Organized

Special Edition Using Microsoft Excel 2002 is divided into logically ordered and carefully divided sections. This makes it easier for you to find the topics you need, and ensures that the book flows from basic to advanced topics in a manner that enables you to read the book from start to finish, effectively building your Excel skills.

Part I: Getting Started with Excel 2002

Chapter 1, “Getting Around Excel,” Chapter 2, “Spreadsheet Basics,” and Chapter 3, “Editing Spreadsheets,” take you around some of Excel's new features and give you basic fundamentals to get around and manipulate the program on its basic levels. From adding and formatting worksheets to a workbook to using the new worksheet task pane, these chapters give you the basics. You'll also learn how to use the new Smart Tags that appear on your spreadsheet based on certain commands performed.

Part II: Formatting and Printing Excel Worksheets

In Chapter 4, “Applying Number and Date Formats,” you'll learn all the basics from applying number and date formats to creating your own custom number and date formats. Chapter 5, “Formatting and Printing,” takes you into the use of spreadsheets with drawing tools. From standard layouts to formatting cells, fonts, and styles, this is where you'll find it. After you learn these basics, you'll also learn the ins and outs of printing all elements in a spreadsheet.

Part III: Using Formulas and Functions

Chapters 6 through 16 take you through just about all the functions Excel offers. Starting off with Chapter 6, “Function Fundamentals,” and moving into all the function categories by chapter including Chapter 7, “Database Functions,” Chapter 8, “Date and Time Functions,” Chapter 9, “Engineering Functions,” Chapter 10, “Financial Functions,” Chapter 11, “Information Functions,” Chapter 12, “Logical Functions,” Chapter 13, “Lookup and Reference Functions,” Chapter 14, “Math and Trigonometry Functions,” Chapter 15, “Text Functions,” and Chapter 16, “Statistical Functions.” With tons of examples, you should find just about every solution you'll need to solve problems in any business.

Part IV: Using Excel's Analysis Tools

Chapter 17, “Setting Up and Establishing a List or Database,” Chapter 18, “Using Excel's Data-Management Features,” and Chapter 19, “Outlining, Subtotaling, and Auditing Worksheet Data,” focus on Excel's data analysis and data-management features. Learn how to build a database or list; edit, sort, and filter the list; and use advanced filters to manipulate just about any list or database. Use Form Controls in conjunction with functions learned in previous chapters to automate and create custom form bid sheet models.

Part V: Creating and Modifying Charts

Chapters 20 through 23 take you on a comprehensive tour of Excel's considerable charting tools. From building a simple bar or pie chart to stacking multiple charts, you'll learn which chart type best depicts your data (Chapter 20, “Building Charts with Excel”) and how to manipulate its appearance and content to express your numeric data effectively (Chapter 21, “Modifying Excel Charts” and Chapter 22, “Formatting Charts”). Learn professional techniques for making your charts stand out visually as well as in terms of their content, communicating complex data in a dynamic visual format. Also learn how to tie form controls such as drop-downs and radio buttons into your charts for automatic data manipulation (Chapter 23, “Professional Charting Techniques”).

Part VI: Using Excel to Manage Your Business

In Chapters 24 through 27, you learn how to build and manage a database with PivotTables. Take advantage of Excel's new PivotTable Task Pane and improved PivotChart tools for data analysis to support your business decisions (Chapter 24, “Using PivotTables and PivotCharts”). Use Goal Seek, Solver, and Analysis ToolPak tools to solve simple or complex business and resource-loading problems (Chapter 25, “Using Analysis Tools: Goal Seek, Solver, and Data Tables”). Create cascading schedules and time-management tools found only in this book (Chapter 26, “Using Excel in Business”). Learn how to create custom functions that have relevance only to your business (Chapter 27, “Customizing Excel to Fit Your Working Style”).

Part VII: Integrating Excel with Other Applications

Chapters 28 through 32 show you how to end your isolation and branch out—using Excel data in your PowerPoint presentations and Access tables. Chapter 28, “Building Presentations with Excel,” provides the fundamentals for porting data over into other office applications for presentations or linked information. Chapter 29, “Using Excel with Access and Other Databases,” focuses extensively on Excel's database access capabilities and on retrieving data from the Web. Learn how to build database queries and retrieve information from Access and from other databases. Chapter 30, “Retrieving Data from OLAP Servers,” introduces you to OLAP PivotTables and data stores, how to create them, and how to use them. Use Excel's Cube Wizard to build and use cubes for quick data access and analysis offline. Chapter 31, “Recording and Editing a Macro,” introduces you to recording and editing macros and Chapter 32, “Using Excel on the Web,” shows you how to take advantage of new Web query options that allow you to manage your stock portfolios in Excel with simple refresh commands.

Web Content

Most readers of this book will be content to use Excel exactly as it comes out of the box. Therefore, many chapters deal only with customization that can be done simply by making choices in dialog boxes. But for those using Excel as a development environment, we have a special introduction chapter for going beyond the basic macro recordings using VBA. You can find this and other valuable information at http://www.quehelp.com.

Conventions Used in This Book

The special conventions used throughout this book are designed to help you get the most from the book as well as Excel 2002.

Text Conventions

Different typefaces are used to convey various things throughout the book. They include the following:

Typeface Description
Monospace Screen messages and Internet addresses appear in this special typeface.
Italic New terminology and emphasized text will appear in italic.
Bold monospace References to text you should type will appear in bold, monospace font.
Initial Caps Menu names, dialog box names, and dialog box elements.

In this book, key combinations are represented with a plus sign. If the action you need to take is to press the Ctrl key and the S key simultaneously, the text tells you to press Ctrl+S.

Special Elements

Throughout this book, you'll find Tips, Notes, Cautions, Cross-References, and Troubleshooting Tips. These elements provide a variety of information, ranging from warnings you shouldn't miss to ancillary information that will enrich your Office experience but isn't required reading.

“Signature” Tips

Tip from

Tips point out special features, quirks, or software tricks that will help you increase your productivity with Excel 2002.



Notes contain extra information or alternative techniques for performing tasks that we feel will enhance your use or understanding of the current topic.



If there is a potential problem with a feature or something you should be aware of to avoid errors or unwanted results, you'll find both a description of the situation and how to resolve or avoid it in the Caution format.


At the end of most chapters, you'll encounter a “Troubleshooting” section. This is where you learn how to solve or avoid common problems you might typically face with Excel 2002.

Excel in Practice

At the end of many chapters, you'll find an example of how to use that chapter's features to improve the overall functioning, legibility, and effectiveness of your worksheets. Often appearing in the form of before-and-after figures with explanatory callouts, these samples offer advice and practical examples for your own implementation.


Cross-references direct you to other locations in this book that provide supplemental or supporting information. They look like this:

→ For information about configuring Outlook so the Outlook Today window appears each time you start Outlook, see “Specifying Other Options,” p.XXX

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint