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



Welcome to Special Edition Using Microsoft Excel 97, Bestseller Edition!

This book is the most comprehensive and useful reference available for Excel 97, which is part of Microsoft's Office 97 suite of productivity applications. A team of the best Excel authors available explains to you how to make the fullest use of Excel 97, how to take advantage of its extensive features, and how to put it to work immediately. Through the use of explanations, demonstrations, examples, and a variety of files found on the accompanying CD-ROM, Special Edition Using Microsoft Excel 97, Bestseller Edition is one-stop shopping for getting the most out of Excel 97.

This book is designed for people who are already familiar with Excel. However, if you're brand-new to Excel, don't worry—Chapter 1 contains Excel basics and is designed to get you up to speed quickly and easily.

New Excel 97 Features

Excel 97 is a substantial upgrade from Excel 95. Many features requested by users through Microsoft's wish line (1-800-226-WISH) have been incorporated into Excel, along with some features that support Microsoft's strategy of focusing on the Internet. The following sections summarize the most valuable new features in Excel 97. If you're upgrading to Excel 97 from Excel 95, reading the following sections will help you quickly master these new capabilities.

Increased Capacity

Spreadsheet programs only give you a certain number of rows and columns within which to work. Although you can extend these limits by linking worksheets together in creative ways, there are times when you simply need to be able to store more information in a worksheet. With computers gaining more and more memory, there's no real hardware limitation that keeps you from having just about as many rows of information as you need.

Earlier versions of Excel limited you to 16,384 rows of data. This is quite a lot by anyone's standard, but Excel 97 increases the limit to 65,535 rows. You can also store up to 32,767 characters within a single Excel cell, whereas earlier versions of Excel limited you to 255 characters in a cell.

Another limit that's increased in Excel 97 is the number of data points that can be included in a chart. Excel 97 ups the number of data points from 4,000 to 32,000.

Multiple Level Undo

One thing has been frustrating Excel users for quite a while now: the single-level Undo command. Although Word has had the capability to reverse multiple actions for quite some time, until now Excel only let you undo the most recent action you took when working in your worksheets. If you made a mistake and then performed another action, you could not go back and undo the earlier mistake. Excel 97 now includes this invaluable feature, called multiple-level undo, so you can undo your most recent 16 actions. If you make a mistake and then perform another action, you can still go back and reverse your changes.

Formula AutoCorrect

Microsoft usability testing indicated that people frequently make common mistakes when entering formulas. For example, a user might leave off a closing parenthesis, or type two operators—like two plus signs—together by mistake. In previous versions of Excel, you just got an error message if you made such a typo, and you had to fix it manually. In Excel 97, however, the 15 most common formula entry mistakes are corrected automatically for you. And, if Excel 97 isn't sure how to correct an error, it will pop up a dialog box offering to make the correction for you and suggesting a corrected version.

Shared Workbooks

Many people rely on Excel to enhance their ability to work within a team. For example, most accounting departments couldn't get along without Excel. Any time you have a team of people constantly using Excel, it's natural to want to share files and collaborate on projects. In Excel 95, a feature called shared lists was introduced, which let several people edit data in worksheets simultaneously. Excel 97 includes improvements to this feature—now called shared workbooks—that make it even easier to let multiple people work on a single workbook at the same time. Changes made to each person's copy are now automatically consolidated together, and each person's view and print settings are now maintained for them.

Natural Language Formulas

Consider the sample worksheet shown in Figure I.1. In particular, notice that the formula shown for cell C21 in the formula bar is =C14+C19. How quickly can you check to see if the correct cells are being used in that formula? Not very!

Figure I.1. How easy is it to check that the right cells are being referenced?

Excel now recognizes something called natural language formulas. Using the labels associated with cells, you can enter formulas using plain English. For example, look at the formula bar in Figure I.2.

It was possible to enter formulas using these types of references in previous versions of Excel, but only after assigning names to the various cells manually. Excel 97 now simply understands what you mean! And, if you still want to manually assign cell names, you can do that, too.

Data Validation

It's very important that the information you and others enter in a shared workbook be correct. One way to assure that information isn't entered in error is to develop some method that checks the contents of specific cells in such a way that any obviously-wrong entries are immediately pointed out for the person entering the data.

Figure I.2. Using natural language formulas makes your worksheets much more readable, and therefore more accurate.

Excel 97 makes this validation process much easier with a new Data Validation dialog box that lets you set up rules for cell entries. For each cell you can specify a message that reminds the user what data is required. An error message is displayed if the data entered doesn't conform to the rules you define. For example, Figure I.3 shows the Data Validation dialog box with a sample Input Message defined.

