May 12, 2005

FOSS spreadsheet hands-on comparison

Author: Bruce Byfield

Rumors about the capabilities of free and open source spreadsheets are common among office software users, but not all of the rumors are backed by hands-on experience with the products. To get a better sense of the current state of FOSS spreadsheets, I installed the latest versions available for Debian: build 1.9.83 of the 2.0 beta (for Calc), KSpread 1.3.5, and Gnumeric 1.4.3-6. Some of what I found contradicts the common wisdom.

I examined their functionality in categories meant to reflect the majority of office users' needs:

  • list-making and display
  • functions
  • formulas
  • formatting
  • charts
  • computational speed

In each category, I examined functionality and ease of use. The results present a more complex, less static, and less well-ordered state of affairs than popular opinion would lead me to expect.

List-making and displaying

Filtering and sorting are two basic functions for lists in spreadsheets. Filtering determines which cells are displayed, sorting the order they in which they are displayed.

For filtering, Calc and Gnumeric offer similar functionality, giving a choice of filters that are a combo box (auto), listed elsewhere on the sheet (advanced), or defined (standard in Calc, or custom in Gnumeric). Custom filters, however, are available in Gnumeric only from the combo box of an auto filter. KSpread, surprisingly, does not seem to have any filtering at all, nor does it have any sorting capacity beyond arranging cells in ascending (A-Z) or descending (Z-A) order. By contrast, both Calc and Gnumeric allow sorting by multiple column criteria. Each offers three sort criteria by default, but more can be added in Gnumeric. Both also allow case sensitivity in sorting, and Calc offers additional options such as the recognition of column labels to keep them from being part of the sort, custom sorts based on pre-defined fills, and the language of the cells to use in the sort.

All three programs have automatic cell filling, which detects a pattern in cell data such as the days of the week or an incremental pattern in numbers, then uses the pattern to fill other cells when you drag on the lower right corner of the first cell. In Calc and KSpread, you can define fills and add them to the patterns that the program can detect -- a feature especially useful when creating templates. In addition, KSpread has a Series tool in the Insert menu which can be used to define numerical patterns. Gnumeric, though, not only does not allow custom fills to be defined, but recognizes fill patterns only when functions are used.

All three programs let you hide rows or columns, so that non-adjacent data can be viewed together or irrelevant data removed from view. Calc and Gnumeric also have outline tools, which group individual cells together for hiding and revealing by means of buttons in the top or left margins. Other tools for manipulating spreadsheet display in Calc and Gnumeric include the option to freeze a row or column so that it is unaffected by scrolling. Calc can also split the screen to provide multiple panes, each with its own scrolling bars.

Verdict: While each program has features for list-making that the others lack, Calc has the widest range of features and the most options. By contrast, KSpread has yet to pay much attention to list-making as one of the main uses of spreadsheets.

Basic function tools

For intermediate and advanced users, functions are the most important features in a spreadsheet. You can get some idea of how developed each spreadsheet is simply by counting the functions available. My count was 368 for Calc, 311 for KSpread, and 464 for Gnumeric. However, numbers do not tell the entire story. For one thing, all three spreadsheets are more than adequate for the majority of users, who need little except simple arithmetic and possibly basic statistical functions such as averages and medians. It is mostly high-end users who might miss the inclusion of other functions. And, for another thing, how functions are entered and what tools support them can be just as important as the functions themselves.

In addition to an input line below the toolbars, all three spreadsheets offer graphical tools for adding a function to a cell. In fact, Calc offers two: The Function List, a floating tool bar that docks on the right side of the editing window and provides a summary of functions, and the Function Wizard dialog. Most people are likely to opt for the Function Wizard, which guides users through the process of building each function, and lets them know whether each parameter entered is valid before they apply a formula. Conveniently, the Function Wizard also scrolls up so that users can select cell ranges from the spreadsheet itself.

Unfortunately, Calc's Function Wizard has a number of weaknesses. When a user makes a mistake, the program reports error codes rather than explanations of exactly what is wrong with the input. Similarly, the help available is frequently too concise. Admittedly, more detailed information is available in's help utility, but for new users especially, even this help may not be enough. Nor does Calc mention which functions are compatible with Microsoft Excel, a concern that many users may have.

