Multiplan

Mike Liardet looks at Multiplan – Microsoft’s entry to the spreadsheet fray.

After releasing the Apple version of Visicalc about three years ago, Visicorp enjoyed at least 18 months completely unchallenged in the market for what has now become known as spreadsheet software. But in the last year and a half there has been a steady stream of Visicalc rivals arriving on the scene and, naturally, some of the established companies have been getting involved in this growth area.

Probably the best known of all the micro software companies, Microsoft’s pedigree goes right back to those prehistoric days of ‘core-store’, paper-tape and teletypes – 1975 in fact, when the first of a million microcomputer systems was equipped with a Microsoft Basic interpreter. Now Microsoft has augmented its own spreadsheet system: Multiplan. Will Multiplan further enhance Microsoft’s reputation for excellence? Will it be another Ford Edsel? (You should get this point if you have heard of a Ford Edsel and you definitely will if you haven’t!)

The first thing that strikes you when confronted with a copy of Multiplan is the packaging: Microsoft has obviously invested a lot of effort (and money as well, I am sure) in presenting its ‘new baby’ to maximum advantage. A heavy-duty transparent plastic case holds a substantial ring-bound manual, system disks, various leaflets and a few pieces of carefully positioned cardboard mouldings – simply there to mask out awkward gaps and present an uncluttered appearance through the transparent box. Readers who are concerned by such a flagrant wastage of the world’s resources on a mere piece of marketing-hype will doubtless be relieved to learn that you need not throw the box away after purchase – it readily converts into a sweet little bookstand to support your manual!

Anyway, underneath the packaging we eventually find the disks – my review copy was for the Apple II (DOS 3.3), but Multiplan is also available for The Apple III, CP/M systems and, of course, Microsoft’s MS-DOS. All versions are evidently functionally identical, with just a few pages at the start of the manual outlining any minor differences, so non-Apple owners should still bear with me! (I also had the opportunity to take a quick look at the MSDOS version on a Sirius, so have made occasional references to this, too. In particular, I have included benchmark results for the Sirius version, specifically to check out Multiplan’s performance with a new generation (8088) processor and all that extra memory capacity.)

Getting started

Getting started proved fairly easy – the ‘First Time’ instructions were not on page 1, where I like to see them, but a little bit of page-thumbing soon tracked them down. A bit of disk copying, data disk initialisation, and two or three minutes later I was faced with a reassuringly familiar display of a spreadsheet. The only hold-up in all this was to have a good chuckle at the latest piece of computer jargon, encountered in the instructions for seeking the system for optional (on the Apple) 80-column display mode: ‘Recable’ – to exchange 40-column video cable connection with 80-column!

The initial display is of the top left hand corner of the spreadsheet, showing seven spreadsheet columns and 20 rows, all completely blank. The remainder of the display is devoted to helpful prompts: the names of twenty different ‘commands’, a ‘what to do now’ message and status information, such as percentage of storage space remaining, current cursor position, etc. Both rows and columns are identified by numbers, unlike many systems which use the alphabet for column headings. The repercussions of this are fairly great, since whereas ‘Q99’ is unambiguously a reference to a specified cell, ‘1799’ clearly is not. Multiplan provides several alternatives for identifying cells, but the simplest is that they be written as ‘RyCx’ – eg, ‘R17C99’ – a little bit longer than ‘Q99’!

Moving around

Moving the cursor around the spreadsheet is very simple – single control-key hits (ie. simultaneously pressing ‘Control’ and one other key) move the cursor left, right, up and down, with the VDU screen window being ‘pulled along’ by the cursor if an attempt is made to move to a cell off the edge of the screen. Sensibly, the keys that achieve this movement are arranged in a diamond (on the Sirius the arrow keys are used) – easy to remember and easy to touch-type when you are looking at the screen. Further investigation reveals that there are also control-key hits to ‘home’ the cursor to the top left hand cell and to the bottom-right, and a ‘Go-to’ command where destination coordinates can be typed in, as well as a rapid scrolling facility where the cursor is moved several cells at one go.

Also of particular interest is a very powerful split-screen facility. The screen can be subdivided into display areas (called ‘windows’ in the manual), each displaying different parts of the spreadsheet, and the cursor can be quickly ‘jumped’ from one to the next. There are many possible uses for this: locking row and column headings for continual display, quick movement between different parts of the spreadsheet, and keeping totals or whatever continually in view when other parts of the spreadsheet are being modified. Moreover each window can be displayed with a nice surrounding border, and can also be ‘linked’ to another window so that columns or rows in both always line up correctly. If all this sounds a little confusing to the newcomer, then take heart. You can completely ignore the facility at first, but once you are ready for it, the chances are that however you want to lay-out your display then Multiplan will accommodate you.

Entering data

As with most spreadsheet systems, the ‘bread and butter’ activity centres on entering or changing numbers, titles and formulae. To achieve this, simply move the cursor to the cell to be changed and start typing whatever is required there. The only thing to watch out for is that text entry must be preceded by selecting ‘Alpha’ mode (simply press ‘A’ before typing the text) otherwise the chances are Multiplan will assume you are entering a command – occasionally disastrous. For example, a sensible abbreviation for Total-Costs-Yacht could be ‘TCY’. Enter this without pressing ‘A’ and Multiplan does a ‘Transfer-Clear-Yes’ wiping out the entire spreadsheet! Don’t believe it could happen? A PCW editor (I’ll spare his blushes) did it! Well, it probably wasn’t a yacht, but a yo-yo or a yard-of-ale or something…

