Data in Gnumeric

The main purpose of spreadsheets like Gnumeric is to collect information in a coherent manner, perform calculations on the information and then be able to update those calculations easily if the original numbers change. The use of a spreadsheet therefore requires a substantial understanding of the types of information which can be entered into the spreadsheet and the methods which can be used to manipulate that information. This section explains how you can use data in Gnumeric.

3.5.1. The Types of Data in a Spreadsheet

Spreadsheets like Gnumeric treat information by separating the data into separate cells and considering the data in each cell to be separate elements. Each cell in the spreadsheet has both a value, which is what Gnumeric manipulates, and a representation, which is what is actually shown. Understanding this distinction is complicated and make take some time if you are new to spreadsheets. This distinction between value and representation is one of the reasons spreadsheets are so useful.

The cells of the spreadsheet are contained in the cell grid area. The cell grid area is the area with a white background and grey grid lines. The grid lines separate this area into separate cells. Each cell has a unique reference name which is the combination of the letters of the name of the column and the number of the row. For instance, the top, leftmost cell is the cell named "A1" and the cell two over to the right and four rows down is named "C4" because it is in the column labelled "C" and in the fourth row. Each of these cells can contain only one single datum.

The datum contained in any cell will have one of five types: a text string type, a number type, a formula type, a boolean type or an error type. These five types of data values can then have various display formats so that, for instance, a number value can be displayed as a number, a monetary amount, a date or a time. Text strings are sequences of characters and punctuation marks and could, for example, contain textual information such as people's names. Number values are simply numbers but may be input and displayed in various formats including decimal numbers, dates, times, and numbers in scientific notation. Formulas are instructions to Gnumeric to calculate a result. The power of spreadsheets comes from these formulas because the results of the calculation can depend on the contents of other cells. Boolean values are either TRUE or FALSE and can be used in logical statements. Error values are usually the result of mistakes or impossible calculations.

For more advanced information on the types of data usable in Gnumeric, see Section 5.2 ― The Types of Cell Elements.

3.5.2. Putting Data into the Spreadsheet

In order to enter data into the spreadsheet, you must first select a cell in which to place the information and then actually type the information on the keyboard. Once you have entered the information, Gnumeric attempts to figure out both the appropriate data value type to assign to the cell and the appropriate data format in which to display this data value. Because this process is quite complex, you may occasionally need to actively select these parameters of the cells, which we explain in Section 3.6 ― Cell Formats below. The next two sections explain how to get data into a cell, by first moving the selection box to a desired cell and then typing the data. Moving the selection box

In order to enter data into Gnumeric you must place the selection box over the appropriate cell. The selection box appears on the cell grid as a double lined rectangle with a small grey square in the lower right corner of the box. By default the selection box surrounds the top, leftmost cell in the cell grid area.

The simplest way to move the selection box is to use the mouse. If the mouse cursor is placed over the cell "C3" (the cursor will be represented as a thick white cross) and the left mouse button then clicked, the selection box will move to cell "C3". Note that the selection box can cover more than one cell if the mouse is dragged while being clicked. The use of these larger selections is explained below in Section 3.7 ― Complex Cell Selections.

The location of the selection box also causes the column and row headers to change slightly. The letters and numbers turn bold, and colors of the headers (the text color and the header background color) change in ways that depend on the version of Gnumeric. This helps indicate what is currently selected.

You can also move the selection box with the keyboard arrow keys. For instance, typing the right arrow twice and the down arrow once will move the selection box from the cell "C3" to the cell "E4".

The selection box can be moved in other ways and will move in response to certain actions. These movements become intuitive after using Gnumeric for a little while. Data input

To enter data into a selected cell, you can simply start typing. The characters will then become part of the spreadsheet when you change the selection either by pressing the Enter key, which moves the selection down one cell, by pressing the Tab key, which moves the selection one cell to the right, or by selecting any other cell with the mouse. If the cursor is in the cell and not in the data entry area, pressing any of the cursor movement keys also causes Gnumeric to record the data in the cell and select another cell.

For example, you could use the mouse to select the cell four columns over (Column D) and three rows down (Row 3). Then you could type "Hello, this is a line of text." and then press the Enter key. The text would then appear in cell "D3" and, if the cells to the right are empty, would span into those cells so that the whole entry is visible. The selection box moves to cell “D4” when you press Enter, ready for the input of more data.

Note that as the data text is entered it appears in both the cell and the data entry area (the area below the toolbars and to the right of the equals (=) sign).

You can correct mistakes you make during data entry by using the Backspace key or the Delete key. Finer control can be obtained if the cursor is moved to the data entry area by clicking with the mouse in the box to the right of the equals (=) sign. Editing in the data entry area lets you use the arrow keys to move backward and forward in the text. You can also use the mouse to move the cursor.

To change the contents of a cell, select the cell again and either type the new contents or edit the existing contents of the cell in the data entry area.

