Excel 4.0 for the Mac I

A new version of Excel already? I thought they just came out with one a few months ago! Microsoft has taken about 13 months to move from Excel 3 to 4 on the Mac side. Almost everyone I’ve talked to about the new version has wondered if Excel 4 is a major upgrade and if they should buy it. Well, it is, and if you use Excel a lot, you probably should send Bill Gates some more money.

With this upgrade, we see the fruits of two major forces at work at Microsoft. Most importantly, they’ve started to get some legitimate (read Lotus) competition on both the Macintosh and Windows platforms. In addition, Microsoft started having a tougher time figuring out new features to add to Excel. The upgrades now focus on usability, rather than adding a new feature that most folks don’t want and will never use.

The focus from above does not mean that Microsoft hasn’t added new features for the ubiquitous head-to-head comparisons you see in the magazines. Hundreds of new functions (statisticians will rejoice) combine with new reporting and analysis tools.

Due to the short turnaround for this upgrade, Microsoft has lowered the price from the now-standard $129 to $99. The price will go up to $129 in September.

In this first part of the review, I will discuss the day-to-day "user" features which Microsoft added to Excel 4. I’ve found I use these features most and they make me more efficient.

Installation — First, though a few words on installing and running this behemoth. The "Golden Master" beta I got of Excel 4 (which has just shipped, according to Microsoft) came on seven 800K disks. (Excel 3 shipped on three disks.) If you do a full installation, it takes up 11 MB on your hard drive. Microsoft seems to have fed it steroids over the past year, but thankfully it does not appear to have succumbed to the brain cancer that recently killed Lyle Alzado, the former pro football player.

You don’t have to give up 11 MB on your drive to take advantage of Excel 4, but you probably will lose about 7 or 8 MB if you want the help system and new macro libraries installed.

The installation follows the standard "Insert disk 1, 2, …" model. While I haven’t tried it, a button appears which allows you to install the installation files on your network. This should save you from having to flip floppies hundreds of times.

After you insert each disk, an information screen comes up which variously tells you that you should fill out your registration card RIGHT NOW, as well as giving short descriptions of some of the new features that you should check out. I found the screens an informative addition to the standard progress bar.

Running Excel — Out of the box, Excel asks for 1.5 MB RAM, but can run with as little as 1 MB. To run smoothly with medium-sized worksheets, I’ve found that it needs at least 2.5 MB. Just as with charity, give as much as you can spare, remembering especially that the add-in extras in 4.0 all take up some RAM.

The program certainly takes up more disk and RAM space and seems to run just a little bit slower on identical tasks in identical situations. However, using Excel 4 pays off in little ways. Once I got used to the new features like Autofill, Shortcut menus, Autoformat, the Chart Wizard, and Toolbars, I completed work much faster with the new version. I’ve worked with the beta of Excel 4 at my office and Excel 3 at all of my client sites for about three months now and feel much less efficient working with Excel 3. The new version lets me do simple and repetitive things much faster and with a lot less effort.

Autofill — When I started Excel 4 for the first time, I immediately noticed a little black square in the bottom right corner of the active cell. Anyone who’s used any graphics program on the Mac would recognize it as a dragging handle. Microsoft calls this the Autofill handle. When you drag it with the mouse, it fills the information in the current cell or range of cells horizontally or vertically into adjacent cells. Before, to fill a formula or value down into a range of cells, you had to select the cells, then choose Fill Down. With the Autofill feature, you can do it in one step – select the first cell, then drag the Autofill handle down. Bang, instant fill.

Autofill can do either a dumb fill or a smart fill. If you drag the fill handle across to fill a totals formula across a row, Excel performs a dumb fill. If your cell has an entry which Excel recognizes, it will do a smart fill. Smart fills have the potential to save more typing than just about anything I can imagine. Type "January" in a cell, then drag the Autofill handle across. In the reference area on the formula bar, you’ll see the months of the year ticking along as you drag from one column to the next: "February", "March", etc. When you let go of the mouse button, Excel fills the month names across the columns automatically.

Excel recognizes days of the week, quarters, dates, and almost any pattern you throw it. For instance, if you type "1st Quarter", then drag the fill handle, Excel fills in "2nd Quarter", "3rd Quarter", "4th Quarter", then "1st Quarter" again. If you typed "1st Product" instead, Excel would act similarly, but would enter "5th Product" in the fifth place.

