Excel 4.0 for the Mac II

In this second installment of my review of Excel 4.0, I promised to tell you about some of the less glitzy features of the new version. I don’t mean to say that you should stop reading lest you get bored. We’ll see plenty of cool stuff here as well.

[Howard didn’t intend the review to go to three parts, but we ran out of space in this issue. I’ve tried to retain the sections dealing with the functional parts of Excel this issue, and next time we’ll look at user interface and output features. -Adam]

Errata — In the first part of this review, I complained that I wanted a way to turn Autofill’s brains off – so that it would mindlessly duplicate values into adjacent cells. I got my wish much sooner than I anticipated. Holding down the option key as you drag does the trick. Thanks to Ray at Microsoft for noticing my mistake.

I also gave an incorrect example for using Autofill to do a linear regression on a series of numbers. Doing an autofill after selecting 2, 4, 8 returns 10.67, 12.67, etc. I meant to say that doing an autofill on 2, 4 would return 6, 8, 10, etc., not 8, 16, 32, etc., since Excel does linear, not geometric autofills.

Macro Changes — The macro language has many nice changes, of which I find the changes to the debugging environment to be the most useful. Excel has always let you step through the macro one instruction at a time so you can watch what happens. Two new buttons show up in the Single Step dialog: Pause and Step over. Pause has saved me an immeasurable amount of grief. I often step through a macro and, just before coming to the crucial step, realize that I needed to make a little change. Before, I had to halt the macro, make the change, then start the macro up again. Now, I just hit the Pause button, make the fix, then hit the resume tool, which sits on the "Macro Paused" toolbar that Excel brings up immediately after you hit pause. The Step Over button allows you to let Excel run through a subroutine you’ve programmed correctly without having to look at each step in the process. After Excel hits the RETURN in the subroutine, it resumes single-step mode.

Direct copy and paste — The COPY and CUT macro functions now have arguments. No, they don’t fight – they allow you to specify the source and destination ranges. In one step, you can tell Excel where to copy from and where to paste to. Not only will this save tedious coding for activating and selecting, it also runs tons faster. If you have macros that work with the clipboard, rewrite them using this.

Global Macro Sheet — Finally, we have a macro sheet that opens every time Excel opens. When you record a macro, Excel will ask you where to store the commands – in a macro sheet you specify, or in the Global Macro sheet. Before Excel 4.0, I didn’t have a convenient place to put those little utility macros which make my life easier. I’ve also created a custom toolbar that includes tools to run most of my macros.

Other Macro Additions — Excel now has AUTO_ACTIVATE and AUTO_DEACTIVATE macro functions that specify which macro to run when the user activates or deactivates a given document. The ON.DOUBLECLICK function lets you change what double-clicking in a document means. These tools can help you create Excel spreadsheets that only a sophisticated user could distinguish from a stand-alone application.

Crosstab Wizard — Next in what will, no doubt, turn into a long line of wizards in Excel and other Microsoft products, we find the Crosstab Wizard. This remarkable electronic helper makes creating cross tabulated reports from your database information remarkably easy.

A crosstab report consists of a grid of numbers with row and column labels. If you had a database of sales transactions which had information on the product sold, total sale, as well as the sex and age of the purchaser, you could create a crosstab report where the column labels would indicate the sex of the purchaser, the row labels would indicate the age of the purchaser (either as separate numbers or in ranges you specify), and the cells would hold the dollar value of the sales for each category (or alternatively, you could have the crosstab count the number of sales in each category). This can be hard to visualize, but crosstabs are really useful.

Like the Chart Wizard I discussed in TidBITS-127, the Crosstab Wizard takes you screen-by-screen through the process, asking you what field to use for the row labels, column labels, and for the actual cell values. You can choose to sum, count, average, or even take the standard deviation of the reported values.

When Excel creates a crosstab report, it creates a new worksheet to hold the information. This report does not have a "hot links" to the database, so you need to recalculate an existing crosstab when the underlying data change. Once you’ve created a crosstab report, Excel allows you to double-click on any cell in the table, at which point Excel creates another new worksheet and automatically extracts all records from the database which meet the criteria you specify – a killer feature!

None of this comes quickly. Unless you have the fastest of Macs or the smallest of databases, you will spend some time drumming your fingers before you get results. I haven’t had any problems with the shipping version of the Crosstab Wizard (yet), but it bombed prodigiously and spectacularly while in beta, so I still feel a little wary about it – I always save my data before jumping in. Although the Crosstab Wizard works nicely, I still want the kind of live, multi-dimensional crosstab reporting and browsing available with Improv, MUSE, and other products.

Data Analysis — With Excel 4.0, Microsoft has added a number of powerful data analysis arrows to its quiver. For the business user who uses Excel for classic spreadsheet "what-if" analyses, 4.0 provides an excellent way to manage the different results generated from a model. With the new Scenario Manager, you can have Excel run a model you have through a number of different "what-if" scenarios with Excel plugging different values for different inputs and returning the end results. You can name each of the scenarios, and have Excel create a summary worksheet which gives you all of the inputs and the results produced by the model. Using Excel’s new Print Report add-in, you can also have Excel print each different scenario automatically.

