Working with Gnome's Gnumeric Spreadsheet

Fast Accounting

By Oliver Frommel

original photo: www.sxc.hu

Spreadsheet applications have always played a special role on the PC. One of the first commercial spreadsheet programs, Lotus 123 was regarded as a benchmark for the compatibility of so-called IBM compatible PCs. Excel came along a few years later, and "Excel file" soon became synonymous with a spreadsheet document.

Of course, spreadsheet applications can do much more than the simple calculations most people use them for. Where spreadsheets really shine is in managing recurring calculations and visualizing the resulting data in the form of easily readable charts. In the world of Linux, the Gnumeric spreadsheet tool (Figure 1) has become an extremely mature application.

Figure 1: Gnumeric is a fast and versatile spreadsheet with support for many mathematical and scientific functions.

Input

Gnumeric's age shows. Launched by Gnome founder Miguel de Icaza, Gnumeric is the oldest Gnome program of them all, an important building block of the Gnome project. Now in its eighth year, Gnumeric offers a wide range of functions and genuine stability. Additionally, the program does not attempt to emulate its Microsoft counterpart, Excel, but goes its own way and tries to outdo all other spreadsheet applications. The developers have made sure that Gnumeric is compatible with its competitors - with a few special exceptions, such as embedded MS Office objects. And Gnumeric can handle all the math functions that Excel offers.

When you launch the program via the menu, or type gnumeric at the command line, the main window appears in a matter of seconds. The OpenOffice Calc spreadsheet, by contrast, takes about three times as long.

Manually filling a table with data is quite easy: just click on a cell and enter the values you need. There is no need to retype a value to copy it to a neighboring cell. Just click on the original value, then drag the mouse to the bottom right corner of the cell. The mouse cursor becomes a fine cross. Now hold down the mouse button and drag a frame over the cells to which you will be copying the original value, and let Gnumeric complete the job.

Enumerations Made Simple

Spreadsheets very often contain numeric sequences; for example, you might have numeric values in the first column to count the rows. Again, Gnumeric makes entering these values really simple. Just type the starting value in the first cell, and the next value in the second cell. Select both, by holding down [Ctrl] (the key for multiple selections in Gnumeric) and clicking on both cells. Then again drag the mouse to the bottom right corner of the second cell. Click and drag the mouse to fill as many cells as you like with the numeric series (Figure 2). Gnumeric references the two starting values to calculate the step. If you enter 1 and 2 for example, Gnumeric will fill the selected cells with 3, 4, 5, 6, and so on. If you need a series of even numbers, just use 2 and 4 as your starting values.

Figure 2: To create a numeric series, type the first two values then drag the bottom right corner of the selection.

Gnumeric gives you 256 columns by default, but you can easily extend this limit if you build the software yourself. To do so, change the value of SHEET_MAX_COLS and SHEET_MAX_ROWS in the src/gnumeric.h file.

Maths Genius

Of course, just typing rows of figures is not much use unless you can perform calculations. To help you do this, Gnumeric provides several mathematical functions, from simple sums and differences to complex statistical calculations. Gnumeric beats most competitors in terms of accuracy and functional scope.

If you know the name of a function, you can just type an equals sign followed by that name in the cell. The function arguments will typically be other cells identified by a letter and a number; for example, the top left cell is A1. To add A1 and A2, you can type =sum(A1;A2) in another cell. If you copy this formula, and insert it in another cell, Gnumeric automatically modifies the argument names to match; that is, you would have =sum(B1;B2) for column B. While the text cursor is marking the spot in the cell where you need to insert an argument, you can simply click the required cell. Gnumeric draws a black and white frame around the cell and waits for more input. You can then type a semicolon and click more cells to add more arguments.

As an alternative, click the f(x) button to insert a function. This tells Gnumeric to pop up a window with a list of available functions. The program lists the functions by field of application on the left: database, scientific, financial, statistics, and so on.

[Ctrl]+[F] opens the search function, which is not very practical, since a search only searches the category names. The same thing applies to the list of functions itself. [Ctrl]+[F] displays a small search window, but the search is restricted to the function name and does not include the descriptions, which would be more useful. In other words, if you are looking for a function to solve a specific problem, you almost need to know the function name before you start. On the other hand, the fact that the function descriptions in Gnumeric tell you if these functions are Excel compatible can be useful.

After selecting the required function, click the Insert button to launch the Formula Guru (Figure 3). The guru gives you an input line for the required function arguments. You can select a line and click on the cell with the required value, rather than entering the values manually. Press [Enter] in the formula wizard to confirm each argument line.

Figure 3: The formula guru helps users enter more complex formulas.

Interfacing

If you need a function that Gnumeric does not offer, you may be able to add a plugin. Gnumeric offers plugins in C, for example. The interface allows you to get the value in the cell by calling c.get_value(), but unfortunately, some distributions do not include the interface. Again, you might need to build Gnumeric from the source code to fully explore the potential of Gnumeric plugins.

Gnumeric lets you import third party formats such as Excel, Quattro Pro, Multiplan, Lotus 123, OpenOffice Calc, and Xbase. Gnumeric also reads comma-separated text files (CSV) and has a wizard for processing structured text files. Gnumeric uses the GnomeDB interface to read input from database formats such as Postgresql or Mysql.

Good Alternative

The Gnumeric spreadsheet outpaces its competitors with a huge collection of mathematical and scientific functions. And even if you don't need all this rocket science, Gnumeric is still a lean and powerful program for everyday tasks.