By selecting more than one cell and then dragging the fill handle, Excel will reproduce patterns as best it can (and it usually does very well). For instance, selecting cells containing "Mon" and "Wed", then dragging the fill handle results in: "Fri", "Sun", "Tue", "Thu", etc. It skips a day each time. If you select a series of numbers: 1, 6, dragging the fill handle gets you: 11, 16, 21, 26, etc.

Excel always assumes a linear series for Autofill, so 2, 4, 8 gets you 10, 12 not 16, 32. If you select more than two cells which don’t have a linear progression when you Autofill, Excel creates a linear regression and fills in extrapolated values. For instance, if you select 1, 6, 13 and drag the Autofill handle, Excel fills the values 17, 22.5, 28, 33.5.

Autofill also recognizes mixed patterns. To create a standard 17 column table (12 months, 4 quarters, 1 total), simply type "Jan" and Autofill "Feb" and "Mar" across. Then type "Q1" in the next column. Put in your category labels down the side, then use Autosum and Autofill to put the totals and sub-totals in. Now select "Jan" through "Q1" and down through the totals. Drag the Autofill handle 12 columns to the right, and Excel will automatically put the right labels on the columns for the entire year and fill the subtotal and total formulas automatically. Cool stuff!

I would like to have the ability to make the Autofill temporarily "dumb". I’ve found myself entering dated transactions into a database and wanting it just to fill the same date down, rather than incrementing by one day – the default. I would also like to create my own Autofill patterns so I could enter the first item in the product line and have Excel know my business enough to fill in the rest.

Autoformat — If you use Excel a lot, you’ve probably spend at least half of your worksheet-creation time formatting the darned things. Autoformat gives you the convenience and efficiency of one-step formatting.

With Autoformat, you simply click somewhere in the middle of the table of data you want formatted, then click the Autoformat tool. Excel selects the entire table, formats the labels, data, and totals with appropriate formats.

Microsoft has chosen 14 different Autoformats. The categories include: Classic, Financial, Colorful, List, and 3-D Effects. Unfortunately, Microsoft seems to value color and glitz over substance in these choices. For those of us who print our worksheets on black-and-white printers, you can only read about half of the formats easily when you print them. Since I expect impeccable printouts, I can only recommend two of the Autoformats: Classic 1 and Financial 1. Complain as I may, this feature has saved me a bunch of time.

Drag & Drop Editing. — The current selection in Excel now has a (somewhat heavier than before) border around it which allows you to drag the selection anywhere you want on the worksheet. When you point at the edge of your selection, the mouse pointer will change from the standard heavy cross to an arrow. When you see this arrow, you can drag the selection around by its edges.

Simply dragging a range of cells mirrors the cut and paste commands. Dragging with the option key held down does what you might expect – it duplicates the selection in the drag destination. Holding the shift key down when you drag performs a cut and insert paste. Shift-option dragging mirrors a copy and insert paste.

While I use this a lot, I’ve found it all too easy to drag cells around inadvertently. New users especially might have problems and find cells in the wrong places. Luckily, you can turn drag & drop off from the Workspace dialog box.

Shortcut Menus, or why I have PC-envy — With its myriad features, Excel can overwhelm you with choices. To partially alleviate this problem, Microsoft has added shortcut menus, which give you only the most used commands for your current situation. When you hold down the command and option keys and click the mouse, Excel brings up a pop-up shortcut menu right next to your mouse pointer. Select a range of cells, command-option click, and Excel allows you to instantly cut, copy, paste, clear, delete, or insert, as well as change number, alignment, font, border, or patterns formatting. This saves the trouble of mousing all the way up to the menu bar, finding the right option and choosing it. (I find our ever-increasing computer laziness quite wonderful!)

For most things, I favor the keyboard or the toolbar over the shortcut menus. I do use them a lot for displaying toolbars and with workbooks (more on those later). The main detriment to using them comes from the keyboard and mouse combination, which usually seems harder than doing it the old-fashioned way.

Why do I have PC envy? If you’ve used a PC mouse, you know that they have more than one button – either two or three. What did those other buttons do? Not much… until now. With Windows Excel 4.0, clicking the right mouse button brings up the shortcut menu – no command-option for PC users.

