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



Each new edition of Microsoft Excel is easier to use than the previous edition. Microsoft Excel 97 is even easier to use than Excel 95 and its menus and toolbars are more compatible with the menus and toolbars of other applications in the Office suite. The most important enhancement to Excel 97 is how it can help you work with others in your workplace and communicate over corporate intranets and the Internet.

This new ability to work in groups, work over the Internet, and gather information could change the nature of how we learn, how we work, and how companies are structured. The printing press was a catalyst of the same magnitude. The breakaway from a dominating church, the development of independent and scientific thought, and the rise of new constructs in philosophy are all linked by historians and philosophers to the explosion of information that came about due to the printing press. The changes we see over the next decade might be as significant.

Reviewing Excel Features

The following sections present Excel's major strengths and capabilities. Features new to Excel 97 are indicated by the version 97 icon in the margin. Excel's new and most powerful features are described in the first chapter, "Spreadsheet Power in Excel 97 for Windows." This chapter also describes features that were new to Excel 95 because many people are upgrading to Excel 97 from Excel 5 and earlier versions.

Web Publishing and Browsing

Excel 97 can publish worksheet data, tables, and charts as Web pages. These Web pages can then be used on your company's intranet or the Internet. You can choose to insert your worksheet selections into existing Web pages or create new Web pages.

You can now insert hyperlinks into Excel cells. When clicked these hyperlinks take you to another document. They can even open programs. Using hyperlinks and the new Web toolbar you can browse through a Web of Office documents or access the World Wide Web and browse through the world of information it contains. When you need to link to frequently updated information on the Web, you can use a Web query to bring data such as stock quotes and exchange rates directly into your worksheet.

Operating Ease

Excel is the easiest worksheet to use, yet it remains the most powerful worksheet on the market. This paradox is possible because of Excel's toolbar and shortcut menus combined with its formatting and advanced analytical features. Microsoft has one of the world's largest software-usability testing laboratories that enables its developers to see how well people use their software.

Microsoft's Office Suite Strategy guarantees that the most frequently used Microsoft applications can work together by sharing data, using common menus and toolbars, and a common user programming language—Visual Basic for Applications. In the Office 97 suite, Excel, Word, PowerPoint, and Access use Visual Basic for Applications as their programming language. Microsoft Project, sold separately, also includes Visual Basic for Applications. After a two- or three-day course, many people who have never programmed before are able to create highly productive programs that run on Excel. After you learn the language in one application, you are well on your way to learning the other languages.

Drag and drop is a concept so beneficial that, when you see it work, you wonder why it didn't become a standard years ago. With drag-and-drop technology, you can select a group of cells and then use the mouse to drag the cells (or a copy of the cells) to a new location even into another worksheet or application. When you release the mouse button, the cells drop onto the cells or document beneath the mouse pointer. You can even drag portions of a spreadsheet onto the desktop and leave them there as scraps for use by other documents.

A concept similar to drag and drop is the fill handle. By dragging the fill handle, you can copy formulas to adjacent cells. The fill handle reduces a multiple-step process to a quick drag with the mouse.

Toolbars are bars of tools (buttons). Each button represents a familiar command. By just clicking a button, you can shortcut many keystrokes. When you use a mouse with the toolbars, you have quick access to the most frequently used commands in a worksheet. Microsoft Excel comes with a predefined set of toolbars, but you can add buttons to, or remove them from, the toolbars and even create toolbars to which you can add buttons that you create.

Shortcut menus appear when you click the right mouse button (in this book, the term right-click is used) on a worksheet or chart item; the most relevant commands appear immediately under the mouse pointer, making the commands you use most frequently immediately accessible.

Data entry is easier in Excel 97. You can still build forms on a worksheet that include pull-down lists, check boxes, and option buttons. The Excel 95 features AutoComplete and Pick Lists make filling in a list easier. AutoComplete examines your typing and completes entries as you type them. You can also right-click a cell in a list and get a scrolling list of all entries made thus far in the column. In earlier versions of Excel you needed to know Excel 4 macros or VBA in order to check data as you entered it. In Excel 97 the new data validation feature lets novices set limits on what data goes into a cell.

