Office shootout: OpenOffice.org Calc vs. Microsoft Excel

In earlier articles, I compared OpenOffice.org 2.3’s and MS Office 2007’s word processors and slide show programs. It seems appropriate to round off the comparison with a look at spreadsheets, the third of the core programs in any office application.

I spent a couple of days testing OOo Calc and Microsoft Excel features for formatting, list-making, formulas, and formula tools. Since the range of user expertise in spreadsheets tends to be much wider than in slide shows or even word processors, I considered the programs largely from the perspective of an average user who might require a spreadsheet for home or business use.

Cell, sheet, and page formatting

Calc’s and Excel’s options for formatting cells are so similar that their tabs are in the same order. The main difference in options is in number formatting, where Excel offers a Special category for phone numbers, social insurance numbers, and zip codes, and Calc offers Boolean values, as well as settings for decimal places, leading zeroes, coloring negative numbers red, and the thousands separator — values that Excel tucks away under the Excel button in the ribbon. Calc also has an option not only for text wrap within cells, but also for hyphenation, which may be useful to those who use spreadsheets primarily for making lists. Otherwise, the cell formatting options are virtually identical for format, font, border, background, and protection, differing mainly in names.

Both programs include themes for formatting whole sheets, and, in both, you can also click the upper left corner of the sheet to select all its cells before you apply formatting changes. Page formats for printing are similar, too, including similar options for fitting spreadsheets, which are primarily intended as online documents, onto paper.

However, although the options are similar, Calc’s treatment of cells and pages as styles is not only more convenient, but also similar to the functionality of other programs in OpenOffice.org. In particular, the Styles and Formatting floating window is especially efficient for designing. By contrast, Excel’s basic formatting options are more chaotic to manage.

Both Excel and Calc also provide for inserting charts, diagrams, graphics, and text art into spreadsheets. The development of these extras seem so closely connected — first one program getting a feature, then another one — that there is little to choose between them. Right now, they seem approximately equal.

Verdict: Calc, because of its emphasis on styles.

List-making and sorting

List-making is one of the most basic uses of a spreadsheet. Both Calc and Excel have similar basic functionality, with custom fills for automatically entering patterned information, and pivot tables (or “datapilots,” to use Calc’s term) to rearrange information in order to gain new insight into it. The two programs also allow you to group rows and columns so that they can be shown or hidden with a single mouse click, although, if you didn’t expect such functionality, you could easily miss it in Excel’s new ribbon layout that combines menus and toolbars into one.

In both, too, you can sort data and filter it, either by custom filters or an autofilter, which creates a drop-down list of possible filters at the top of a column. However, Excel has an advantage because of its advanced filters, which allows cells to be arranged by things like format, or color, or whether a cell’s content matches the average in the column, or is above or below the average.

Verdict: Excel.

Functions and formulas

Functions are the equations and expressions that, for advanced users, are the main point of a spreadsheet. A comparison of Calc’s and Excel’s functions (in French) of earlier versions of the programs shows that better than 80% of all functions are identical in the two programs, including all the most-used ones for basic arithmetic and simple statistics such as averages, medians, and means. In the remaining 20%, Calc has features that Excel lacks at least twice as often as Excel has features that Calc is missing. This situation seems unchanged in the latest versions.

The main difference lies in how functions are inserted. To enter functions, both programs have a function bar at the top of the editing window that opens on a list of functions. Excel’s list includes a handy search field, in which you can enter natural language queries if you choose — although with mixed success. Only after you have searched for a function does Excel open the Functions Argument dialog. Alternatively, you can choose a function from the separate icons for each category of functions on the Formulas tab of the Excel ribbon, although the inability to move between them means a delay if you click the wrong icon.

In comparison, Calc opens directly to its function wizard. The function wizard could use a search field like Excel’s, but has an advantage over Excel’s Function Argument dialog in that it always lists required fields, lists errors before you insert a function into a cell, and offers a tree view of the formula structure that is invaluable for users composing complex formulas. For those who require less help, Calc offers the more stripped-down Function List.

Verdict: For most users, Calc. Excel might be preferred by advanced users who need a particular function that Calc lacks.

Function tools

While Excel’s ribbon works well enough for most purposes, it obscures many of the tools for working with functions once they are inserted on a sheet. You can find the analog of Calc’s Detective function on Excel’s Formulas tab in the form of the Trace Precedents and Trace Dependents option, but you will probably need to resort to the online help before you locate Goal Seek, Subtotals, or Validation. In fact, if you are not an experienced spreadsheet user, you might not even guess that they are there. Calc’s arrangement of these options between the Tools and Data menus is not perfect either and seems equally arbitrary, but has the advantage of being familiar to advanced users who want the options, and discoverable by new users.

Verdict: Tie. The functionality is much the same, but neither arranges the tools in very logical order.

Choosing a winner

I have heard — but lack the knowledge to verify personally — that Calc tends to be slower than Excel when performing advanced calculations. However, if true, this complaint does not apply to the needs of the average home or business user, most of whom rarely dabble in advanced physics or mathematics on a daily basis.

Users of either program may consider a minor feature not considered here as absolutely essential. A particular keystroke combination or formatting option may seem trivial to others, but if it’s part of your workflow, you are unlikely to give it up easily.

However, for most functions that the average user is likely to want, there is no major functional reason for preferring either Calc or Excel over the other. While OOo Writer outperforms Microsoft Word, and Microsoft PowerPoint offers more features than OOo Impress, with the spreadsheets, there is no clear winner. Even when one has an advantage over the other, it never seems to be a knockout victory. The one recurring theme seems to be that functions are more accessible and logically arranged in Calc, and that advantage may be due to the switch in Microsoft Office 2007 from menus and toolbars to ribbons.

Although individual preferences are always a factor, chances are that, in opting for Calc and free software, you are not putting yourself at any functional disadvantage. In fact, depending on your exact needs, in some ways, you may find yourself ahead.