Toolbars — Microsoft added a toolbar just below the menu bar in version 3.0. This toolbar contains a number of icons and a drop-down menu that allow you issue commands with a click of the mouse instead of a trip to a menu and a dialog box. While this made certain tasks easier to perform, 3.0’s toolbar has many limitations.

In implementing toolbars for version 4.0, Microsoft seems to have taken every feature from every palette program in existence and put them all into Excel, which now contains a "well" of 160-odd tools (not "buttons"!). When you click the Customize button (not "tool") in the Toolbars dialog box, Excel presents you with an array of tools. As with Format Number and Paste Function, this dialog box shows you tools grouped by category and function. To add the oval tool to your standard toolbar, simply choose the Drawing tools category, then drag the oval tool into position on the toolbar. Nothing to it. To remove a tool from a toolbar, simply drag it off.

To move a toolbar, simply drag it. In Excel 3, the single toolbar has to sit at the top of the screen. Now tool bars can go anywhere. If you drag them into the middle of the screen, they turn into floating palettes like those in PageMaker or HyperCard. You can resize the floating toolbars to make them tall or wide. Drag the toolbar to any edge of the screen and Excel re-orients it and "docks" it flush with the edge of the screen. The standard toolbar uses this feature – you see it docked at the top of the screen, although you can drag it anywhere!

You can attach a macro to any tool on a toolbar. If you do, the macro will override the original function of the tool. Excel comes with a score of tools with faces, but no function attached – you just tell it what macro to run. You can create a tool face in your favorite graphics program and paste it onto any tool face, custom or otherwise.

Excel comes with nine pre-defined toolbars, including: Standard, Formatting, Utility, Chart, Drawing, Excel 3.0, and Macro. You can change the tools on each bar, and if you do Excel remembers the changes from session to session. If you’ve totally destroyed a built-in toolbar, you can click the Reset button and it returns to its original state.

Microsoft has left out only two major toolbar features in Excel 4. First, they didn’t include a painting program, so you can’t directly edit the bit map of the tool face within Excel. (I find this outrageous! A spreadsheet without a painting program!) Second, you can’t save toolbars separately – Excel creates a file called Excel Toolbars inside the System Folder. This file includes information on all of your toolbars. If you put together one cool toolbar and sent your toolbars file off to a coworker, when she replaces her toolbars file with yours, she loses all of her own toolbar modifications – bummer. I have one other complaint with toolbars – screen real estate. My once-expansive 13" monitor shrinks to a size I can barely use if I have more than one toolbar docked. I don’t expect Microsoft will recommend a 16" monitor on the back of the Excel box, but it’s gotten to the point where I might.

The Chart Wizard. — With Excel 3, Microsoft added the ability to place worksheets directly onto charts. This added a new level of complexity, because you had to go into a different mode to edit the chart. Excel 4 retains that need for a charting mode, but by creating the Chart Wizard, Microsoft made it a lot more likely that you’ll never use it.

To chart data with the Chart Wizard, you simply select the titles and data you want charted, click on the Chart Wizard tool, then drag a rectangle on the worksheet indicating where you want the chart to go. Now the Wizard comes into play; it comes up to ask you a series of questions – almost everything you need to create a chart. Five screens appear in sequence, asking you about the data range you selected, which cells correspond to categories and which to data points, what type of chart you want, whether to add a legend or title, etc. The final two screens present you with a small picture of what your chart will look like. The Charting Wizard takes into account the fact that you might make mistakes, allowing you to move backwards to change your choices.

In the love it or hate it department, whenever you click on a chart, the charting toolbar appears docked at the bottom of the screen. While this makes changing your chart simple, I often find it visually annoying, especially on smaller-screen Macs, since Excel often has to resize the worksheet window when it displays the toolbar. Nonetheless, I’ve found it wonderful to change the chart type with just a click.

Coming attractions — Next issue I’ll cover some of the new Excel’s less-glitzy features. While you might not use these each time you launch Excel, many of them will make your life significantly easier. I’ll also list a series of those "little touches that mean so much," which you’ll love about Excel 4.

Microsoft Customer Service — 800/426-9400

Information from:
Howard Hansen, The Oasis Group — [email protected]
206/282-6255