The formulae themselves can be built up using a wide range of maths and other functions, including trig, standard deviation, string concatenation, logical and table look-up, etc. The notation used is the classic keyboard version of school maths notation, easily learned by anyone not already familiar with it. As we have already mentioned, formula references to cells require an RyCx’ notation – eg, the formula to add the first 2 cells on the first row could be written as ‘R1C1 + R1C2’. However, there is a little trap lurking for experienced spreadsheet users – the replication facility does no formula adjustment whatsoever. Thus, if the above formula was located at R1C3, and then copied to 99 cells below, each and every copy would be ‘R1C1 + R1C2’ and the expected Column 3 = Column 1 + Column 2 would not be achieved. It turns out that the original formula, quite correct if no replication is envisaged, should be ‘RC[-2| + RC[-1)’, meaning ‘add cell in current row two columns back, to one in current row one column back’. Now, wherever this formula is located, it will add together the two previous values on the row, and in particular, if replicated right down column 3 it will do the column sum correctly.

If typing ‘RC[-2] + RC[-1]’ seems like a bit of a fingerful (tactile equivalent of mouthful) then Multiplan to the rescue! Instead of working out ‘RC[-2]’, etc, simply use cursor moves in mid-formula entry and Multiplan will type in the formula for you. In the above example only the ‘+’ need be entered from the keyboard, the rest of the formula being built up by using the cursor to point to the cells to be referenced.

It is also possible to refer to cells by their row or column name and thus build up formulae like ‘profit = sales – costs’. Since (a) this is immediately comprehensible and (b) always replicates correctly, the extra typing involved is well worth it!

In conclusion, I must say that I did not greatly like Multiplan’s methodology for referencing cells. It should be noted that cell references occur not only in formulae, but are also required by the majority of commands (see below), so a major part of one’s time at the keyboard is spent using them. In fairness I must point out that (a) my previous spreadsheet has been with the Visicalc style of cell-reference and (b) that Multiplan has some compensations for this minor irritation with some excellent other features and facilities.

Commands

Thus far, we have looked at Multiplan’s basic essential facilities, but of course there are many other, typically more peripheral (in both senses!), functions needed to provide a comprehensive spreadsheet system. These extra functions are provided for by Multiplan commands, and invoked by selection from a command-menu.

Actually, in passing, we have already touched upon four commands provided by Multiplan – ‘Go-to’ cell, ‘Alpha’ for entering text, ‘Copy’ for replicating cells, and ‘Window’ for the split-screen facility. There are in fact 20 in all, each starting with a different letter of the alphabet, and all permanently displayed at the bottom of the screen. Bearing in mind that there were only six letters of the alphabet to spare, the implementers have done a pretty good job of choosing 20 sensible names – probably the worst one is ‘Alpha’ (it couldn’t be ‘Text’ because that clashes with ‘Transfer’ and ‘Transfer’ couldn’t be ‘File’, ‘Storage’ or ‘Disk’ because F, S and D are in use, etc).

Anyway, in the unlikely event that a command’s meaning is unknown, or in the more probable event that the precise method of usage is unclear, there is an excellent ‘Help’ facility available. Basically the list of command names has its own cursor, which can be shifted along by pushing the space bar. Commands can be selected by moving the command-cursor then pushing ‘Return’ (or by just typing the command’s first letter – much quicker). However, if ‘?’ is hit instead of ‘Return’ the spreadsheet screen is replaced with a ‘help’ screen for the currently indicated command. Moreover the information is not just a few cryptic instructions, but a fairly comprehensive run-down which in some instances extends to several pages. By the way, all the help-screen information is read from disk when needed, and does not affect the precious memory allocation for the spreadsheet itself.

To get some idea of the command facilities available, here is a quick rundown of all 20:

  • Enables text to be entered at the current cursor position.
  • Blanks out one or more cells. Contents are blanked out, but display format assigned to cell is unchanged. Not the same as Delete since, in particular, the following rows or columns are not shifted.
  • Copies cells from one place to another (ie, replication). Relative-copy is not possible (see text above) – must do absolute copy of relative formula!
  • Deletes a row or column of cells, moving all subsequent rows/columns back by one.
  • Instead of correcting a long formula by retyping from scratch, this command can be used to apply the changes quickly.
  • Numerous different display formats are possible: different column widths, centre, left, right justify, scientific, integer, financial, primitive bar graph, and more besides! As an extra convenience, a default format can be specified, assigning the format you most expect to use to all cells not explicitly reformatted to something else.
  • Go to cell specified by its name or coordinates.
  • Gives general help information, not covered by the help-screens, for each specific command.
  • Inserts a blank row or column, moving all subsequent rows/columns along by one.
  • Locks or unlocks specified cells. Can permanently lock all formulae – useful for turnkey systems.
  • Moves a row or column to between two other row/columns.
  • Enables a cell or group of cells to be given a user-supplied name. This name can be used in formulae, and also by the ‘Goto’ command. It saves confusion if the name here is the same as the visible title.
  • Used to set basic operational features, eg, switch off auto-recalculation or audible error beeps. The former is very useful when the spreadsheet is getting fairly full and every change takes several seconds – not to be registered on the screen, but for its effects to permeate through the system. The latter is absolutely priceless if you work at home and your family ‘can’t stand that incessant cheeping’ (to quote my good lady).
  • Can print to printer or disk file. Option to print the formulae as well as the calculated values. This is useful for documenting or debugging the model. It’s also possible to print selected areas.
  • Finish – back to resident operating system (eg, CP/M, MS-DOS, etc).
  • Sorts calculated or entered numbers or text by suitably shuffling rows.
  • Load, save, delete and other disk file operations. Of particular note: Multiplan can read Visicalc data files, or read/write files in a well-documented external interchange format, as well as using its own internal disk format. As it can also print to disk, it is extremely versatile in its file-handling.
  • Can optionally be used for entering formulae or numbers.
  • Split screen facility.
  • Used to read in answers calculated by one spreadsheet as raw input data for another. Can be used for ‘consolidation’.

Documentation

The documentation is comprehensive, clear and well-written. The bulk of it is in a stout ring-bound manual (minor niggle – the rings are not circular and tend to snag the pages when you are turning them quickly). It has obviously been put together with the sort of thoroughness we would expect from Microsoft, right from the Contents page at the front to the Index at the back. The basic material provided is:

  • System-specific instructions. How to create your working disks under your particular operating system.
  • Organised as seven lessons. Gives you key by key instructions, starting with simple cursor moves in lesson one through to multiple work-sheets at the end. Well illustrated.
  • In alphabetical order, everything you need to know about the command, key-strokes and formula-functions. Also includes a list of all system messages, together with advice on what to do when you encounter them.
  • Extra helpful information, including a glossary and notes for Visicalc experts – a nice touch!
  • Quick Reference Guide. A separate pocket book (16 pages), being a condensation of the reference section in the main manual.
  • Help Screens. Comprehensive instructions on-screen for every command and a few of the other facilities.
  • With this breadth of documentation, there should be something to please all levels of user. Complete beginners can try the tutorial. Experts will probably just use the quick reference guide or help-screens and everyone can make good use of the comprehensive index.

Sirius slip-up

Having given the Apple version a thorough work-over, I arranged a joyride on somebody else’s Sirius. The article was nearly complete – I just needed to pencil in the Sirius Benchmark times and then off to Mustique for yet another three weeks.

First problem: Sirius version of Multiplan manual temporarily mislaid. Well, I should know the system well enough by now. So, in preparation for Benchmark 1, I quickly set up the first 12 columns by 200 rows of the spreadsheet. (Readers familiar with the benchtests will know that this results in a display of 1.. 12 in the first row, 13. . 24 in the second, etc.)

Next I needed to set up column 13, each cell in it being the sum of the previous 12 in the row. Easy! Just use the row-sum function in column 13 of row 1, and then copy it down to all cells below it. Unfortunately I couldn’t remember the correct syntax for using it. Anyway, after experimentation I found that ‘SUM(C1:C12)’ at least did not give a formula error message, but it did seem to be displaying the wrong answer. Okay – time to copy it. Well, much disk-whirring and clanking, then watch the calculation count-down on the VDU display. 45 minutes later; I’m still waiting and the disk is still whirring and clanking and countdown’s still not finished – I’m frightened to switch off in case I corrupt the disk (it’s not mine, anyway) – can’t stop it at the keyboard, etc. Anyway it took about 50 frustrating minutes.

So, what went wrong? Well, basically a minor slip-up in my use of the SUM formula. I eventually got it right (by using a help-screen, what else?): ‘SUM(RC[-12]:RC[-1])’ and the whole test was over in under a minute. The formula I had originally used did not add the row up, but calculated the whole 12 x 200 array of numbers, and of course this formula was then copied 200 times down the column – a bit of a hefty number-crunch!

Anyway, the moral of this story is: make a good effort to learn Multiplan’s cell referencing – it could save you a long wait!

Conclusion

We have taken a fairly fast swoop right through the major facilities and features of Multiplan; so fast that some very valuable features, not generally available in mere state-of-the-art spreadsheet systems, may have gone unnoticed. Just for the record.

Multiplan gives you:

  • If you need to sort columns of figures or text then it is impossible to do this without a ‘Sort’ command.
  • Multiple worksheets. Results from one worksheet can be communicated to another, useful for consolidation.
  • Multiple split-screens. Very flexible facility to design VDU screen display of spreadsheet.
  • Flexible file handling. In particular data interchange with other software is feasible, and Visicalc data files can be read (but not written! – no doubt Microsoft doesn’t want to encourage users to migrate that way!).
  • Available on 16-bit microprocessor (8088/6). The new 16-bit processors can handle a lot more memory, and spreadsheet systems which have been properly installed on them can use this extra memory for setting up bigger spreadsheets (see Benchmarks).
  • Comprehensive help-screens. In addition to these. Multiplan also provides more mundane, but by no means universally available, facilities – such as cell references by names, formula protection, formula printout, print to disk and formula editing.

Certainly Multiplan has a lot of facilities to offer, but what is it like to use? Well some minor complaints here: the row/column numbering scheme increases the amount of typing for formulae. You have to consider replication consequences when you enter a formula, rather than when you do the replication, you have to choose the ‘Alpha’ command before you enter text (okay, it’s only one extra character, but most other spreadsheet systems don’t do it this way). To balance these minor grumbles are comprehensive error messages, and understandable prompts for all input.

So finally, my advice to spreadsheetless owners of Apples, CP/M or MS-DOS systems, or to anyone looking for an upgrade: put it near the top of your list!

Benchmarks and other measurements

These tests were run on an Apple II system with 64k of RAM (which is in fact mandatory) and an 80-column display card (which is optional). Available space for the spreadsheet itself amounted to 21k. Figures are also included for the Sirius (with 128k of RAM, and theoretically extendable to 800k+), running MS-DOS and allowing greater storage space for the spreadsheet. Where the Sirius figures are different they are appended in parentheses after the Apple figures.

Incidentally, a Sirius retails for around £2500, and the nearest equivalent Apple system (but with lower disk capacity, half the RAM, 8-bit processor) would be around £1750.

  • Spreadsheet size: 63 columns wide by 255 rows.
  • Numeric precision: 14 digits.
  • Max column width: 32 characters.

The benchmark tests are described in ‘Which Spreadsheet’, PCW Feb 1983.

Benchmark 1: (a) max rows accommodated: 95 (235); (b) recalculation time: 60 (55) seconds – ie, 1.5 (4) rows per second: (c) recalculation time: 60 (55) seconds; (d) vertical scrolling: 6 (6) rows per second; horizontal scrolling: 4 (4) columns per second.

Benchmarks 2: max rows of text accommodated: 190 (Sirius not tested).

Benchmark 3: max rows of numbers accommodated: 190 (Sirius not tested).

Price: Around £150.

Checklist

Documentation: 400+ pages, contents, tutorial, reference, index, quick reference and help-screens. Well-illustrated. Excellent.

User-friendliness: Consistent and easy to use — cell-referencing can be a little tricky!

Error-handling: 20+ error messages. Erroneous calculations (eg, zero-divides) displayed as special error values.

Facilities: Arithmetic and other functions: +, -, *, /, %, string operations, logic, descriptive statistics, trig, logs, look-up and more besides!

Configuration: version tested easily configured for different types of Apple screen.

Graphics: a let-down compared with the other facilities!

Interface to other software: specifically can read Visicalc files, and print to disk. Can also be interfaced to other software using data interchange format (requires programming skills to do this).

Spreadsheet overlays: yes – can do consolidation or merge information into existing spreadsheet.

Turnkey: Apple version is turnkey with all disk formatting, copying, etc, achievable without recourse to Apple DOS.

Insertion, deletion and replication: yes.

Display flexibility: just about everything you could possibly want. Excellent.

Protected cells: yes.

Formula printout: yes.

Formula editing: yes.

Automatic/manual recalculation: yes.

Out of memory: memory left permanently displayed. Recovers correctly when it runs out of memory.

Long jumps: can jump directly to any specified cell.

Sorts, searching and logic: yes.

First published in Personal Computer World magazine, April 1983

Advertisements

Crash Course in Spreadsheets

TheSpreadsheet001

Pete Galliard tallies the Spectrum-based Spreadsheet package against VisiCalc and Supercalc

Microl’s spreadsheet package for the Sinclair Spectrum is cheap, especially compared to VisiCalc and Supercalc – its big brothers on the bigger micros. And since the Spectrum is so portable, it’s practical to carry it around in your briefcase to the office, home or a hotel, and just plug into a TV set.

Features

The Microl Spreadsheet lets you SAVE models you have created along with the program itself. There is a CALCULATE command, which allows recalculation of the whole model. The REPLICATE command allows calculation rules to be reproduced over different parts of the spreadsheet. There’s also the option to modify those calculation rules, if you need to do so.

You can make changes to format, such as the number of decimal places, and it is possible to alter the widths of columns. Although you can display only four columns at one time, there is a jump facility to let you move the window to any part of the spreadsheet you wish. You can enter text and numbers into the cells, and all the standard arithmetic rules can be used.

There are also quite a few features not there. You cannot:-

  • split the screen to give two independent windows on the spreadsheet;
  • overlay data from one spreadsheet to another;
  • move, insert or delete specific columns or rows;
  • search for a particular number;
  • protect particular cells from accidental changes or sabotage.

Presentation

The package comes on cassette, along with a simple 15-page manual. Microl says the manual is being updated to include extra information on how to design effective models.

Getting started

It takes about three minutes to LOAD the package from cassette. SAVEing and re-LOADing each model takes about the same time.

If you’re new to spreadsheets, expect to spend an hour or two learning how to use them. The knack is fairly easy to pick up, especially if you are used to playing with numbers. The layout itself is identical to that used in larger spreadsheets.

The commands are easy to use – they are simple, reasonably memorable, and consist of single-letter or single key commands. Most are the initial letter of the action you wish to perform, and all are set out in a single long list in the manual.

One of the most annoying problems with this package is the extremely slow response when moving beyond the limit of the window. Program crashes are also frequent and easy to cause.

There are no DELETE, INSERT and MOVE commands, and this reduces the package’s scope. Aligning numbers with decimal positions is also a problem.

I used the package to build a budget model for testing, and I found that when I entered a whole number of pounds, with zero pence, the layout ended up looking confusing. The program right-justified it so that the pounds ended up in the pence column. You would have to define a different local format to cope with those cells affected.

If you want good-looking reports on your screen, you will need to spend a good deal of time formatting it. But if you can tolerate truncated titles and headings, and just focus on the numbers, the model can be built up quickly.

When the window passes beyond the nearby range of five columns or 20 rows, there is a delay of about ten seconds. I carried out a benchmark test by building a model of 26 columns and 31 rows and filling the whole matrix to capacity with numbers seven digits long. When the matrix was recalculated I found it took six minutes to complete. I tried recalculations with decimal numbers and text information, and got similar results.

The size of the model you can create is limited. The package comes set up with a default model size of 26 columns and 31 rows. The maximum number of columns remains 26, but you can extend the number of rows to 99, if memory space allows.

This package’s reaction to virtually every misdemeanour is to crash. It crashes if you use invalid expressions or command keys, long calculation rules, and if you reach the limit of memory size. Recovery is pretty straightforward and the data is not lost as all that happens is it dives into Spectrum Basic. But program crashes resulting from typing errors are irritating.

Verdict

Overall, I found this product fair value for money. I had to fault it on reliability, and the performance really was too slow on larger models and when the window was moved, not surprisingly since the package is written in Basic. If you have a Spectrum and are prepared to live with those weaknesses, you will probably find plenty of uses for Spreadsheet.

Rating

  • Features – 3
  • Documentation – 3
  • Performance – 2
  • Usability -3
  • Reliability – 2
  • Overall value – 3

Details

  • Name – The Spreadsheet
  • Application – Spreadsheet
  • System – ZX Spectrum, 48K
  • Price – £9.95
  • Publisher – Microl
  • Format – Cassette
  • Language – Basic

Image by Kieren Phelps

First published in Personal Computer News magazine, 25th March 1983

Microsoft Excel

Microsoft Excel is a powerful, sophisticated spreadsheet which runs under Windows and has the potential to overtake Lotus 1-2-3 in the popularity stakes. But how do its features compare with those of its established Macintosh relative? Anthony Meier finds out.

Microsoft’s new spreadsheet program, Excel, looks set to leave Lotus 1-2-3 and its lookalikes well behind in the spreadsheet stakes. It promises to be the most powerful and user-friendly spreadsheet written to date. It is being introduced as the third generation spreadsheet for personal computers, and is designed primarily to run on machines based on the 80286 and 80386 microprocessors. Macintosh users will be familiar with this program already, as a version of Excel has been available for this machine for 24 months or so (see the ‘Function comparison’ box for a comparison between the two versions).

Overview

Excel is a sophisticated piece of software which offers many advanced spreadsheet facilities and programming features, an integrated onsheet database and a wide range of charting and graphing facilities. It is the first spreadsheet in the MS-DOS environment to offer interactive, dynamic linking of worksheets, a one-step automatic macro recorder and high-resolution output. It runs under Microsoft’s Windows 2.0 and takes full advantage of all its facilities, providing multiple worksheets in overlapping windows onscreen, pull-down menus and full mouse operations.

Excel001

Excel makes use of the ability of Windows 2 to have a number of spreadsheets open at once. The arrows at the top left size the windows. The most obvious difference from Excel on the Apple Macintosh is the use of colour.

A run-time version of Windows 2.0 is bundled with the program for users without the full version. A version of Excel is also planned for the OS/2 operating system. Windows 2.0 has an identical interface to that of OS/2 with Presentation Manager, so Excel users should find making the transition to that new operating system easy.

The machine I used for the review was a Dell 286 with a 20Mbyte hard disk, EGA card, colour monitor and mouse. I also had an AST card installed which increased the memory from 640K to 2.5Mbytes to give more room for testing large spreadsheets. There is only about 140K available for data on a standard 640K machine.

Installing the program on the hard disk was very simple. It involved inserting the setup disk, typing ‘setup’, and following the instructions given on the screen. These asked for the other disks supplied to be inserted one by one until all the necessary files had been copied across. I was supplied with 14 disks, eight of which contained the files for Excel, the other six were files for run-time Windows.

In use

When the program had been installed and loaded, I found Excel very simple to learn and use. Virtually all of the user-friendly features of the combinations of clicking, double-clicking and dragging.

The mouse can be used to give all the commands and instructions you need in Excel. It saves you from having to learn and type in commands at the keyboard, and makes program operation very fast. You can also keep your eyes on the screen instead of continually glancing at the keyboard. However, keyboard lovers can still use the keyboard instead of the mouse for all the commands and operations they need – even moving and sizing windows. Pressing the ‘Alt’ key makes the menu bar active, then pressing the underlined letter of the menu title you want (or using the cursor key and Return) pulls down that menu. Finally, pressing the underlined letter of the command you want (or using the cursor key and Return) invokes that command. Pressing the ‘Esc’ key cancels the menu selection.

The mouse, however, does make it quick to select a cell, or cells, for data entry – you just move the pointer to the cell you want and click to make it active. You then need to use the keyboard to type your data in. The mouse can also make operations like inserting and deleting rows and columns, and cutting, copying and pasting cells, very fast.

The mouse also comes in handy for entering cell references into formula. Instead of typing in a cell reference, you only need to point and click on the cell in question for its reference to be automatically inserted into the formula. Dragging the pointer across a range of cells inserts that range into the formula. And you can include references to cells on another spreadsheet (linking the spreadsheet) just by clicking on the cells in that other spreadsheet. This saves time setting up formulae and speeds up the creation of models.

Spreadsheet handling with Excel is very impressive. You can have several spreadsheets, charts and macro sheets onscreen at the same time, each one in its own window, like so many pieces of paper. You can shrink or expand the windows, depending on which one you are working on, and you can transfer information easily from one to the other.

Spreadsheets can easily be linked, allowing you to consolidate figures from as many different spreadsheets as desired. Because you can work on many spreadsheets at once, you can see the effects of changes in one worksheet on other linked worksheets immediately on the screen.

Each spreadsheet has a maximum of 16,384 rows by 256 columns, and it is easy to move quickly to any desired location using the mouse on the scroll bars along the sides of each window. Column widths and individual row heights can be adjusted easily with the mouse. Each window has a horizontal and a vertical split bar which you can use to divide the window into a maximum of four panes, to see different parts of a spreadsheet next to each other. You can also open up new windows for the same spreadsheet if this is more convenient. As you are expected to have many windows fighting for space on your screen, there is a window menu which lets you select the window you want to bring to the top of the others.

Functions

Excel has all the features and functions you would expect to find in a top spreadsheet package, such as cell protection, calculation options and zero suppression. It has an ‘undo’ feature that can reverse your last command if you make a mistake, and it also has a matching ‘repeat’ feature that you can use to repeat your last command.

Excel only recalculates those cells that have changed since the last calculation, thus speeding calculation. It also uses ‘background’ calculation which lets you continue working while it recalculates. And it doesn’t require you to wait until all the cells have been calculated before you can start working again, which is nice.

Excel has more functions than both Lotus 1-2-3 version 2 and Excel for the Macintosh. The box on page 140 gives a comparison, although functions alone should not be used as a guide to a program’s overall capabilities.

Many of Excel’s functions are similar to those of Lotus 1-2-3, so 1-2-3 users should be able to build spreadsheet models with Excel’s functions without too many problems. Some of the interesting new functions provided by Excel are as follows:

  • The ‘information’ function, CELL(type-of-info, reference), returns information about the formatting, location or contents of the upper left cell in ‘reference’. CELL(“width”, F13), for example, would give you the column width of cell F13. CELL(“format”, B12) would give you information on the cell formatting.
  • The text function, CODE(text), returns the numeric ASCII code of the first character in ‘text’. CODE(“Alphabet”), for instance, would equal 65. CODE(B5) would equal 70, where cell B5 contained the text “February”.
  • Excel can be used for working on arrays, which are groups of two or more values that can be used like a single value in formulae and functions. Excel also has matrix functions which can be used for working with these arrays. The matrix function, MMULT(array1,array2), returns the product of two arrays, where both arrays contain only numbers. This might be written as MMULT(A1:B2,D1:E2).

Compatibility with Lotus 1-2-3

Many of Excel’s new users are expected to be previous Lotus 1-2-3 users, and Microsoft has developed tools and functions within Excel to make learning and using the program easier for these users. The features will also help Excel integrate more easily into a Lotus 1-2-3 environment.

For beginners, two-way file compatibility enables spreadsheets to be exchanged between the two programs. Then there is a useful 1-2-3 macro translator that can automatically convert nearly all 1-2-3 macros into Excel macros. A ‘1-2-3 Help’ facility lets users type in the command sequences they would have used in 1-2-3 and automatically gives them the corresponding Excel commands.

Presentation features

Excel002

Fonts, type styles and colour can be used to enhance the appearance and logic of a spreadsheet both onscreen and when printed. Debits, for example, could appear in red

Excel’s presentation facilities are very impressive, and provide you with a wide range of screen display and printing options. You can turn the spreadsheet grid on or off, show or hide the row and column headings, switch them between R1C1 and A1 according to your personal preference, and choose between different font types and sizes. You can use up to four different fonts on one worksheet – individual row heights will automatically adjust to accommodate the font sizes you choose. There are 19 number-formatting options which are meant to be used for things like date formats, decimal places, commas and negative brackets.

Excel007

An Excel worksheet can be as plain or as detailed as you want, with grid lines and headers being optionally shown in various colours and styles.

Individual cells can be emboldened, underlined or italicised. You can add shading, create boxes or lines around cells or blocks of cells, and control screen colours to enhance the appearance of the screen display or printed document. You could have all the positive figures in a column display automatically as blue, and all negative figures red, for instance. All these facilities help you to produce printed documents that rival word processor output and can be used for final reports and presentations.

There is a page preview facility to let you see a miniature version of your page as it will look when printed out, which is very useful for checking pages before printing them. It is also useful for viewing large spreadsheet models like a map to give you a better idea of what they look like.

Printing

A wide variety of printers and plotters are supported, and your own printer and plotter drivers can be installed during the ‘SETUP’ procedure. High-resolution graphics printers are required if you want to take advantage of the graphics output of the program – a laser printer would be ideal.

Excel includes a sophisticated printer spooler that lets you queue up print jobs, control the printing operation and continue with your work while they print in the background.

Charts

Excel003

Charts are created by selecting an area of data and then choosing a chart style option. Charts are automatically updated as the data changes.

Excel has sophisticated charting and graphing facilities. A wide range of charts can be summoned instantly from selected spreadsheet cells and will change shape automatically if the cell contents are changed. You can see a chart in one window change as the data in the spreadsheet window alongside it is altered.

To create a chart from data in your spreadsheet, you first need to select the data you want to chart. This can be done by dragging the mouse across the relevant cells to highlight them, then you select the ‘File New’ command and click on the ‘Chart’ option. This creates a new chart window that automatically contains a default-type chart built up from the values in your highlighted cells.

The program has 44 pre-designed chart formats grouped into seven types of charts: area, bar, column, line, pie, scatter and combination. When any of these is created, the program provides default labels and designs. The charts are highly customisable, however, and most of the parameters can be altered to suit your own requirements. You can alter the colours, add text labels and legends, and scale the chart horizontally or vertically to get it to look just the way you want.

Excel004

Many chart styles are available. The ‘help’ system includes a cross-reference to Multi plan and 1-2-3 commands, so users who know what to do in those programs can transfer across.

Auditing & documenting

Excel has very useful auditing and documenting features. These help you check the logic and formulae in your model, track down errors and discrepancies, and document your model for your own reference and for other users. You can attach notes to any cell and view them using the ‘Show Window Info’ command. This command also shows you other information such as the cells that contain references to your active cell (dependents) and the cells that it refers to (precedents).

You can use the ‘Formula Select Special’ command to highlight all the dependents and precedents in the worksheet for easy identification. You can also automatically find all the cells with notes or those containing a particular formula.

These features are a great help when you are creating or amending a spreadsheet model and when you are checking its logic. They reduce the risk of missing important cells and making errors.

Excel has sophisticated cell-naming features, too. You can name each cell in a block of cells automatically by using a combination of the titles in your row and column headings. You can easily find cell references in a spreadsheet and replace them with names, and you can find cell names.

You can define a name which is not attached to a particular cell, but which refers to a value: ‘INFLATION’, for instance, can be defined to be ‘4%’. Then, whenever you use the name in formulae in your spreadsheet (and in other spreadsheets) it will equal 4%.

Macros

Excel005

Macros can be created line by line or recorded; this allows Excel to ‘learn’ a process that the user performs. A separate module allows for the conversion of 1-2-3 macros.

Excel has powerful macro facilities which let you pre-program the system to perform calculations and operations automatically. Excel macros have their own programming language and are created on separate macro sheets which are handled in the same way as spreadsheets. The macro commands are typed into cells in a column and, like cells on a spreadsheet, can be deleted, copied and moved around. You can have as many macro sheets as you want, and as many different macros as you can fit on each macro sheet. The macros can then be used with any spreadsheet.

You can incorporate branches and loops into your macro, and control can pass from one macro to another if certain conditions are satisfied. You can create ‘intelligent’ macros to interact with the user for example, to prompt for information at certain stages, using dialogue boxes.

There is also a group of macro commands for customising the appearance of the program itself. You can set up your own menu bar and menu options, and create your own commands and dialogue boxes. You can use these facilities to effectively create your own custom applications within Excel.

The automatic macro creation facility can be used to build macros if you want to avoid programming – this works by simply recording actions you perform. The ‘Record’ command starts the macro recording, after which you can perform the task you want to record. When you have finished, you give the ‘Stop Recorder’ command. When the macro has been recorded, it can be edited and added to just like any other macro. In fact, you can see your macro being created line by line as you perform the actions it records. You can do this if you place the macro sheet window next to your worksheet window.

Macros can also be used for creating new spreadsheet functions; these are called function macros as opposed to the command macros just described. The 131 functions already available cover most of the standard purposes I can envisage, but function macros can be created for more complex, customised requirements. A function macro called ‘PAYE’, for example, could be set up to calculate the tax due for a given set of variables such as gross pay, tax code, month, and so on. Function macros can be used in formulae in the same way as standard functions.

Database

Excel006

It is possible to create forms for the entry of information into a database section of an Excel worksheet. There is provision for creating search criteria for finding records.

Excel has on-sheet integrated database facilities with 11 database functions and a new feature, an automatic database form interface. Any rectangular area of the spreadsheet can be designated as the database area, after which its rows become database records and its columns database fields. All the database functions, like ‘EXTRACT’, ‘DSUM’ and ‘DMAX’, are then available for acting on the information, but these don’t interfere with other spreadsheet functions which can be used as normal.

The ‘Database Form’ command is used to bring up the automatically created form window, which you can use to enter, edit, delete and find records. The form resembles the standard form layout screen that many database programs provide, and makes using the database very simple.

The macro facilities can be used in conjunction with the database facilities to perform customised database operations and create customised database applications.

How Microsoft Excel compares to the Macintosh version

On running the Windows version of Excel for the first time, I was amazed at its similarity to the Macintosh version. The look of the spreadsheet with its cell grid, the cross pointer, the menu options and the way in which the mouse operated are all the same. The ways in which you create macros, databases and charts are the same, too. On closer inspection there are a few differences, all of them turning out to be improvements. The Windows version I used did not seem to be as fast, however, but the final release version should be faster as all the debugging code will have been removed.

The Windows version has all the features of the Mac version with many more besides. The first new feature difference I noticed was a status line at the bottom of the screen that gives brief explanations of each command as you move through the menu options – very helpful for the first-time user. Another feature is that you can choose between short and full menu options: short gives you the most commonly-used commands and may be more suitable for beginners; full gives you the complete range of commands.

On the Mac version you can adjust only column widths on a spreadsheet, but on the Windows version you can adjust the row height of individual rows as well. You can also use more than one font on a worksheet. Both these features give you a lot more flexibility in designing models and spreadsheet reports.

On the Windows version, there is a new ‘Arrange Windows’ command that automatically resizes and fits all your windows into neat boxes on the screen to let you see them all side by side. I found this feature very useful when my screen became cluttered with several spreadsheet windows.

The ‘Resume Excel’ feature from the Macintosh version has been enhanced in the form of the Workspace feature on the Windows version. This lets you save all open worksheets and window arrangements you are working on for any particular project as a workspace file, to which you can give a name. You can then reload that workspace file (or any other) if you wish to continue working on that project, and all your worksheets and windows will be opened up exactly as they were when you saved them.

The auditing and documentation features of the Windows version, described in the main text, are an important new addition that make the Windows version useful and practical, and there are also many new spreadsheet functions (see the ‘Function comparison’ box).

There are other differences too, but for day-to-day operations the programs are basically the same; and a Macintosh Excel user should have no problem at all getting to grips with complex spreadsheets on Excel for Windows. However, the Windows version offers more features and functionality which power users will find very useful indeed.

Data transfer

Data transfer facilities are very important, as you may often need to import data from other programs to Excel in order to perform analysis and create reports from it. Excel can read and write files in any of the following formats: text, CSV (comma separated values), SYLK, WKS and WK1 (Lotus 1-2-3), DIF, DBF2 and DBF3 (dBase II and III). This is a comprehensive range and facilitates the exchange of data with a wide variety of programs.

The Dynamic Data Exchange (DDE) protocol resident in Windows can also be used by Excel to exchange data with other programs running under Windows.

Documentation

The Excel manuals are well up to Microsoft’s usual standard, and I didn’t have to refer to them too often since the program’s menu options are fairly self-explanatory.

Conclusion

Excel is an impressive program, and there is no reason why it should not ultimately overtake Lotus 1-2-3. It has superior power and ease of use, more facilities, and it is easy for 1-2-3 users to upgrade to. I have been a regular user of Excel on the Macintosh for some time, and I am confident that Excel for Windows will serve me equally well.

Function comparison      
Function Type Lotus          1-2-3 Excel (Windows) Excel (Macintosh)
Maths/Trig 17 26 18
Logical/Special 18 34 23
Text/String 18 21 8
Date & Time 11 12 10
Financial 11 13 8
Statistical 7 14 11
Database 7 11 7
89 131 85

Anthony Meier is a chartered accountant and computer consultant.

First published in Personal Computer World magazine, December 1987

Excel 3

Steve Cassidy is gripped by feelings of wonder and discovery as he dips into the new features of the latest version of Microsoft’s spreadsheet. But the Mac edition greys out some aspects enjoyed by Windows-OS/2 versions. Strange…

The latest version of Excel, Microsoft’s offering in the spreadsheet market has had a laser and dry ice launch at the Hippodrome – a venue famous for attracting accountants and number nerds (like me) in their droves in its usual usage – and the major version number suggests a fresh approach. Does the product warrant this much razzmatazz? At first sight, in the hands of a skilled demonstrator, it looks like it does. There’s much to talk about when it comes to new features, improvements to old capabilities, and new directions. How these components fit together is another matter.

Where they fit together is the first problem. I received a pre-production copy, composed of a stack of manuals five times thicker than my Cambridge Computer Z88, and four high density 3½in disks. Imagine the shock when the cute Windows install program informed me that first it would like to have 5Mb of space, and second it didn’t have that much available. It took me a while to realise that it was because I was using a 386 PC. On 386s, Windows can create a virtual memory swapfile on the hard disk. I had a large swapfile. Once I had used the Windows Swapfile program to disable the use of virtual memory, I could run Excel install.

This is not the most user friendly or intuitive means of installing the system: given the potential pitfalls of the Windows based procedure, I’m not convinced that the loss of the old, character based Microsoft general purpose Setup.exe is worth gaining the snazzy dialog box of the new program. That snazzy dialog box offers you the option of installing just the program, or including the tutorials, sample files and ‘add-ins’. The smallest configuration hovers around the 2Mb mark.

This is not a criticism – I can’t see how all the facilities of the system could be fitted into less space – but it doesn’t feature prominently in the installation documents. People using 286s or Macintoshes (or even OS/2 systems) don’t have this problem.

New features

On first run Excel assumes you want a tutorial, and provides one which succeeds in looking so little like a spreadsheet as to make you wonder whether a virus has grasped your machine. When you consider that 95 per cent of Mac spreadsheet users use Excel, and the £75 upgrade policy for PC users, this demo of the demo writer’s art seems curious. However, the lack of the spreadsheet look and feel is a (thankfully optional) hallmark of Excel 3’s most immediately striking changes.

There’s a tool bar located just beneath the formula entry bar at the top of the program window. Tools in the bar must be operated with the mouse. You press buttons on it to draw lines and boxes and arcs, directly on the spreadsheet, and all of these drawn items are classed as ‘objects’.

Of greater use are Button objects. These are drawn on the sheet and have a name and a section which you can press. There are also Note objects, which let you put small patches of text on top of the sheet. All the objects can have macros associated with them, so that when they are selected or clicked on, the macro executes.

That ticks off a few of the icons on the tool bar, which I’d collect as the fripperies. It leaves some more practical buttons. There’s a tiny chart: highlight a range containing suitable data, click on the chart icon and drag a rectangle on the sheet. You get an object which contains a chart. Last of the object buttons is a ‘snapshot’ of a cell, which shows a magnified version of the cell’s contents.

That leaves a few more buttons, which are not associated with the creation of objects. Working from right to left, there’s a selection tool, to allow lines and boxes to be grabbed (and, for me anyway, deleted), there’s three concerned with aligning cell contents, and a couple borrowed from the Word for Windows Ribbon for emboldening and italicising.

There’s a small Sigma button which will sum a range of figures. Microsoft says that 70 per cent of the world’s spreadsheets contain nothing more hi-tech than a bunch of totals, so the Sum button automates their construction by making a guess as to what you want summed. It’s a fairly good guesser but doesn’t consider grand totals – it sums simple values until it hits a formula or a blank. When you hit the button it performs its auto-summing through the standard = SUM() function, which you can edit.

There’s a set of buttons which are about as symbolically intuitive as a Moscow tube ticket. They control a feature which changes the way users think about spreadsheets. It’s as dramatic a change as the concept of linked, separate sheets. It’s outlining. You can declare row or a column to be a heading, and the rows or columns beside it as body text. You can expand or contract the levels of outline to show only your ‘headline’ rows, or everything, or any points between. You can have row-oriented and column-oriented outlines.

This addresses one of the fundamental procedural difficulties of spreadsheets: as information rises through an organisation, the amount of detail drops. People collate figures. With outlining, this condensation can be done on a single worksheet.

Excel3-Image001

The two sheets shown here are the same but the lower one has been compressed by using the outlining feature to remove the working, leaving just the bottom line

Bearing in mind that I’m not entirely delighted by the idea of lines and boxes and imported graphics on my worksheet, it’s nice to find that it’s possible to firstly associate the frippery-class objects with nearby cells, assign those cells to a nice low level of the outliner, and make them go away.

This kind of approach might seem somewhat silly in the light of being able to take file copies – just have two versions – but in today’s environment of mail systems, file servers and group working, an original file in a single location becomes quite an important object.

Working with files

It’s nice to see other ideas from Word for Windows leak into Excel. The last part of the tool bar is a style box, lifted without modification from the WfW Ruler. You can assign a set of attributes to a cell (font, bold/italic, alignment and so on) and have a descriptive name attached to that combination of attributes. The style box in the bar shows a list of the styles you’ve set up.

In combination with another new feature, styles can be used to make the business of preparing standard forms for others much easier. The new feature is templating: a worksheet can be saved as a template. Others can load your template, but unless they have read the little note in the manual about holding down the shift key when clicking on ‘OK’ to save the template, they can’t change your format.

The last part of the ‘departmental information’ jigsaw is in place now, too. There’s a consolidator. It allows you to bring in data from other sheets – even Lotus ones – and subject it to any of the standard mathematical functions. It would be easy to produce a worksheet showing an average for all the sales figures of all the salesmen in a region, based upon templates they have filled in. Or a total, or the standard deviation.

The possibility of consolidation functions combined with document templates is exciting. It may encourage the use of consolidation when sheet linkages should be used instead, but since it’s possible to ‘promote’ a consolidation to a set of external references, this is not a problem.

Bringing things together

If data collation from contributing spreadsheets has been a pain, the presentation of data held in other programs has been an even bigger problem. Excel, unmodified in its version 2 form for the best part of two years, has been bypassed by both Lotus and Borland’s excellent Quattro spreadsheet in terms of links to popular PC database products.

This has had the effect of making some people retain their data within Excel, and forego any of the advantages of proper relational design and indexed data retrieval in favour of the nicer presentation and ‘number nerd friendly’ algorithmic features of the spreadsheet. In version 3 they have been given an external application bundled with the program called Q+E. It’s a database retrieval system with which Excel communicates through Dynamic Data Exchange (DDE). DDE is a Windows feature, and Excel 3 uses a much expanded internal definition of DDE which improves flexibility and response times when large amounts of data are manipulated between programs.

Q+E’s interface is not a new idea: Superbase 4 for Windows, from Precision Software, is capable of either serving or driving a DDE ‘conversation’ with the current version of Excel. Q+E’s advantage is that it supports the three main categories of external database the average PC Excel user is likely to find: dBase III, other Excel files, and SQL databases, which can reside either on the PC which is running Excel 3 or, more interestingly, on a host mini, file server (running, say, OS/2 EE or one of the 57 varieties of Unix) or corporate mainframe.

A cursory glance at this bundled system (I admit to not having an SQL service available on my test facility, and space is short) shows that it forms a front end to the database. You can choose to have the remote database server do all the sorting, relational linking and subset creation, or you can download large chunks of information and bring your PC to its knees working on it within Excel (my preference is to make the database engine do what it does best, and have the PC stick to what PCs are good at). It wasn’t clear to me whether Q+E could be used as a two-way pipeline between Excel and a host database – in other words, whether those salesmen and their template spreadsheets could be made to contribute to a full scale corporate database. If this were possible, it could have interesting ramifications.

Interesting, but with an unfortunate semantic history. The macro command used to send data to Q+E, is =POKE(). This was quite enough to raise hackles long dormant, grown from exposure to too much GW-Basic. I’m sure it’s just a bad dream…

Q+E is a badly needed addition to the Excel capability tick sheet. The only problem with it is that you must have a machine classed as minimal for Windows 3 in order to be able to load both Excel and Q+E at the same time. A 640k 286 system will just load Excel and be pretty slow: I’m running most of my tests on a 2.6Mb Compaq Portable III, and the larger tests on a 5Mb 386S.

Things that have been fixed

Excel3-Image003

Excel3-Image004

As you can see from the screenshots, some pretty glaring bugs have been sorted out in the new version. In v2.1, a stacked column or area chart containing a negative number comes out as if the number were positive. Excel 3 fixes this. There are fixes to the links between sheets, too. Previously, a link to a cell in another sheet would take the form ‘ThatSheet’!$A$1: the name of the sheet, an exclamation mark, and the absolute cell reference. In the new version, relative remote addresses have been introduced. A cell reference can appear just as before but without the $ signs. So, if you copy the reference around on the new sheet, it will change the remote reference for you. Likewise, if you go to the remote sheet and move the target cell of a relative reference around, the ‘top’ cell in the linkage chain will update its reference. This is most interesting in the case of consolidated sheets and templated input or for existing collections of sheets with linkages in them. In the case of a template, even if someone decides to make a change (like inserting a line or shuffling some cells around) so long as the linkage has been made to a main sheet, Excel will tenaciously track that shifted linkage. Either that, or present you with an angry #REF! in the cell containing the unresolvable formula. (#REF! makes me laugh: it’s the noise I like to think a rather annoyed terrier might make.)

Making it make sense

Now that we’re past the frippery items, and we have some data which with luck we’ve obtained without the penance of hours and hours of stultifying manual input, we come on to the business of wringing some conclusions from it. The outliner lets the summarisers have their day, but there are many users who model what might happen, as well as report what has happened. Excel 3 supplies several neat gadgets to assist the modeller or analyst.

The first, largest and neatest of all is the macro language. All the facilities mentioned so far, and detailed later on, have matching macro commands. When you want to do some ‘what if work, the older versions of Excel provided a set of macro sheets to drive a spreadsheet through a number of calculations, feeding different variables into cells to show what might happen to a scenario according to those inputs.

The old, bundled macro sheets were tremendously useful to those who wanted goal seeking or the other facilities badly – they can take the supplied macros and rebuild them to suit their particular scenario. But this leaves a lot of people for whom the business of mastering tons of tightly coded macro sheets is too much bother, or too difficult. They could try casting themselves upon the mercy of iterative calculation – the setting up of a series of cells which refer to one another, and which are calculated until a kind of mathematical soap-film surface of least tension of figures is achieved. But I suspect many may share my unease at this curious balancing act, not because the program doesn’t do the job properly, but because the concept is rather curious.

And rather slow. A spreadsheet which is iterating to solve a particular problem (if this is too confusing, pass by to the bit about charting in the next section) has to revisit and recalculate all its cells, for each pass of the iteration. Excel 3 offers two tools to make iterative use available to a wider church. One is the Goal Seeker, and the other is the Solver.

The Goal Seeker is the simplest. It lives at the bottom of the Formula menu and it allows you to set a target value for a cell, and specify which cell you would like to have altered to make your target hit the desired value. This works only for single cells: you can’t make the Goal Seeker stabilise a series of numbers which contribute to an average.

But that’s OK; this is simple goal seeking for simple situations. Perhaps the simplest use for it is to set up a loan schedule using the =NPV() formula, then use the Goal Seeker to determine the maximum amount you may borrow for a given monthly payment. Hardened financial modellers will scoff at this example, since it doesn’t address the next logical question one might choose to ask about that loan, but this is the whole point of a simple function like the Goal Seeker. It provides an easy intro to the business of iterative solving, on just one target and one contributor cell.

If you’re reading this with a copy of Excel 3 in front of you and are following these examples, do be careful when calling up the Goal Seeker function: below it lives the Solver add-in. The Solver is a much more complex tool for controlling iteration and establishing finishing conditions for a modelling process. It is not part of the main program but instead lives as an add-in, loaded on demand. If you are running on a 640k 286 system, you can forget the Solver right away – it won’t fit. On this Compaq 286, it takes eight seconds to load.

It does the things the Goal Seeker can’t. You can cause a cell to attempt to reach a target value (or to stop the Solver when a cyclically variant number hits a maximum or minimum) by changing values in a whole series of contributing cells, subject to a list of constraint expressions. The constraints are supplied in the same ‘A1 < 0’ format which was used in the Criteria block of the old database functions.

Solving is clearly a cold-towel subject, worthy of its own article – there are dialog boxes deep in the options setup for solving which talk about Newton or Conjugate searching methods, which I will cheerfully admit to being over my head. Suffice it to say that if your business is beset by the kind of mathematical problems they used to have people solve on Brain of Britain against the clock, with six schoolboys filling a bath with teaspoons competing with four St Bernards doing an identical bathtub with brandy barrels, taking into account the speed of the St Bernards and the tendency of the schoolboys to throw the occasional bonio to slow the dogs down, then the Solver is for you.

That’s got us to the painfully complex level of iterative modelling, and we’re not finished yet. The Solver is a bundled add-in, loaded into Excel on demand and held within it. This is a more intimate form of addition than Q+E. You can also produce your own add-ins: one of the permitted formats for an add-in file is that of a macro sheet. Rename your favourite macro from a .XLM file to a .XLA, and you have an add-in. So, those people with a perfectly satisfactory custom macro application for controlling existing iterated or predictive models can swiftly adapt them to unmodifiable (and hopefully completely debugged) .XLA add-ins and carry on using them.

Charting

Excel3-Image002

The 3D graphing looks good and is easy to set up with a wire-frame representation of the graph in the dialog box

This section would have wound up under the fripperies section since it deals with another completely cosmetic part of the program: the production of graphs. It is with great personal sadness that I have to report that Excel 3 now includes 3D charts which are equal to, if not better than, those supplied in Wingz.

You’ll gather I think this is a waste of time, and it is: it merely serves to obscure the true nature of the data being presented. It’s true that people need to see the occasional cheerful bit of visual presentation to keep their attention from waning but I’m afraid I just can’t follow this drive towards warped pictures being the best way of showing people the true picture. Possibly the best part of the graphing from this gloomy point of view is that now the graph can actually be placed on the worksheet.

The things that kept the chart features description out of the fripperies are the tricks with feedback between the chart and the data which spawned it. Double click on a column chart, control-click on a column, and you can drag the top up or down to make it fit where you want. The number which produced that column on the original sheet will be changed to match that column’s new position. If the number was a formula, a slightly bigger and better Goal Seeker will pop up and allow you to specify which original value point where a suspicious user can input commands and watch as Excel plays back the series of menu options required to do the same function, at a speed that can be varied from Paul Daniels’ style prestidigitation to Emo Phillips’ agonising action replay mime…

Documentation

The manuals as supplied to me were page proofs of an early version of the shrink-wrapped documents. They carried several sections and paragraphs with the editor’s pen marks in place, which showed that any differences between the PC (Windows), Mac and PC (OS/2) versions had been ironed out before release. This is a great advance: the Excel user under any of these operating systems will be working from the same manuals.

Like most Microsoft documentation, these are clear but somewhat limited in their advice-giving. Features are clearly described, but only occasionally are you told when you might be better off on another tack. An example for the unwary is the =SET.VALUE() macro function. Its purpose is to set a cell in the macro sheet (not the worksheet) to a supplied value. You shouldn’t use it for cells in the worksheet, but the documentation doesn’t say so. More guidance in problem solving would help.

Prices

The Windows and OS/2 versions of Excel 3 cost £395 each, the Mac version costs £345. If you bought version 2.1 after 1 December 1990, the upgrade is free; otherwise, it costs £75.

Conclusion

There’s so much to look at in this package that I’ve only scraped the surface of the changes made. I’ve not had room to mention delightful features like automatic column sizing – double click on a column and it sizes to the widest cell – or the consistent concept of double clicking on any object – a chart, a cell with a note in it (flagged by a little red dot in the corner): nor will I try. Excel is one of those ‘feeling of wonder’ discovery products which rewards investigation and creativity.

The development team at Microsoft have managed to deliver genuine improvements to spreadsheet users without compromising the existing functionality or slowing down usage. They could easily have stopped with the fripperies and the Hippodrome launch but I’m extremely happy to say they didn’t, and boring types like me can turn off the Tool Bar, never draw a single line in a spreadsheet ever again, and still gain real benefit.

In theory, Excel is the same under Windows 3 or OS/2 and on the Mac. There are also sections in the manual dedicated to Hewlett-Packard’s NewWave (a vastly expanded environment for Windows 3).

In practice, some features of the system are not enabled when you run on the Mac under System 6. They’re present, but greyed out, so for once the Mac is lagging behind the PC. Microsoft says this is not the company’s fault and that the stable parts of System 7, which is rumoured to offer facilities analogous to DDE on the PC, will work with Excel 3.

Mac users will also have their own version of Q+E, to connect to DAL, Apple’s likely new method for linking to external databases. There are enough differences between platforms to cause some delay, but this time it’s not on Microsoft’s side. It seems a great shame that the one application responsible for getting the Macintosh into so many financial institutions (‘get me Excel and something to run it on’) should now be the biggest searchlight illuminating Apple’s apparent inability to deliver the oft-promised goods.

The OS/2 version seems minimally documented. A few of the crossed out sections in my proof copy of the manual made reference to OS/2 specific features, which have clearly moved into the common ‘feature pool’. Perhaps something of Microsoft’s attitude towards OS/2 might be inferred from this…

First published in Personal Computer World magazine, March 1991