Cell Formats

The data in Gnumeric are stored in the cells of the spreadsheet, each of which has a cell format which dictates how the data will be displayed, whether the cell will have borders and other information. Cell formatting can be quite confusing at first because it combines simple changes, such as the colour of the characters being displayed, with more complex ideas, such as how future changes to the cell will be interpreted.

All of the cell formatting commands can be reached through a context menu by right clicking on a cell and selecting the Format Cells... menu entry. This will open a dialog window with tabs which group together similar types of formatting. Clicking on the Font tab allows you to change the font family, style, size and colour. For instance, if the cell B2 contained the text "Hello, this is my first spreadsheet" then you can make this text bigger by selecting a larger font size.

3.6.1. Simple Cell Formatting

Simple changes to the format of a cell include changing the alignment of the characters, changing the font type or colour, changing the border, and changing the colour or pattern of the background.

The Alignment, Font, Border, and Background tabs are simple to understand simply by playing around with the settings and looking at the effect on a cell which contains text.

The Protection and Validation tabs are advanced functionality which you can ignore at the beginning. For explanations of these tabs, see the advanced description in Section ― Validation Tab.

3.6.2. Formatting the Display and Entry Data Types

Cell formats are most difficult to understand when they address the type of data stored and the visual display of that data. This only arises with the options selected in the Number tab of the Format Cells dialog. While these ideas are complex, you need to understand them early on as they are fundamental to spreadsheet use.

When you enter data into Gnumeric, the spreadsheet interprets the entry based on the input format of the cell. The default format of empty cells is the General format which instructs Gnumeric to guess both the type of the data being entered and a suitable display format for that data type. However, you can change the General format to a specific format in order to alter both the way Gnumeric interprets any future data input to the cell and the way data in the cell are displayed.

Changing the format does not alter the data type of data already in a cell but does alter the display format of that data. This means that the input format will only affect future input whereas the display format will affect both the data currently in the cell and any data placed later into the cell.

For example, if you enter "12/25/2000" (without the quotes), Gnumeric guesses that this is a date and stores the value (serial number) 36885. (Usually, the value Gnumeric uses for dates is the number of days since January 1st, 1900.) 1 At the same time, Gnumeric changes the display format to display this number as a date, with a numeric month, day and year, separated by slashes.

The order in which the formatting operations occur is critical. It is not possible to alter the type of a datum currently in a cell by formatting. To alter the interpretation of the data type in a cell, formatting must occur prior to the entry of the data.

It sometimes becomes necessary to override the "General" type if Gnumeric is making an incorrect assessment of the data being entered. Postal Zip Codes in the United States, for instance, are incorrectly interpreted to be numbers. Some of these Zip Codes start with a leading zero which the "General" format type drops so the user must intervene to keep that zero displayed. In order to input these Zip Codes, the following steps must be performed. First, the cell must be selected. Next, the cell must be formatted to hold a "Text" value. This formatting changes both the interpretation of any future data entry into this cell and alters the display formatting of the cell. Finally, the Zip Code can be entered. Following these steps, the data value will be considered to be a "Text" value, any leading zeros will be retained and the data will be left justified since this is the default display format for "Text" values.

If you need to alter the data type of a whole column prior to data entry, you can do this in one formatting operation. You can click the right mouse button on the column header (the letters at the top) and select Format 1 Column from the context menu, or you can first select the whole column by clicking on the column header, then selecting the Format menu and the Cells... menu entry. This quick approach to pre-formatting cells can be done for any group of selected cells.


For the sake of compatibility with Excel, Gnumeric also assigns a serial number to February 29th, 1900 as if 1900 had been a leap year even though it was not. So February 28th, 1900 has the serial number 59 and March 1st, 1900 the serial number 61. If you try to format the serial number 60 as a date, Gnumeric recognizes that that date does not exist and shows a sequence of # symbols.