For those of a more technical bent, the "Analysis ToolPak" add-in consists of a series of what I like to call "mini-wizards." These ask you everything you need to tell it to perform a statistical, financial, or engineering analysis – you can perform Anova, t-, and, z-tests, and even create one-step histograms.

Workbooks — If you link worksheets together, you probably know the remarkable stress that comes from the "Update references to unopened documents?" dialog box. Excel 4.0 allows you to "bind" as many documents as you wish together into one file, called a workbook. Under Excel 3.0, my company’s financial management system consisted of four separate worksheets (receipts, billings, assumptions, and a summary worksheet). With Excel 4.0, I’ve bound them all together into a workbook. Now I don’t have to make sure I open them in the right order. Plus, I only have to double-click on one icon, and I only copy one file.

You can also have "unbound" worksheets in your Workbooks. They will open just like bound worksheets, but you can change them outside of Excel. For instance, you may have a server which has today’s currency exchange rates in a Lotus 1-2-3 format. If you store it as an unbound worksheet in a workbook, the latest version opens when you open the workbook.

You can also use Workbooks to simulate 3-D worksheets (in fact, if you open a Lotus 3-D file in Excel, it becomes a Workbook). In practice, though, I find that Excel’s Data Consolidate feature provides a more flexible solution for summarizing data than 3-D worksheets.

Add-ins — Many of the new features I’ve described here don’t reside in the core of Excel code. They live on special macro sheets called Add-ins. Rather than put everything into the product, Microsoft chose to make Excel more modular and add major functionality this way. I applaud the decision to keep the core code of the product simple, and allow the user to add only what she needs. This technique saves RAM to make your "real" work faster.

This modularity also has a cost. When you choose the menu item for the first time, Excel opens up the add-in, then does whatever preparation the command requires, so after you let go of the mouse button, you wait… even on my Rocket-accelerated Mac II. I wish Microsoft could figure out a way to compile the add-in and create files like Word 5.0’s command files.

Add-ins also present the problem of possibly different configurations of Excel for different users. I can just see the help desk person saying, "Now just choose Crosstab from the Data menu…. What do you mean you don’t have Crosstab on your Data menu?!?" If you chose a minimal installation, you will have a significantly different program than your neighbor who loaded all 11 MB.

Charting Changes — Excel bulked up with a few new chart types for this version. These include the Surface chart (in 2-D and 3-D versions) and the Radar chart (really – they use it a lot in Japan). You’ve all seen surface charts, but radar??? In a radar chart each data category gets its own axis. I have seen this type of chart created manually in psychological profiles, where the test measures your level of intuition, or extraversion, or whatever, then plots each on its own axis. The radar chart connects the points into a polygon so you can see how vastly your spouse’s personality differs from your own!

In Excel 3.0, the Format 3-D View dialog box took the prize as the coolest feature in the entire program. Unfortunately, you will need to use that wire frame wonder much less often with 4.0, since you can now manipulate the 3-D view directly. Select the 3-D chart area, wait a moment, then click on it again. You will see a set of eight handles which correspond to the corners of a cube (or rectangular prism). Drag any of the handles and the 3-D view changes automatically.

Microsoft still needs to eliminate the distinction between worksheet and charting modes. I consider myself a power user (to steal a phrase, I actually consider myself a raging thunder-lizard of an Excel user). Even so, I get confused and forget that I have to double-click on a chart embedded on a worksheet so I can get at a certain menu item which exists only on the charting menus. Why not have the charting menu bar appear automatically when you have a chart selected, and have the worksheet menu appear when you click away. Heck, if Claris could figure that out for ClarisWorks, why not Microsoft for Excel?

Nice Additions

• When you select a cell or range of cells which you’ve defined a name for, the name automatically appears in the reference area (to the left of the formula bar).
• Double-clicking on a cell which contains a formula causes Excel to select ALL cells which it uses to calculate the value.
• The color tool on the formatting toolbar makes choosing colors for cells or objects much easier – each click applies the next color in Excel’s 16-color palette. (Shift-clicking moves backwards through the colors.)
• Nearly every dialog box in Excel now has a title bar, which means you can drag it around and easily jump to another application and a help button which summons context-sensitive help.
• Functions, Number Formats, and Toolbar tools are grouped into categories in their dialog boxes.
• If you type a function and the open parenthesis, then forget the arguments to that function, just hit Control-A and Excel automatically enters the argument names.
• Worried about making a spelling error in your worksheet for the Annual Report? Never fear, Microsoft has added a spelling checker to Excel. It doesn’t use the Word dictionary, but it can share user dictionaries with Word.
• If your Mac has a microphone, you can record sound notes in Excel and attach them to cells. With the macro language, you can have Excel play any sound in any file on your Mac.

[Stay tuned next week… -Adam]

Microsoft Customer Service — 800/426-9400

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