Worksheet Publishing

Excel is still the leader in worksheet publishing capabilities. Besides having all the formatting capability of desktop publishing software, Excel includes a built-in spelling checker. By using the spelling checker, you can feel confident that the quality of your analysis isn't compromised by poor spelling. The dictionary for the spell checker is shared by all Office applications.

Layout and worksheet design also are easy in Excel, because Excel includes a zoom feature. Zooming enables you to reduce or magnify the view of the worksheet so that you can see a close-up view to adjust formats or a compressed view to see the big picture. With the new Intellipoint mouse you can zoom in or out of a worksheet by rolling a wheel located between the two mouse buttons. If you frequently view different areas of the document by using different display settings, or print different areas with various print settings, you also may be interested in the View Manager, which enables you to give different names to each view or printing setup.

The templates and cell styles available in Excel can help you if you need to create a frequently used worksheet or a worksheet that presents a standardized appearance. Templates act as master documents that contain worksheet layouts, text, formulas, cell styles, custom menus, and macros. When opened, a template produces a new worksheet that contains everything in the original template. You must save the document to a new name, which preserves the template as a master.

Styles are a powerful feature found in professional-level word processors. With a style, you can name a collection of formatting commands and apply all the formats by selecting this style name. A style named Total, for example, may contain the formats Arial 12 point, bold, right align, currency with two decimal places, and a double-line upper border. Changing the definition of a style changes all cells formatted with this style.

Excel includes AutoFormat, a collection of predefined formatting combinations that you can apply to tables of data. AutoFormat saves you a great deal of time when formatting budgets, forecasts, or lists.

Excel's printing preview capabilities show you how print is positioned on the page. You can zoom in to see the detail of character and drawing positions. While in the preview, you also can drag column and margin markers to reposition columns and change print margins.

Analytical Tools

Although Excel always has been known for offering more analytical tools than other worksheets, new analytical tools in Excel make analysis easier for novice users and expand the upper limits for scientists, engineers, and financial analysts.

Excel has new Wizards that help you do some of the common and frequent analysis jobs. The SumIf Wizard adds data from lists based on conditions you set. The Lookup Wizard will help any novice extract answers from a database without learning how to use the Lookup functions.

Pivot Tables remain one of Excel's most amazing analytical tools. Anyone who needs to analyze a database will appreciate the speed and beauty of Pivot Tables. Excel 97 Pivot Tables can even incorporate formulas you specify.

Some problems must be solved for an optimum solution. For these problems, Excel includes the Solver. The Solver is an add-in program provided with Excel that uses linear and nonlinear programming techniques to find the best solution to a problem.

Excel's hundreds of built-in functions, which are predefined formulas, were expanded with the addition of the Analysis ToolPak. The Analysis ToolPak is another add-in program provided with Excel. If a job requires extensive statistical or financial and investment analysis, install the Analysis ToolPak when you install Excel.

Graphics Features

You can perform many kinds of drawing on Excel worksheets. Excel 97 has made the drawing tools even easier as well as adding many more tools. The new AutoShape tool enables you to draw schematics, flow charts, floor planning—anything where you need to connect and group common shapes or need to have shapes that stay connected. Of course you can still draw lines, arrows, rectangles, ovals, circles, and arcs. You also can draw freehand and then reshape the freehand drawing by dragging lines and corners into new locations. You can create text boxes that you can position anywhere on the page. Not only are there hundreds of colors available, you can mix your own colors or use textures and gradients for fills. The graphics features included are like getting a high-level drawing program with Excel.

You can embed charts or cell pictures in a worksheet. You can take the cell pictures or charts from the same or from a different worksheet. When you change data, the embedded charts or cell pictures update, which enables you to position pieces of worksheet or charts in any arrangement on a worksheet. You can arrange these pieces in the same way that a desktop publisher builds newsletters or annual reports.

With Object Linking and Embedding (OLE), you can embed drawings and graphics from dedicated graphics programs. Embedded graphics are more than images in the work-sheet; these images include the actual data necessary to recreate the graphic. When you double-click an OLE object, Excel's menus and toolbars actually change to the menus and toolbars of the application that created the object. When you click outside the object, the Excel menus and toolbars return.