If the content of the cell is too large for the size of the cell, the entry may span over the edge of the cell into the empty cells to the right. If the cell is a number, the cell grid area may display hash marks (######) to indicate the cell has content which is too large to display in the given cell width. Automatic data recognition

As you enter data into the spreadsheet, Gnumeric interprets the information in order, first, to assign it to a data category and, second, to give it an appropriate data display format. The entry will be assigned to one of the basic data types and possibly to a sub-type. Entries which start with an apostrophe (') are considered to be text no matter what the rest of the contents. Entries which start with an equals sign (=) are automatically considered to be a formula. Entries which are single numbers or which fall into commonly used patterns for dates or times will be considered to be numbers.

Gnumeric usually figures out correctly both the type and the appropriate display format for the data being entered. Occasionally, you will have to force Gnumeric to consider the data to be a different data type than Gnumeric would guess by default. We explain the details of this process in greater detail in the extended chapter on data, Chapter 5 ― Working with Data. Entering text

To enter text, select the appropriate cell, type the text, and then press the Enter key. If the text is too large to fit in its own cell, and the cell to its right is empty, the text will span into the cell on the right. By default, Gnumeric uses a display format for text in which the contents are shown left justified.

For more information about text elements, see Section 5.2.1 ― Text Data Elements. Entering numbers

To enter a number, select the appropriate cell, type in the number and then press the Enter key. Gnumeric recognizes several types of information to be numbers.

The simplest kind of input which Gnumeric recognizes as numbers are standard numeric values. Technically, these are contiguous sequences of digits which may have a separator symbol between the thousands and another symbol indicating the decimal separator. These symbols follow the English convention by default (comma as thousand separator, period as decimal symbol) but will adopt the symbols appropriate for a different locality if Gnumeric is launched in a particular way (see Section 13.5 ― Languages and Locales). For instance, in a French setting the period is the thousand separator symbol and the comma the decimal separator symbol. By default, Gnumeric displays numeric values lined up against the right side of the cell.

Several other types of input are recognized as numeric values which means that calculations can be performed on the values in the cells.

  • Dates in the standard format of the locale (see Section 13.5 ― Languages and Locales) are recognized as numbers. By default, 11/21/1970 will be recognized as the twenty-first of November of the year nineteen seventy. Gnumeric stores the value as the number of days since the first day of January in 1900.
  • Time values, such as 10:34 or 11:23:45 PM, are recognized as number values. These values are stored in Gnumeric as fractions of the whole day.
  • You can input percentage values simply by appending the percent symbol (%) to the value.
  • Fractions and mixed numbers are recognized as numbers. For example, “1 1/2” is equivalent to 1.5. Note that a simple fraction, such as “3/12”, may be interpreted by Gnumeric as a date. You can prevent that by including a sign (for example, “+3/12”) or by entering the fraction as a formula (“=3/12”).
  • You can also input numeric values using scientific notation. For instance, 1.003e+6 will be recognized as the value one million three thousand.

For more information on numbers, see Section 5.2.2 ― Number Data Elements. Entering a Boolean

To enter a boolean value, select the appropriate cell, type in either "TRUE" or "FALSE" and then press the Enter key. Entering a formula

To enter a formula, select a cell and type the equals sign (=) followed by a valid formula. If Gnumeric cannot understand the formula which is entered, it will open a dialog box which may have an explanation and gives you a chance either to re-edit the expression or to accept the entry as a text entry instead of a formula. The second choice makes it easy to re-edit the entry into a valid formula simply by fixing the formula and removing the leading apostrophe (') before the equals sign.

Formulas can be quite complex since the power of spreadsheets comes from these formulas. A simple example of the use of a formula is as follows: first, select cell B2 and input the value "3" into that cell. Second, select cell D4 and input (without the quotes) "=B2+2" and then type the Enter key. Cell D4 should display the value "5". If the value of cell B2 is changed from "3" to "100", Gnumeric will automatically update the value of cell D4 to "102".

A valid formula can be a simple arithmetic expression such as

which uses a formula to make the cell equal to the value 6.

Formulas may include calls to functions. These are statements which indicate that more complex operations should be performed. For instance, a formula could be "=EXP(24)" which would give the value of e (the base of the natural logarithm) raised to the 24th power. The cell would then display "2.6489e+10".

Certain functions return not just a single value but an array of values. To enter such a function, first select a range of cells to receive the result, then enter the formula to generate the array, and then press the key combination Ctrl+Shift+Enter rather than just the Enter key. For more details see Section ― Array Formulas.

As was shown in the example above, formulas may contain references to the contents of another cell. In the example given above, the contents of the cell in the second column and the second row was used in a calculation by using the cell name "B2". These references mean that complex calculations can be automatically updated when one of the original values change.

You can make references to the cells in other worksheets and even to those in other workbooks (files). The basic format of a complete reference is made of the name of the file the reference is in, enclosed by square brackets, followed by the name of the sheet, followed by an exclamation point, followed by the letter(s) of the column name, followed by the number of the row. For example, a complete reference could be "[my_file.gnumeric]Sheet3!C3". These complete references can be shortened if the filename or sheet names are the same as that of the reference. "AE34" would refer to the cell in the current file, in the current worksheet which is in column "AE" and in row "34".

References can identify a contiguous range of cells. For instance, the reference "A1:E5" refers to all the cells from the top left corner of the current sheet to the cell five rows down and five rows over. This can be useful in a formula which uses a function such as MAX(). The formula "=MAX(A1:E5)" would display the value of the largest number value in this range of cells.

For more information on references see the complete discussion in Section ― Cell Referencing later on in this manual.

For more on the use of formulas see Section 5.2.4 ― Formula Elements later in this manual. For a list of the functions available, see the function reference appendix, Appendix A ― Function Reference, or click on the toolbar button with the symbol "f(x)" on it for an organized list of functions. Entering an error value

Error values are almost never entered into the spreadsheet directly but generally arise when formulas cannot calculate valid results. The only error value occasionally entered is "#N/A". It can be either typed in directly or created via "=na()".