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

Advertisements

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

Bill Gates Interview

Bill Gates founder and President of Microsoft

Interview by Glyn Moody

Bill_Gates

Programming has always been central to Bill Gates’s life. He took a programming job after leaving school and before going up to Harvard. He left university after only two years in order to set up Microsoft in 1974. Gates was also actively involved in the writing of code for Windows and adapting MS-DOS for the IBM PC. Today, looking even younger than his mere 29 years, Gates presides over a company whose latest turnover is expected to top $140 million. He and his cofounder Paul Allen remain the majority shareholders.

How are things going to pan out between the Mac and the IBM PC?

I think that’s very clear: the Mac will be number 2. The Mac plus Excel is a far superior solution to the IBM plus 1-2-3. And until you have serious software, a machine is not a serious machine, despite the power and ease of use the Mac has brought. A year ago they had no software, now they’ve got a ton of software and that’s helping a lot. But they need a few milestone packages which push it to the point where a guy who works with numbers says, “Look, get me a Mac, because the Mac with a LaserWriter lets me do my job in a far better way than I could do in the past.”

In what way do you think Excel moves beyond the previous generation of packages?

Our claim is very simple: it’s the world’s greatest spreadsheet, it’s the best way of working with numbers. And that was our very straightforward goal in doing the thing.

How do Topview and Windows sit together?

Topview is a very nice utility that allows you to run multiple applications. I don’t know of a single software company that’s writing applications that require Topview because there’s really nothing Topview lets you do that’s unique. It’s not compatible with the network, it uses up a lot of memory, it doesn’t use batch files: there are some limitations.

Windows happens to run multiple applications, but Windows is a sub-system that supports graphics, and a graphics user interface. So unless you like the graphics applications we’ll be including in with Windows, then you shouldn’t buy Windows.

What has been the problem with the release dates of Windows?

When you’re building the foundation you’re telling everyone to put their application on top of, it’s a very significant responsibility to get the thing small and fast and good. We underestimated how tough that was going to be to get it exactly right. We’ve had good feedback from software developers in terms of what they really want, and speed and things like that. Also there were some things about “should we work with old applications?” and we decided we should; “should we work without a mouse?” and we decided we should. That’s one of the more fantastic features we’ve put in. In our case it’s a graphical user interface that doesn’t require the mouse to use the menus although it supports it very fully. And it’s a very substantial system, not only Windows itself but the development tools that go with it. We underestimated the process.

How important for Microsoft is networking?

We have two real thrusts. One is graphics, the other is networking. Networking has gone so smoothly and we’ve gotten so much support that it’s probably gotten a little less attention. But it is one of our greatest successes to have people like IBM, ACT and HP – almost everyone is behind MS-Net – and therefore having all the software developers using the MS-Net protocols. It’s gone super well for us. The key market right now that DOS machines sell into is the office market, and in the office market, all the machines will be networked eventually.

How do you see that squaring with the multi-user capacity?

There is obviously some trade-off when you’re solving a particular problem. In the long run, as people want to use graphical applications we think that a single user, networked, will be the dominant approach. But we see a very significant role for multiuser systems like 286-based systems running Xenix where the cost per terminal is much lower, and the ease of setting things up and controlling the data is far greater today than it is in the network case. So that’s a market that will flourish. Because the 286 chip is really great: it’s the first inexpensive microprocessor that has the performance, and the memory management. Coupled with a 20Mbyte hard disc it’s the first popular machine that’s adequate for Xenix and Unixtype applications. Because we got IBM to announce Xenix we’re going to get applications momentum behind Xenix. It’ll get it to critical mass.

Do you think Xenix will take off now?

Over 70 percent of the Unix systems in the world today are Xenix so we’ve done very well with customers like Intel, Radio Shack and Altos. But even so it’s fallen short of market predictions. I think the 286 will help that, and IBM’s involvement will help that. We need to get up to like 400,000 systems in the next year-and-a-half to make sure that the software companies involved make a good living in Xenix applications.

How do you see PC-DOS developing in the future?

I’ve talked about multi-tasking, and I’ve talked about Windows being on top of that, and there are some extensions we can do in the network area. Another key thing is to track the Intel chip developments, the 286, and then the 386. That, in whole, is a full set of activities for the next three years.

First published in Practical Computing magazine, August 1985