Linking and Consolidating Features

Excel is flexible enough to adapt to many business situations. Within Excel, you can link worksheets to fit the way you work. You can link cells or ranges of cells between open worksheets or worksheets on disk.

When you need to gather data from multiple divisions or different times to a single worksheet, you can use the 3-D formulas or Excel's consolidation feature. With Excel's 3-D formulas, you can insert many worksheets into one workbook. One 3-D formula can give a consolidated total from all the sheets by spearing through all the cells at the same location in each sheet. You also can use Excel's consolidation feature. Excel can consolidate data from Excel or 1-2-3 worksheets.

Charting Capabilities

Excel has over 100 chart formats from which to choose, but building a chart is extremely simple when you use Excel's ChartWizard. The ChartWizard guides you through the process of building charts. As you select alternatives, you can see the effect of the choices you make. At any time, you can back up and make an alternative selection.

When you format your chart, you can use Excel's AutoFormat feature for charting, which makes it easy to see how a chart will appear before you are finished. You can also create user-defined chart types. After you select a type of chart and the basic format, you can use all of Excel's charting tools. You can drag elements of the chart anywhere in the chart area, orient text sideways, use up to 256 different fonts, and link numbers and text back to worksheet cells. Excel's charting capability rivals the capability of dedicated charting programs. You can even draw on a chart.

By using a chart as the data entry device, Excel even enables you to solve worksheet problems. In line, bar, and column charts, you can drag a chart marker (line symbol or top of bar or column) to a new location. If the marker reflects the result of a formula in a worksheet, Excel asks for the cell that you want to manipulate to accomplish the desired result. This feature provides a way to back into solutions and uses the chart to specify the final answer.

Data Mapping Capabilities

The new Data Mapping feature gives you a geographical view of the data in your database. For example, instead of looking at columns of state abbreviations and sales revenue, you could see it as a color coded map of the United States. And you can buy add-in maps, geographic data, and census data from MapInfo, a leader in computer mapping.

Database Capabilities

A database is like a card-file system that stores information. Because so many worksheet problems involve a collection of historical sales, marketing, engineering, or scientific information, Excel has both built-in database capabilities and the ability to link to external databases of many different formats. Excel's new database features filter information directly in the worksheet, hiding data you don't want to see and displaying information you are interested in.

When you need to work with extremely large databases or databases stored on a mainframe computer or a local area network server, you may want to use Microsoft Query, a program that comes with Excel. Microsoft Query adds commands to Excel that enable you to link worksheets to large databases outside the worksheet.

Worksheet Outlines

Excel contains an outlining feature, which is valuable to anyone who must create extensive reports. The outline enables you to quickly expand and collapse databases and worksheets so that you see only the level of information you need to print or display on-screen.

Outlining enables you to drill down. When you build a summary report by consolidating other worksheets, you have the choice of maintaining links from the source worksheets to the summary. When you double-click a summary number in the consolidation worksheet, Excel drills down and opens the source worksheet.

Visual Basic for Applications and Excel 4 Macro Language

Excel was the first application to include Microsoft's Visual Basic for Applications, based on Visual Basic—the world's most widely used personal computer language. With the release of Office 97 all the applications in the Office suite use Visual Basic for Applications. When you learn one of these languages you learn the Integrated Development Environment, IDE, used by all the applications. The statements, methods, and properties concepts are the same in all the applications. The only thing that differs between the language in each application is the object model. The object model describes the items within an application, for example, some of the objects in Excel are worksheets, ranges, and charts. Those of course must be different from the objects in an application like Word where the objects are word-oriented.

Using This Book

Special Edition Using Microsoft Excel 97 contains eight parts. The following list presents an overview of the parts in this book:

Part I, "Everyday Worksheet Tasks," presents the basics of using Excel, how to enter data and formulas, copy and move contents, organize worksheets within the workbooks, format using Excel's powerful formatting capability, and print the results.