Figure I.3. One of the features of the Data Validation dialog box is that you can define messages that tell the user what information is required.

Track Changes

For quite some time Word has had the capability to track changes made by various people editing a document. Each change was highlighted and the person's name was attached to the change so others could quickly and easily see what was new in the document. You now have similar capabilities with Excel 97. The new Track Changes command makes it much easier to have multiple people revising a document together, without losing track of who makes which change.


Learn about Track Changes in Chapter 23, "Collaborating with Excel."

AutoCorrect and Spell Checking

Another feature added to Excel 97 that has been available in Word for a while is AutoCorrect. When you enter data into cells, Excel 97 now automatically corrects common spelling errors instantly. Excel 97 also now includes the capability to spell-check your worksheets, which makes turning out professional-quality work easier than ever.

Collapse/Expand Dialog Boxes

If you've used Excel 95, you probably appreciated the capability to directly select cells from within dialog boxes fields. Click a field in the dialog box, then click a cell or range of cells in your worksheet and the cell reference is automatically entered into the dialog box. However, in many cases the dialog box was still in your way as you tried to select cells! In Excel 97, any dialog box that lets you perform direct selection now includes a Collapse/Expand button at the end of the field, as shown in Figure I.4.

Figure I.4. Click the Collapse/Expand button to shrink the dialog box out of your way, then click it again to restore it once you've selected the cells.

Range Finder

Making Excel 97 even easier to use than before was a major design goal for Microsoft. One of the neat new features that makes it easier to see what you're doing is called Range Finder. When you edit a formula in a cell, the parts of the formula that reference other cells are highlighted with different colors, and the same colors are used to highlight the actual cells that are being referenced. This makes it much easier to see if your formulas are correct, as shown in Figure I.5.


When you attach notes to cells, you used to have to activate a dialog box to see what the note said. Now, with Excel 97, you can review annotations in cells much faster than before! In Figure I.6, you can see how this new reviewing feature, called CellTips, appears on-screen. You just place your mouse pointer over a cell with a comment for a moment, and the CellTip appears.


If you've used Excel before, you've probably had situations where you needed a quick sum of a series of numbers, or perhaps a quick count of how many numbers there are in a range. In the past, you would have to enter a formula into an empty cell in order to get the quick answer you wanted, such as =COUNT(B2:B255). Now, with AutoCalculate, you can get quick answers much faster. In Figure I.7, you see AutoCalculate in action. Simply select a range of cells, and the sum appears in the status bar. You can also right-click the box with the sum to choose from other popular quick calculations.

Figure I.5. Range Finder lets you easily see what cells are being referenced by a formula.

Figure I.6. CellTips make it faster to review worksheet comments.

Figure I.7. AutoCalculate gives you quick answers to your questions!

Improved Page Break Management

Handling page breaks in Excel has long been a sore subject with users. Before Excel 97, you really had to do backflips to get your worksheet page breaks to occur exactly where you wanted them. With Excel 97, however, you can now preview exactly where the page breaks will occur, and you can easily drag and drop them to get just the effect you need.


You can learn how to use Excel's new page break features in Chapter 5, "Printing Worksheets."

Conditional Formatting

Another new improvement to Excel 97 surrounds conditional formatting, where you can set rules that determine how a cell is formatted. You can, for example, set rules that say that if one cell is less than some number in another cell, that it will be displayed with a particular format. And you can actually do much more than this. Figure I.8 shows the new Conditional Formatting dialog box, with a simple conditional formatting rule applied.

Figure I.8. Conditional formatting lets you format your worksheet cells based on their results.


Chapter 4, "Formatting Your Worksheets," shows you how to use conditional formatting.

Formula Palette

Entering formulas into worksheets is now easier with the Formula Palette, an easy-to-use tool that walks you through the process of using functions. In Figure I.9, you can see the Formula Palette with the PMT function. For each argument of the PMT function, you can see a quick answer to the right of the argument field, and you get a quick answer displayed near the bottom of the Formula Palette.

Figure I.9. The Formula Palette is an easier way of entering and editing formulas and functions.


Learn the ins and outs of using the Formula Palette in Chapter 6, "Using Excel Functions."

Improved Database Queries

