|Home | Polski|
Reprinted from PC Magazine, issue 12/1987, pp. 103-111.
A new face for spreadsheets
Feature for feature, Microsoft Excel for the PC bests Lotus’s 1-2-3 on power and meets it on price – at $495 Will this newcomer push 1-2-3 back to number two?
Microsoft Excel, the new spreadsheet program from Microsoft Corp., could be one of those milestone programs that change the way we use computers. Not only does Excel have a real chance of giving 1-2-3 its most serious competition since Lotus Development Corp. introduced that program in 1982, it could finally give the graphics interface a respectable home in the starched-shirt world of DOS.
Microsoft clearly had its finger on the pulse of PC technology when it developed Excel. The program glitters when run on new, fast, powerful systems. But users who run it on anything less than an 80386 may consider Excel’s reliance on state-of-the-art hardware a liability. Excel will run on an AT but, being a graphics-based program, it’s slower than other spreadsheets. It will work on a regular CGA monitor, but you can’t really appreciate its fine use of color without EGA. And Excel consumes so much RAM, that you’ll need expanded memory to run models bigger than 180 bytes. Excel’s future is really linked to how quickly users become enamored of and ready to invest in the newer, more powerful systems.
For people who cut their teeth on 1-2-3 and have never played with a Mac, Excel looks more like a video game than a serious spreadsheet. It comes with a run-time version of Microsoft Windows, so it has cheery colors, scroll bars, icons, and menu bars. But users will soon discover the beauty of Windows. Since it treats the whole screen as graphics, you can have different spreadsheets and charts in different parts of the screen and you can change nearly everything about the way anything looks.
If you want to draw attention to a number, you can write it in boldface, italics, or large type. You can change its color or font style, underline it, put a shaded background behind it, or draw a border around it. You can make Excel display negative numbers and zeros in special colors. You can adjust column widths and row heights in extremely small degrees. If you like, you can turn off the whole row-and-column grid or paint it any of eight different colors. You can even change the colors of menu bars, window borders, and scroll bars. And if you think all that graphics interface paraphernalia clutters the screen, you can get rid of most of it.
You perform most of this magic by choosing items from Mac-style pull-down menus and dialog boxes. This is the interface the mouse was made for, but you can use Excel without a mouse. There are plenty of shortcut keyboard commands, either to open menus or to go straight to the choice you want. Excel uses all the function keys for this – in combination with both the Shift key and Ctrl-Shift – as well as obscure combinations like Ctrl-Shift-Spacebar. Even veteran mouse users will find some of these combination key commands handy, though they won’t have to learn as many of them.
Excel is stuffed with features. It has 131 built-in functions – 41 more than 1-2-3 – including little gems like fact(), which gives the factorial of a reference, and product(), which multiplies all references by each other. If these 131 aren’t enough, you can design your own functions by writing special macros. In all cases, when you need to enter a function, you can call up a list of them, in alphabetical order, and paste them right into the spreadsheet. You simply click the mouse on the edit menu to bring up the selection of Paste functions available to you. Click on the one you want and the function appears in the edit line of the spreadsheet. This is a handy feature that both reminds you of proper syntax and eliminates typos.
Display formats are just as versatile. Excel gives you 21 standard formats and lets you design more. Telephone format, for example, might add parentheses, spaces, and hyphens, while you entered only numbers. Lira format (or US Budget format) could throw away the last nine digits and display all numbers in billions. You can choose any characters, in any color, as prefixes or suffixes to numbers. To design your own display formats, you can either edit a standard format line or write a string to indicate an entirely new format.
A wonderful gift to the user is the Undo command. This feature alone is practically worth the purchase price. A related command saves time by repeating the most recent command.
Other nice touches make it easy to build spreadsheets. If you want to enter the same or similar formulas in every cell within a group, you can do it at a single stroke. Highlight the cells, write the formula, and hit Ctrl-Enter instead of Enter. Relative references are adjusted and absolute references stay absolute. The Copy command also comes with nifty options. You can add the source range to the destination range (or subtract, multiply, or divide) or copy only values or formats.
Cell naming is especially well done. If you have data with labels down the left column and along the top row, you can highlight the whole matrix and name every cell in it. Each cell at an intersection of a column and row gets a unique name, such as March Expenses. You can use these names in formulas and, as with functions, when you need them you can paste them into your spreadsheet from a list.
Excel can build little databases almost exactly the same way 1-2-3 does, with records strung out in single rows. You manipulate them with criteria ranges, extract ranges, sort keys, and so on. A handy advance over 1-2-3, though, lets you enter data into pop-up windows that look like labeled forms so that you don’t have to type in new records row by row.
For sophisticated applications, Excel gives you array math. This means that with a single formula you can multiply one group of cells by another group of cells and add up the results. In other spreadsheets, an operation like this would take a great many different formulas.
If you need to do consolidations, Excel lets you write formulas that refer to cells in other spreadsheets. A nasty kink in this feature, though, is that if a formula refers to more than one external spreadsheet, all the spreadsheets have to be in memory. You’d usually want to do consolidations only because you had a lot of different spreadsheets. Unless you had loads of expanded memory, your roll-up model might not fit in RAM.
If you do have huge spreadsheets, though, Excel takes the agony out of lengthy recalculations. Instead of stupidly grinding its way through the whole sheet recalculating everything, Excel updates only formulas affected by the latest changes. Just as helpfully, Excel doesn’t freeze your machine while it’s recalculating; start typing and you’re back in control. Excel starts recalculating again once you’re finished.
Excel gives you a whole bagful of tricks to keep bugs out of your spreadsheets. For example, you can attach notes to any cell, explaining the reasoning behind a value or formula – a feature that you can add to 1-2-3 by buying one of the cell notation add-in products on the market (See “Scratch Pads and Annotators: TSR Notes to Yourself.”) Another wonderful feature lets you find all the cells that depend on a particular reference, or all the cells the reference depends on. You can get a list by cell address, or if you like, Excel will highlight cells right in your worksheet. If you find you've made a consistent reference error, you can fix every occurrence at a stroke by using the search-and-replace command, which works on formulas as well as labels.
With all these auditing techniques, if you ask Excel to highlight offending cells, you may want to see more of the worksheet than usual. Excel has a preview feature, which lets you squeeze your spreadsheet down to tiny type that is 4, 5, or 6 points in size, instead of the usual 8- to 25-point size that you usually work with. Of course, the smallest type is much too small to read, but you can see six normal screenfuls at one time. You can still make use of all of the normal spreadsheet functions in this bird’s-eye view, which allows you to examine the overall structure of your spreadsheet.
Even if you’re not thinking about audits or maintainability while you build your model, you can use the Apply Names command to make things much easier for anyone who comes later. By giving the key values in your worksheet useful names like back_orders or tax_rate, you can force every formula in your model to display names rather than cell references, making your logic much clearer.
The Precision as Displayed option is another great way to reduce errors. It ensures that if you format your data with two decimal places, that is the value Excel stores internally – not some 12-decimal-place monstrosity. This means you never have rounding errors or tables that won’t cross-foot.
If you do make mistakes, Excel has eight different cell error messages to tell you what you did wrong. #DIV/0 obviously means you tried to divide by zero, and #NAME? means you used a range name that Excel hasn’t heard of.
Graphing and printing
True to its graphics orientation, Excel lets you draw lovely charts and graphs. It gives you seven basic types, such as bar, line, and pie, and 44 different variants on these basic types. You can pick these different formats from a charting gallery, just as you choose commands from a menu. You can adjust colors, patterns, labels, and chart titles. If you want to emphasize a value, you can even draw an arrow to it.
But the best thing about Excel graphs is their intelligence. If you highlight a data matrix, with labels across the top and down the left-hand column, Excel understands that the numbers should be data points and that the labels should run along the two axes of the graph. Excel saves you time by arranging all this information the way you would arrange it yourself. You can then add graph titles and other text in the colors and fonts of your choice. If you want to examine a graph while you work on the spreadsheet that contains the data, you can squeeze the graph off in a window at the corner of the screen where it will rescale and redraw itself. As the data change, the graph changes.
Excel drives most common printers and plotters, and gives you the usual options of specifying headers and footers, page breaks, titles, and the number of copies. One of its slickest features lets you preview a print job if you’re not sure what it’s going to look like on paper. You get a screen image of the printed page in tiny, unreadable type, but you can pick a spot and zoom in on it if you need to check the text.
If you have the right hardware, you can print a spreadsheet with just as much color and style as Excel can use to display it. With a laser printer and the right font cartridges, you can build a spreadsheet that not only figures your taxes, but prints out a perfect copy of a 1040 form, complete with lines, fine print, and shading.
Macros and more
All the power of Excel is at the disposal of what is clearly the most sophisticated spreadsheet macro language available. Macros stay in separate macro worksheets, which means you can use the same macros on any number of different models or even on a series of models in succession. While they’re running, you can hide macro sheets or reduce them to icons to keep your programs secure.
For automating simple, repetitive jobs, you can record keystrokes and mouse clicks directly as macro script. For complex programming, you can use any of an astonishing 355 commands and statements. Macros can be set to run at particular times, when you open or close a file, or when certain data change. They can tell whether a model has been changed since it was last saved and what subdirectory it came from. You can even write macros that remap the entire keyboard to the characters – or macro routines – of your choice.
The macro language is also your gateway to interprogram communication. With Excel running under Microsoft Windows, Version 2.0, macros can load other Windows programs and send command sequences to them. At an even deeper level, Excel can establish two-way communications links with compatible Windows programs, in a procedure called dynamic data exchange (DDE). With DDE links, Excel can act either as a client or as a server. Each program has access to the others’ command primitives and can share memory objects.
As an example of DDE, you could write a macro that tells Excel to load a communications program at a certain time of day. Excel would then tell the comm program to dial up an on-line stock quote system and feed updated quotes to Excel. At certain price points, Excel could tell the comm program to dial a broker and place an order. DDE is likely to be at the heart of many multitasking applications running under OS/2.
Finally, in a feature strictly for programmers, the macro language has commands that let you run FORTRAN and C routines you have written yourself. You can also use these commands to call routines from the Windows library.
Help, manuals, and tutorials
For users who are not going to write their own C routines, Excel has the most thorough set of help screens, manuals, and tutorials I have ever seen. There is an on-line manual that gives information on any topic, as well as context-sensitive help. You can turn the mouse pointer into a question mark, move it onto anything on the screen, and get help on that. There are even special help screens for 1-2-3 users. If you know how to do something in 1-2-3, the program tells you how to do it in Excel.
Excel’s on-line tutorial and reference manuals are excellent. The tutorial gently coaxes you through realistic Excel sessions, and the manuals are clear and well organized.
What could keep Excel from becoming an instant hit? Two things. The first is Excel’s hunger for hardware. Graphics-based programs are naturally slower than character-based programs, so Excel is a sloth on a regular 8088-based PC. It runs all right on an AT but doesn’t really get frisky unless you move up to an 80386. Even then, it doesn’t have 1-2-3’s satisfying snap. Also, though you can run Excel with a regular CGA monitor, anything less than an EGA is ugly. Finally, Excel is a RAM hog; you’ll need expanded memory to run models bigger than 180K.
All the same, Excel is just too good not to have a fighting chance. At the very least, it should put the fear of God into Lotus Development Corp., and we’ll all benefit from that. And once people realize there are alternatives to 1-2-3, they might consider some of the other fine programs now available. (See “Challenging 1-2-3 on Price and Power,” PC Magazine, Volume 6 Number 18.) Spreadsheets have been a one-product market for too long.
Jared Taylor is West Coast editor of PC Magazine.
Excel versus 1-2-3: Summary of Features
|Page added on 22nd September 2004.
Copyright © 2002-2005 Marcin Wichary
|Printable version | Contact | Site map|