Numbers ’09, the spreadsheet component of Apple's iWork ’09 application suite, is the second major release of Numbers since its debut in iWork ’08. While the initial version of Numbers worked well for small projects and included some beautiful templates, I found it lacking in a few areas. Exports to Excel's file format lost formatting; there was no support for pivot tables, Excel macros, or AppleScript; scientific graphing options were limited; and large files could really slow the program down.

So how well does Numbers ’09 address these issues, and what new features does it bring to the table? And, is it now a full-fledged Excel replacement—or does that even matter? In short, Numbers ’09 is a solid improvement over its predecessor, addressing many of the outstanding issues while adding usable new features. Can it replace Excel? That depends on how you’re using Excel, but for many users, Numbers ’09 is probably now a viable alternative to Microsoft's program.

New templates

Numbers ’09 includes 12 new templates, most of them in the new Personal Finance category. These include financial templates, a home inventory tracker, and templates to help you save money for your kids' education and your retirement.

I found the new templates to be well designed and easy to use; yellow notes on certain complex templates explain exactly how to use them. Apple’s templates are also a good way to learn many of Numbers' unique capabilities—just take a look at the cell formulas to see how certain cells work.

New functions

Numbers ’09 features over 90 new functions, bringing the total function count to over 250. Technical types will love the new Engineering category, with over 20 engineering- and computer-related functions. A new Duration category lets you convert textual durations, such as 3w 1d 5h into actual time units (533 hours, using the DUR2HOURS function, for example).

Existing categories also gain new functions. NETWORKDAYS, for instance, shows the number of work days between two dates. Mathematical users will find MULTINOMIAL and FACTDOUBLE (and more) in the Numeric section.

But the most notable changes are in the Statistical category, with over 30 new functions, including AVERAGEIF, CHITEST, and many more. A new Function Browser offers greatly expanded help on every function. Where Numbers ’08 provided one line of text for a given function, Numbers ’09 not only offers longer explanations, but also adds sections that describe each variable in the function, usage notes, examples, and links to related topics. If that’s not enough help, there’s an extensive, detailed manual just for formulas and functions that you can access through the Help menu.

Adding functions to a table has gotten easier. As before, an Insert Function button places the formula on your table, complete with placeholders for the variables. In Numbers ’08, these placeholders were text, which had to be removed when inserting values. In Numbers ’09, the placeholders are clickable buttons. To enter a value, click on a placeholder, then click in the table or type in a value. (If you forget the function of a placeholder, hover your mouse over it to view an explanatory floating tooltip.)

When you click or type, the placeholder is replaced by a cell reference or your new content. This change makes entering formulas much faster than it was in Numbers ’08. A new Formula List feature displays every formula in your project, and shows you the cells used in each formula. You can print this view, which helps with both spreadsheet auditing (as you can see exactly which formulas reference which cells) and will aid students turning in assignments (as many classes require formulas to be visible). Unlike its predecessor, Numbers ’09 supports keyboard entry of formulas. Hold down the Option key before pressing one of the arrow keys while entering a formula and the selection will change as you press the arrow keys. Release the Option key to enter the selected cell reference in the formula.

Table categories

Numbers still doesn’t include support for pivot tables, a tool that automatically summarizes data from one table and displays it in another table, making it much easier to see hidden relationships in your data. However, the new Table Categories feature is a reasonable substitute, and is superior to pivot tables in some ways. Table Categories give you a simple way to group columns. Once grouped, you can add totals, averages, counts, and other summary data. Consider a roster of potential bowling team members: In its raw form, it’s not very usable because there’s so much data to look at. Create table categories for the Team and Handed columns however, and the data is much easier to read and interpret.

Categories and sub-categories are created via a pop-up menu in the column header. After assigning categories, you can rearrange sections of your data via drag and drop. If you modify your data—change a person’s team, for example—that entry will automatically move to the proper section, and all summary data will update. Table Categories work well, are very simple to create, and for many users, they provide all the pivot-table functionality required.

New charting features