In comparison, both Gnumeric's Function Selector and KSpread's Functions window have more detailed help built-in, including examples and notes on Excel compatibility. Both also give some help in entering parameters, although in both cases it is less detailed than in Calc, and does not pre-warn about errors. Moreover, Gnumeric's Function Selector window does not scroll automatically to allow cell selection, while whether KSpread's Function window does depends on the window manager rather than the spreadsheet program.

All three spreadsheets divide functions into categories to make them easier to find. Gnumeric and Calc both maintain separate lists of recently used functions. Although all the categories are similar, Gnumeric's categories are the most useful, mainly because there are 16, compared to the other programs' 12. The extra categories may not seem like much, but they are enough to make tracking down functions easier. To a greater or lesser extent, all categories in all the spreadsheets seem based on Excel's.

Verdict: This category is close, with none of the programs winning in all aspects. Gnumeric, however, takes the top spot because, while its Function Selector is not quite as handy as Calc's Function Wizard, its help, function categories, and sheer number of functions surpasses Calc. KSpread is in the third spot.

Next: Formula tools

Formula tools

When a user enters a function into a spreadsheet, it becomes part of a formula. Of the three programs, KSpread has the fewest tools for formulas. They are Consolidate, for placing data from different sheets into a single area; Goal Seek, for calculating what input will give the desired answer for an operation; and Subtotals, for quickly adding totals. Otherwise, KSpread is somewhat lacking in function tools.

Gnumeric and Calc each have versions of KSpread's formula tools, although under slightly different names. But in addition, both have scenario tools that insert combo boxes of variables. Beyond that, they diverge sharply from each other.

Beyond the basics, Gnumeric tends to specialize in tools for mathematical analysis. Its formula tools include Solver for linear systems of equations, and the self-evident Simulation and Statistical Analysis. By contrast, Calc's formula tools are more general-purpose. Standouts among Calc's tools include Detective, which gives a color coded view of which cells were used as parameters for a functions, and Datapilot, Calc's answer to Excel's Pivot tables, which rearranges existing data to show new relations to it.

Verdict: In this category there's a tie between Gnumeric and Calc -- which one you prefer will depend on your purpose. As with list tools, formula tools are an area that KSpread needs to develop.

Formatting options

Spreadsheet formatting falls into two main categories: formatting for cells, and formatting for printed pages. Cell format can cover any number of cells, including an entire row, column, or spreadsheet, while page formatting sets the layout of hard copy. In both categories, spreadsheet formatting is often manual, although both Calc and KSpread implement styles and templates. Gnumeric, by contrast, contains no concept of styles at all.

All three spreadsheets offer a similar range of manual format options; the main difference is in the arrangement of some features. For example, Calc does not include Protection or Validation as options for formatting, the way Gnumeric does, placing these features instead in the Tools and Data menus, respectively. Similarly, KSpread includes Protection in cell formatting, but includes no tool for validation.

Another difference is that, while all three spreadsheets include the option of wrapping text so that it stays within the boundary of a cell, their implementation of the feature differs. Only Calc offers text wrap with hyphenation. KSpread offers text wrap, but no hyphenation, while in Gnumeric, text wrap means that only as much text as can be displayed on a single line is visible until you click on the cell.

The concept of cell styles is most developed in Calc, which comes with 17 pre-defined styles, as opposed to KSpread's two. The closest Gnumeric comes to styles is Format > Autoformat, which offers a variety of pre-set formats for selected cells. Calc contains a similar feature called Choose Themes which is not available on the default toolbar, but can be added from Tools > Configure. Neither Gnumeric's Autoformat or Calc's Choose Theme tools, however, allow users to add new formats or themes to the list.

Page formatting in the three spreadsheet consists of options for the text and design in headers and footers, and for how spreadsheets (which have no set height or width), are fitted onto a particular page size. All three allow users to set text for the left, right, and middle of headers and footers, but only Calc includes options for setting dividing lines, backgrounds, and drop-shadows. Each spreadsheet also allows the scaling of information to fit it on the page, as well as choosing cells to be repeated on new pages. In addition, for occasions when the columns selected are too wide to fit a page size and orientation, Calc and Gnumeric include the option of choosing whether a sheet is printed from top to bottom first, or from left to right.