In corporate environments, people often use Excel to query databases and return the results to a worksheet, after which they perform their analyses or reports on the data with Excel. Excel 97 improves this important function in a variety of ways. First, a new Query Wizard makes creating basic queries easier than using the full-fledged Microsoft Query tool (which is still available if you need it). Second, queries now take up less memory on the system (running out of memory during queries used to be a big problem). Third, queries can run in the background, so you can continue to work with your worksheet as you wait for the query data to be returned.


Learn about the Query Wizard in Chapter 16, "Retrieving Data with the Query Wizard." Learn about the more advanced Microsoft Query in Chapter 17, "Advanced Queries Using Microsoft Query."

Better PivotTables

PivotTables were introduced in Excel 95 and quickly became one of people's favorite features. They let you easily analyze lists of data and extract answers in different ways. Excel 97's PivotTables have been extensively improved, including the following:

  • Persistent Formatting lets you format PivotTables, and retains their formatting when you refresh their data or rearrange them.

  • Automatic Sorting keeps your data sorted as you want it, even after refreshing the PivotTable data.

  • Reduced Memory Requirements let PivotTables do more work before exhausting your computer's memory resources.

  • Smart Page Fields let you only retrieve the data from an external database related to a particular page field. Choosing a different page field then retrieves the new data. This lets PivotTables remain useful with very large external databases.

  • A Special PivotTable Selection feature lets you choose parts of the PivotTable for formatting or for use in other formulas.

These are only a few of the new features and improvements made to PivotTables. As you'll learn in Chapter 20, "Analyzing Data: PivotTables!," PivotTables are better than ever in Excel 97.


PivotTables are covered extensively in Chapter 20, "Analyzing Data: PivotTables!"

Improved Charts

If one area of Excel really didn't need much improvement, it was Excel's charting functions. Just the same, Microsoft managed to both increase Excel's charting capabilities and make them easier to use at the same time (quite an accomplishment!). Look for these new features in Excel 97's charting features:

  • An improved Chart Wizard makes choosing just the options you want in your charts quick and easy.

  • New chart types are available in Excel 97. Look for Pie of Pie, Bar of Pie, Bubble, and new 3-D charts in Chapter 12, "A Field Guide to Excel Chart Types."

  • You can have up to 32,000 data points in 2-D charts now!

  • Data Tables in charts let you easily add a table that shows the tabular data on which the chart is based on the chart itself. Before, you had to embed a chart on a worksheet and create the data table below the chart manually.

  • Time Scale Axes in charts automatically display a date axis in chronological order, even if the underlying worksheet range isn't ordered that way. Time Scale Axes also let you easily change the base unit of time shown, so you can quickly switch between showing days, weeks, months, and so forth.

  • Chart Tips let you easily see which data series corresponds to which range on the underlying worksheet.

These are just the most notable improvements to Excel 97's charting features, but as you can see they're extensive and increase the power of this important Excel tool.


Learn about all of Excel's charting features—both new and old—in Part III, "Creating Charts and Graphics."

Office Assistant

A new help tool in all of the Office 97 applications is the Office Assistant, shown in Figure I.10. This tool is an animated assistant that "watches over your shoulder" as you use Excel and lets you enter questions using plain English and get help and advice on using Excel. You can choose from a variety of Office Assistant characters, each one providing a different personality and level of animation. You should definitely try the Office Assistant, and even if you don't like to leave it on your screen all the time, you'll find that you can call it up anytime and get quick answers to your Excel questions.

Figure I.10. The Office Assistant is a powerful new help tool in the Office 97 suite of applications.

How This Book Is Organized

Special Edition Using Microsoft Excel 97, Bestseller Edition is organized into sections to make it easier for you to find the information you need. Each section deals with a different area of Excel.

Part I: Using Excel Worksheets

The first part in the book deals with working with worksheets, the fundamental tool in Excel. Chapter 1 begins with basic information for people new to Excel, and then the section (and the book) assumes you have some experience with Excel. In this section, you learn about how to get further help for Excel, how to manage Excel workbooks, worksheets, and windows, how to enter and edit data in worksheets, how to format worksheets, and how to print worksheets.

Part II: Advanced Excel Worksheet Features

The second part extends your knowledge of Excel worksheets. Here, you learn about using Excel's powerful built-in functions, its array of add-ins that extend Excel's utility, how to customize Excel to your preferences, and about a number of sample worksheet solutions that can solve a variety of problems or can just serve as a starting point for you to develop your own solutions with Excel.

Part III: Creating Charts and Graphics