While Numbers’ charts have always looked good, Numbers ’08 lacked many options used by financial, scientific, and statistical users. Numbers ’09 fills most of these holes with its new mixed charts, two-axis charts, and the ability to add trend lines and error bars to charts. The mixed chart type shows two different charts on one page—chart sales targets as a line, with bars used to show actual sales, for example. The two-axis chart is similar, but it’s used when the data being presented require different scales—calories burned and miles run, for example. Trend lines can be fitted to a chart in a few seconds with a few mouse clicks, using several different methods of fitting a line to your data. Error bars work in much the same manner as trend lines. Error bars can be set as positive, negative, or both. The error range can be set as a fixed value, fixed percentage, standard deviation, or standard error.

Once you’ve created the perfect chart, you can embed it in a Pages  ( ) or Keynote ( ) document. This isn’t just a copy-and-paste operation, as a link is maintained between the embedded chart and its source in Numbers. Unfortunately, the link isn’t “live”—if changes are made in Numbers, the chart must be selected and updated in Pages or Keynote. Only charts, not any other sort of Numbers data, can be linked in this manner. Overall, charting in Numbers is greatly improved in this version, though Excel still rules as the charting powerhouse—there are over 70 chart types in Excel 2008, versus only 19 in Numbers.

Large files

To see how Numbers handles large data files, I used an 8MB test file that consisted mainly of thousands of numbers, with one SUM column. Numbers ‘08 took 53 seconds to open this file on my 2.66GHz Mac Pro; Numbers ’09 dropped that time to 32 seconds. However, that’s still a far cry from the five seconds it took to open the same file in Excel 2008. Working with a large file in Numbers is frustrating. Because Numbers lacks a way to turn off recalculation, every time you change a cell, the entire worksheet recalculates, leading to lots of delays. Trying to drag the scroll bars in a large file is frustrating; there’s lots of lag and jumpiness. Excel 2008 exhibits none of these issues, and even when recalculating automatically, it does so much more quickly than Numbers. If you work with large data files whose contents cannot be easily broken up across separate sheets and tables in Numbers, Excel 2008 is still a better solution than Numbers, though the gap has narrowed a bit with this release.

More enhancements, some frustrations

Minor new features abound throughout Numbers ’09. Three new cell formats have been added—Duration for time-based cells, Numeral System for converting numbers to different bases, and Custom, for custom cell formats. Rows and columns can be frozen, so they don’t scroll off the screen, and there are a few more icons available when customizing the toolbar. If you’ve got a newer laptop, Numbers even supports multi-touch trackpad gestures for flipping between sheets. Numbers now supports AppleScript, but since there’s no way to record your actions in Numbers, you’ll need to know AppleScript to use this feature.

There’s still no support for Excel macros; if you open a macro-enabled Excel file, then save it back to Excel, the macros will be lost. Saving a Numbers document in Excel format works well if the Numbers document is a simple table. Saving a complex Numbers template to Excel creates a usable file, but one that will require lots of formatting work in Excel. That’s because each table in Numbers becomes a new worksheet in Excel. You can also export Numbers files in CSV (raw data) and PDF format.

Minor annoyances from Numbers ’08 persist in this version. The Find & Replace feature still won’t work on a selection (though you can now limit replacements to the current worksheet, entire document, or formulas). Sorting still affects all columns, regardless of the selection, making it tricky to sort a portion of your data. You can now protect a Numbers file with a password, but you can’t lock cells within a table (to prevent modification). Finally, if you change the value in a cell and then undo the change (Command-Z), Numbers will actually erase the cell and leave it blank. To get the original value back, you need to press Command-Z twice. More than once, I’ve been caught by this feature—Apple confirms this is intentional behavior—and wound up with empty cells in my models.

Macworld’s buying advice

So is Numbers ’09 a perfect replacement for Excel? No—power users, charting wizards, and those who work with large data sets will still find Excel the superior tool. For everyone else, however, Numbers ’09 is a compelling alternative. The new functions, Function Browser, Formula List feature, Table Categories, and charting options eliminate many of the weak points in the first version of the program. At only $79 for the entire iWork bundle, Numbers ’09 is also an incredible value, delivering features well beyond its price.

[Rob Griffiths is a Macworld senior editor.]