Calc has no predefined templates, but, like the rest of, is designed with the assumption that templates and styles will be part of your workflow. Calc templates are also available from the OOoExtras site. KSpread comes with 12 templates divided into Business, General, and Home and Family categories. Gnumeric does not use templates, any more than it does styles.

Verdict: For features, styles, and templates, Calc is the clear winner. The other programs are strong contenders, but which finishes second depends on your priorities. In terms of features, Gnumeric takes second place. In terms of styles and templates, KSpread is second. Call this category a dead heat for second.

Next: Charts and computation speed


Each of the three spreadsheets has a charting wizard that guides users through formatting a chart. Each allows extensive customization, although Gnumeric offers less than the other two and does not allow users to select a new chart format once they've chosen one. Both Gnumeric's and Calc's default colors are uninspired, and show jagged edges when enlarged. Neither is a match for KSpread charts, which have richer default colors and a more polished 3D look. Nor do they degrade when enlarged.

Verdict: KSpread tops this category. The other two spreadsheets require more tweaking of charts to get an acceptable look, and much of the time the results still don't look as good as KSpread's. Calc takes second place for its greater variety of options than Gnumeric.

Computation speed

To test the speed of the three spreadsheets, I created three columns of 2,000 rows. In the first row of the first column, I entered 0. In the second row, I entered the function SUM(A1_ 1.10). The second column contained the cosine of the numbers in the first row, and the third column the sine. I then changed the number in the first row of the first column and waited to see how long each program took to update. All the spreadsheets responded too quickly to time with any accuracy, so I increased the number of rows to 5,000. All times were well below 2 seconds.

Verdict: Based on a subjective impression, it looked as though Gnumeric lived up to its reputation of being the fastest free spreadsheet. However, neither of the others was far behind. The 2.0 version of Calc handled the file noticeably faster than the 1.1.14 version did, and seemed slightly faster than KSpread. However, KSpread's performance was acceptable, even when opened in GNOME.

Perhaps a computer low in RAM or an even larger test document would slow the spreadsheets down enough to measure comparative speeds. However, this test does suggest that, for most users, speed shouldn't be a major factor in deciding which free spreadsheet to use. If you need more speed than any of these programs offers, you probably should be using a database instead.

Other features

Both Calc and KSpread have an advantage over Gnumeric because they are part of a larger office application, with access to a greater variety of tools; in theory, of course, Gnumeric is part of GNOME Office, but most of that suite's applications are not written, and those that are seem less integrated with each other than their counterparts in or KOffice. Both Calc and KSpread benefit, for example, by access to drawing tools and special character palettes. By contrast, Gnumeric offers drawing tools for only a few basic shapes.

In addition, Calc includes a variety of other useful features, such as the ability to insert movies or sounds, conditional formatting, and a variety of macro languages.

Because Excel dominates spreadsheets, all three free spreadsheets are designed to some extent with Excel compatibility in mind. Average users will find the import and export filters acceptable, especially for list-making. However, advanced users will find the success of the filters depends on the functions and tools for formulas used. None, of course, can run Visual Basic macros from Excel.

Calc's popularity is indicated by the fact that KSpread includes a filter for its version 2.0 OASIS format, while Gnumeric has one under development. All three programs can also export to PDF format.


After tallying the results from the individual evaluation categories, Calc comes out ahead, as it was tops in more than half the categories. It remains the most fully-featured FOSS spreadsheet, and some rumored problem with speed seem solved in version 2.0. However, this is a victory based on points rather than a knockout. Gnumeric ties Calc in one category and wins two rounds outright. Nor is Gnumeric as far ahead of KSpread as rumor makes it. Both Gnumeric and KSpread are developing rapidly, already outperforming Calc in some areas and making respectable showings in others.

Of course, these verdicts do not necessarily tell the whole story. Gnumeric's formula tools, for example, may make it more appealing to mathematical students than the other two. KSpread's excellent charting might make it the choice of those who often need to represent their data visually. For others, a particular feature -- perhaps even one not mentioned -- may be an important factor in choosing a spreadsheet.

Two decades ago, spreadsheets and word processors spearheaded the rise of the personal computer. Now, as FOSS unfolds, Calc, Gnumeric, and KSpread all seem ready to become as important as their proprietary predecessors.

Bruce Byfield is a freelance course designer and instructor and a technical journalist. He is also a regular contributor to NewsForge, ITMJ, and

Click Here!