|Home | Polski|
Reprinted from Byte 13/1989, pp. 136-138.
New Extras for Excel
Microsoft has given the latest release of its Excel spreadsheet the simple but evocative name “version 3.0.” With such a name, you might wonder if the new Excel 3.0 will bask in the golden glow of Windows 3.0.
Some of Excel 3.0’s capabilities set it apart from all its competitors. These include a new outlining feature, borrowed from the word processing world, that works surprisingly well. Excel also incorporates Microsoft’s Object Linking and Embedding (OLE) technology and supports the direct manipulation of objects.
Ease of Use
Despite the addition of several new features to Excel 3.0, Microsoft says that its primary design goal was to make the spreadsheet easier to use. A “ribbon” tool bar across the top of the screen, like the one in Word for Windows, offers shortcuts to common procedures such as cell formatting, outlining, and charting. Many actions have been made more intuitive by Microsoft’s applying the rule that the mouse double-click is a gateway to more detail. And features like “best-guess” summing and user-definable styles and templates make building a model faster and easier.
Perhaps the most interesting of these features is Autosum, represented on the ribbon by an icon labeled with a Greek sigma. If you create a series of cells, highlight the next empty cell in the row or column, and then select Autosum, Excel will propose a formula that adds the cells in the series. If Excel guesses correctly, you can accept the formula with a mouse-click; if not, you can quickly edit it. This is a much-needed shortcut for the most commonly used function in a spreadsheet.
Other icons let you call up drawing tools for adding lines, rectangles, and circles directly onto a worksheet or for creating a quick chart (by choosing a range of cells and letting Excel make a smart guess at the graph).
To lure dyed-in-the-wool Lotus 1-2-3 users to Excel, Microsoft includes a remarkable help system just for Lotus loyalists. By using the familiar slash key, 1-2-3 users can access the Help menu, which is available in two modes. In the demonstration mode, you enter 1-2-3 keystrokes (the Lotus menu tree appears in the Help dialog box), and Excel translates them into its own commands and carries them out for you while you watch. In instruction mode, the Excel equivalents of 1-2-3 sequences are listed in a brief summary note, which you can tear off and stick on your worksheet, just like a yellow Post-it note.
Whether your worksheet contains a bill of materials or a balance sheet, chances are you have regions of cells that are subordinate to others; for example, months totaling into quarters or territories adding up to regions. Excel 3.0 lets you structure this information in an outline form so that you can choose the level of detail in which you wish to view your data (see the photo).
Up to eight layers of nested outlines are supported on both the horizontal and vertical axes. You can move from one level to another by clicking plus or minus icons (which can also be hidden) on the edge of the sheet. Or you can jump instantly from a fully exploded view that would, for example, display every row in your income statement to a completely collapsed view that would show only your company’s bottom line.
Amazingly enough, you don’t have to develop an outline from scratch: Excel can construct an outline automatically by analyzing the worksheet and looking for regions of cells that are added together. Even a Lotus 1-2-3 worksheet can be imported and restructured into outline form.
Making the Link
The most important innovation of Lotus 1-2-3 release 3.0 was its introduction of multipage 3-D spreadsheets, which are especially useful for consolidating similarly structured sheets into a master sheet. Microsoft has declined to add the same capability to Excel; instead, worksheets are maintained as separate files, and you can link cells by including a source filename and cell address in a formula.
To simplify the process of consolidating data, Excel 3.0 supports “name-based linking,” in which connections are made not by cell address but by the name of a range. Therefore, you can link to a master sheet all references to “research expenses” in subsidiary sheets. Microsoft says that the direct-mapped linking of 1-2-3 can lead to mistakes if, for example, a cell used in a formula is deleted but the formula is not rewritten. Name-based linking can be more time-consuming to set up, but it is insensitive to the physical structure of source worksheets.
Excel 3.0 also supports a variety of external links. Through the clipboard, you can import text and graphics from other applications on a one-time basis. Using the Paste Link function, you can set up a dynamic or “hot” link to an external object, and any changes made to it in the source application will be reflected in Excel. And double-clicking on an object imported from a program that supports Microsoft’s new OLE technology will bring up the program that created it.
To help connect spreadsheets to the world of servers and databases, Microsoft will bundle with Excel 3.0 a copy of Q+E, a query and edit utility developed by Pioneer Software. Q+E lets you use Excel as a front end to several databases, including Microsoft’s SQL Server, Oracle, dBASE, and IBM’s OS/2 Extended Edition.
Because of its graphical environment, Excel has always had strong presentation capabilities. Version 3.0 adds only a few functions, including support for 3-D charts, more colors and patterns, and picture charts. The gallery of graphics options includes 3-D column, area, pie, and line graphs, all of which you can adjust for rotation, vertical viewing angle, and perspective.
Picture charts are graphs in which traditional bars or lines are replaced with images that you supply. Thus, for example, you could illustrate a chart of bakery sales with stacks of doughnuts.
Solve Me a Riddle
The Solver and Backsolver in 1-2-3/G generated much excitement and were quickly copied by Lotus’s competitors. Excel 3.0 is the first Windows spreadsheet to include these capabilities. You can use Excel’s Solver, which is actually a separate application supplied with the spreadsheet, for single- or multiple-variable goal-seeking. For example, you can maximize the value of one cell by changing the value of another, or you can jiggle a complex production plan with many variables and constraints to arrive at optimum profitability.
If you’re a real power user, Microsoft’s Solver offers a lot of flexibility and options. You can limit the amount of time or number of iterations used to achieve a solution, specify a level of precision, or constrain the Solver to a linear model. You can also customize the methods that the Solver uses, choosing tangential or quadradic extrapolation, forward or central differencing, and quasi-Newton or conjugate gradient searching.
By combining the Solver with graphics and Dynamic Data Exchange, Excel 3.0 lets you use a chart as a scratchpad for modeling. This feature is called Dragging Data Points: You create a chart, and then, by simply dragging graph points with the mouse, you can automatically change the numbers in the original spreadsheet that created it. If the point that you want to change is dependent on a formula, the Solver pops up automatically and asks you which variable you want to adjust.
Many Products, One Vision
For the last several years, Microsoft has been steadily working to unify all its graphical software applications (whether intended for use on the Mac, Windows, or OS/2 Presentation Manager) so that they all look and behave the same way. The most recent versions of Word, for example, sport almost identical features across all platforms, and even the character-based version 5.5 has a new pulldown menu interface that uses the same commands as its GUI siblings. Besides trying to achieve consistency across many platforms for each application, Microsoft has also made different applications look the same; thus, Excel looks like Word, which looks like PowerPoint. Excel 3.0 is the ultimate realization of this plan. As astonishing as it may seem, most volumes of the documentation are the same, whether for the Mac or an Intel-based platform. Microsoft has succeeded in making the hardware platform irrelevant, which is good news for users in mixed network environments. If you need a spreadsheet with lots of features and performance – and most of all, with unassailable clarity of vision – take a long, hard look at Excel.
Andrew Reinhardt is BYTE’s associate news editor in New York. He can be reached on BIX as “areinhardt.”
|Page added on 22nd September 2004.
Copyright © 2002-2005 Marcin Wichary
|Printable version | Contact | Site map|