Understanding Number Formats

By default, all numbers entered in your spreadsheets use the Standard number format. This Standard format is also known as the General number format and shows numerical entries with no decimal places and no special formatting (such as commas or dollar signs).

While the Standard, Currency, and Percentage formats are easily accessible from the Object toolbar, the other formats available for your numbers are found in the Cell Attributes dialog box.

To Apply Cell Number Formats

The Cell Attributes dialog box also provides a drop-down list that allows you to change the language used for the numerical formatting of a range of cells. So, for example, if you wish to use Spanish as the language selection, click the drop-down list and select Spanish.

To Apply the Currency Format

In terms of currency formatting, you can also select the type of currency that is being used in a particular cell (as well as the overall currency formatting). For example, if you are working with the Rand from South Africa (rather than dollars from the U.S.), you would select the Rand as your currency type in the drop-down list that appears at the top of the Format box in the Cell Attributes dialog box.

As you've probably already seen in the Category box of the Cell Attributes dialog box, there is a number of categories provided for numerical formats. Table 3.1 provides a brief description of each format category (precluding the date and time formats that are discussed later in this chapter). Figure 3.4 provides an example of each of the format categories.

Each category provides several different formatting possibilities, which include different ways to portray negative values and the number of decimal places available. Obviously, some of the formats lend themselves to more obvious uses, such as the Currency and Percent formats. Just keep in mind that as far as “raw” values go, the best format is the General format (which is the default).

One thing that you should definitely keep in mind: If the value you have entered in a particular cell and then applied a particular format to exceeds the column width set for the column that it resides in, a series of number signs (#) will appear across the cell.This means that the column width needs to be adjusted to accommodate the value and its formatting. Just double-click on the column's border to set the column to accommodate the largest entry in the column. More about changing column widths is discussed later in this chapter.

To Apply Decimal Places and Leading Zeros

A few words should be said about decimal places and leading zeros. You can quickly change the number of decimal places available to a particular numerical format using either the Add Decimal Place tool or the Delete Decimal Place tool on the Object toolbar. Select the cell or cells for which you wish to change the number of decimal places and then click the appropriate button to add or delete decimal places.

For instance, to change the number of decimal places or leading zeros that are part of a numerical format found in the Cell Attributes dialog box, select the number format and then use the Decimal Places or Leading Zeros click boxes in the Format box, respectively.

To Apply the Date and Time

Several formats are also available for date and time entries, and they deserve some special discussion. Dates are actually seen by your computer as the number of days that have passed since December 31, 1899 (January 1, 1900 would be considered day 1).

You can enter dates into your Calc spreadsheets using the format M/D/YY (which is the default formatting for dates in a Calc spreadsheet); for example, 03/12/01. Or, you can actually abbreviate or spell out the month. For example, the entry Jan 5 01 will be recognized by Calc as a date and converted to 01/05/01 in the spreadsheet cell.

A number of other date formats are available such as December 31, 1999 (where the month is spelled out and the day is separated from the four-digit date by a comma), Fri, Dec 31, 99, and date formats that include the current time.

Calc also sees time as a numerical value; it is seen as a decimal value that represents the percentage of the day that has elapsed since midnight. For instance, .50 would be 12 noon. Fortunately, a time value will appear in a format that looks like something that would appear on a digital clock. Time formats such as HH:MM (hours and minutes) and HH:MM:SS (hours, minutes, and seconds) are offered.

When you enter times into your spreadsheets, enter the hour followed by the minute in the format HH:MM. If you wish to include the time with the date, format the cell holding the date to show the time that the cell information was entered (using the Cell Attributes dialog box to select the appropriate date format).

To Create Your Own Format

While there are probably more than enough “built-in” numerical formats for you to use, you may find the occasion when you have to change one of the default formats to meet your value formatting needs. When you change an existing format, Calc automatically begins creating a user-defined category for you. Your new format (based on the default format you edited) will appear under the User-Defined category in the Cell Attributes dialog box. Once the new format has been created, you can then assign it to other cells in the spreadsheet as needed. You can also create user-defined formats from scratch as follows: