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