Part II, "Publishing and Browsing on the Web," tells how you can publish worksheet data, tables, and charts in Web pages that can be viewed on your company's intranet or the Intranet. Even if you don't use Web pages, you can enter hyperlinks in cells that take you to other Office documents with a single click. With the Web toolbar built into Excel you'll be able to browse between documents on your local hard disk, network, or the Internet.

Part III, "Creating and Formatting Charts and Maps," shows you how to create, format, and manipulate charts and maps. With charts and maps you can see a visual or geographic presentation of your data and its relationships. The chapters in this section cover topics that range from basic charts and maps, to charts embedded in worksheets, to advanced charting and mapping tricks.

Part IV, "Optimizing Excel," teaches you how to use Excel's graphics and reporting features to present your results with a memorable impact. You will also learn how to use the numerous add-ins that come with Excel and enhance it with special features.

Part V, "Analyzing the Worksheet," explains how to use some of the powerful analytical and reporting capabilities in Excel. You learn how to analyze databases, create data entry forms, solve complex worksheets for the best answer, and more. This section also covers one of the most powerful analysis features available on a personal computer—Pivot Tables.

Part VI, "Managing Lists or Databases," shows you how to create and maintain a list or database. The chapters in this section teach you how to enter, edit, sort, find, and copy information from a database. One chapter explains how to retrieve data from databases outside of Excel by using Microsoft Query.

Part VII, "Using Excel with Office and Workgroups," describes how you can use Excel with others in your workgroup. You'll also learn how to be efficient when using Excel with other Windows and Office applications.

Part VIII, "Customizing and Automating Excel," explains how to customize the toolbars, create buttons on toolbars, and modify the menus. This part also gives you a quick introduction to the fundamentals of Visual Basic for Applications, the programming language used by most of the Office 95 applications.

The Appendix, "Services and Resources," lists some of the most valuable sources for information about Excel 97 and other Office applications. The listings include phone numbers and World Wide Web URLs for technical support, updates, and free software. Make sure you check out these resources.

Conventions Used in This Book

Certain conventions are used in Special Edition Using Microsoft Excel 97 to help you more easily use this book and understand Excel's concepts. The following sections include examples of these conventions to help you distinguish among the different elements.

Special Typefaces and Representations

Special typefaces in Special Edition Using Microsoft Excel 97 include the following:

Type Meaning
italics New terms or phrases when initially defined; function and Visual Basic syntax variables
underline Menu and dialog box options that appear underlined on-screen
boldface Information you are asked to type
special type Direct quotations of words that appear on-screen or in a figure; Visual Basic code

Elements printed in uppercase include names such as SALES, functions such as SUM( ), and cell references such as A1:G20. Also presented in uppercase are file names such as INVOICE.

In most cases, keys are represented as they appear on the keyboard. The arrow keys usually are represented by name (for example, the up-arrow key). The Print Screen key is abbreviated PrtSc; Page Up is PgUp; Insert is Ins; and so on. On your keyboard, these key names may be spelled out or abbreviated differently.

When two keys appear together with a plus sign, such as Shift+Ins, press and hold the first key as you press the second key. When two keys appear together without a plus sign, such as End Home, press and release the first key before you press the second key.

On the Web

This icon and format signal URL addresses for the Internet and World Wide Web of places that have related products or information such as:

Visit the Microsoft home page at:



This paragraph format suggests easier or alternative methods of executing a procedure.


This paragraph format indicates additional information that may help you avoid problems or that should be considered in using the described features.


This paragraph format warns the reader of hazardous procedures (for example, activities that delete files).

Margin Icons

This book uses a special margin icon to indicate new Excel 97 features or tasks you can perform only in the current version. If the icon appears next to the first paragraph in a section, all the information in that section is new.

Additional icons appear in the margin to indicate that the procedure described in the text includes instructions for using the appropriate toolbar buttons in Excel.

Special Sections

Special Edition Using Microsoft Excel 97 includes cross-references to help you access other parts of the book. At the end of major topic sections, related tasks you may need to perform are listed in the margin by section name and page number.

In addition, troubleshooting sections are provided in most chapters to help you find solutions to common problems encountered with the Excel procedures covered in that section of the book.

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