You're not done with your worksheets until you communicate their results to someone. You can make communicating important data or ideas much easier through the use of Excel charts. Part III covers everything there is to know about creating charts. You learn about creating charts, about advanced chart features, and about all the different chart types built-into Excel. You also learn about using Excel's built-in drawing tools to annotate charts and worksheets.

Part IV: Creating and Using Excel Databases

Excel is often used as an analysis tool, but it's also a rudimentary data management tool. In Part IV you learn how to build databases using Excel 97. You also learn about accessing and querying data from other sources, such as Access, corporate databases, and the Internet.

Part V: Analyzing Your Data

Since Excel is most-used as an analysis tool, you would expect it to have strong features that make analyzing data easier and faster. In this section you learn how to outline worksheets, work with multiple scenarios, audit and validate worksheets, use Excel's Solver, use Excel's PivotTables, and how to make the best use of the Analysis ToolPak.

Part VI: Networking and Integration with Excel

"No program is an island," to paraphrase a famous quotation, and Excel 97 includes features that make it easier to work with other people using Excel, interoperate with other programs, and work seamlessly with the Internet. In this part, you learn how to do all of these things with Excel.

Part VII: Programming Excel

One of the best parts of Excel is its programmability. You can use Excel's built-in programming language, Visual Basic for Applications (VBA), to make Excel do many things. You can create programs that range from automating routine chores in Excel, to building sophisticated applications in Excel. Part VII shows you what you need to know to get up to speed and gain some mastery of VBA in Excel.

Conventions Used in This Book

Que has over a decade of experience writing and developing the most successful computer books available. With that experience, we've learned what special features help readers the most. Look for these special features throughout the book to enhance your learning experience.

Chapter Roadmaps

As a sidebar on the first page of each chapter there is a list of topics to be covered in the chapter. This list serves as a roadmap to the chapter so you can tell at a glance what is covered. It also provides a useful outline of the key topics you'll be reading about.


Notes present interesting or useful information that isn't necessarily essential to the discussion. This secondary track of information enhances your understanding of Windows, but you can safely skip notes and not be in danger of missing crucial information. Notes look like this:


Ctrl+Esc is a shortcut key combination used to access the Start menu. Throughout this book, when you see a key+key combination, that signifies a shortcut to accessing an application or opening a menu.


Tips present short advice on quick or often overlooked procedures. These include shortcuts that save you time. A tip looks like this:


Nearly every item in Windows 95 contains a property sheet you can customize. Right-click an item and choose Properties to see its property sheet.


Cautions serve to warn you about potential problems that a procedure may cause, unexpected results, and mistakes to avoid.


If you have a similar printer that could use the same drivers, do not remove the software. Deleting the associated software might remove that driver from use by other printers.

Common Problems and Solutions

No matter how carefully you follow the steps in the book, you eventually come across something that just doesn't work the way you think it should. These troubleshooting sections anticipate common errors or hidden pitfalls and present solutions. Each chapter ends with a list of common problems.

Cross References

Throughout the book, you see references to other sections and pages in the book, like the one that follows this paragraph. These cross references point you to related topics and discussions in other parts of the book.

See Starting and Exiting Excel

In addition to these special features, there are several conventions used in this book to make it easier to read and understand. These conventions include the following.

Underlined Hot Keys, or Mnemonics

Hot keys in this book appear underlined, like they appear on-screen. In Windows, many menus, commands, buttons, and other options have these hot keys. To use a hot-key shortcut, press Alt and the key for the underlined character. For instance, to choose the Properties button, press Alt and then R.

Shortcut Key Combinations

In this book, shortcut key combinations are joined with plus signs (+). For example, Ctrl+V means hold down the Ctrl key, while you press the V key.

Menu Commands

Instructions for choosing menu commands have this form:

Choose File, New.

This example means open the File menu and select New, which in this case opens a new file.

Instructions involving the new Windows 95 Start menu are an exception. When you are to choose something through this menu, the form is

Open the Start menu and choose Programs, Accessories, WordPad.

In this case, you open the WordPad word processing accessory. Notice that in the Start menu you simply drag the mouse pointer and point at the option or command you want to choose (even through a whole series of submenus); you don't need to click anything.

This book also has the following typeface enhancements to indicate special text, as indicated in the following table.

ItalicItalics are used to indicate terms and variables in commands or addresses.
BoldfaceBold is used to indicate text you type, and Internet addresses and other locators in the online world.
Computer typeThis command is used for on-screen messages and commands (such as DOS copy or UNIX commands).
MYFILE.DOCFile names and directories are set in all caps to distinguish them from regular text, as in MYFILE.DOC.

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