13 Number formats and styles

This chapter describes how to work with the Number section of the Style dialogue box to define number, date and time formats.

It also contains a general introduction to using styles in spreadsheet documents, showing how you can use styles to apply formatting to text and numbers within the document, and to customise spreadsheet features such as the style used to show the current cell.

The Number section of the Style editor also contains other functions, such as marking cells as protected, so that they are locked against future editing.

This chapter contains information on:

Styles in spreadsheet documents

You will be familiar with the way in which Fireworkz uses styles to make text and numbers appear at a particular size, or in a particular font or style.

The style system is also used to apply formatting information to numbers, such as whether they are shown as currency, in continental format, as a percentage or a scientific or engineering format.

The Style editor is also used to define how the current cell is shown, and can be used to add borders to cells.

As a general rule, you should define styles containing number formatting information independently of styles containing font or paragraph layout information, to increase the flexibility with which styles can be used. You can apply as many styles as necessary to a cell or to text within a cell.

Predefined styles

The Sheet template supplied with Fireworkz contains several predefined number formats built in. These are automatically available in any document based on the Sheet template, simply by choosing them from the style list and applying them to selected cells.

Number formats

Fireworkz contains a number of standard number formats which are easily applied to any numbers in spreadsheets. Because number formats are an aspect of style, number formats are manipulated through the Style editor.

These formats are stored in the Number section of the Style editor. You can access them to create the styles you need which can be stored in the style list. You can also create your own completely custom number formats.

For example, the list contains a ready-made option for producing continental format numbers (where the decimal point is represented by a comma, and a full stop is used as a thousands separator). By adding the character "€" after the number as part of the format, your numbers will automatically appear as Euro amounts.

As well as adding characters to show currency, you can also use the style system to specify the precision to which numbers are shown. When you do this, the number displayed on screen is rounded to the number of decimal places which you requested, even though Fireworkz keeps the full number and uses it internally for calculations.

Base number format

All worksheets have a number format specified as part of their base style. To see this:

Style tool button Click the Style button on the toolbar.

Choose BaseSheet from the style list and click Change.

Click the Number button to look at the Number section of the Style editor.

The Number format writable field will contain the base number format of the worksheet.

This is the way in which any numbers entered into number cells will be displayed. In this case, all numbers will appear with a decimal point and two decimal places; thousands will have a comma separator.

It is recommended that you do not change the number style in the base sheet style, but create new styles for each type of number format you want to use in your worksheet.

How number formats are constructed

Number formats are built up out of hash signs (#), zero characters, punctuation marks and alphanumerical characters. Click the button to the right of the Number format definition box, which will display a pop-up menu showing a list of pre-defined number formats.

Hashes and zeroes

Hashes which appear in the format definition are replaced by numerals (i.e. any of zero to nine) in your worksheet. Zeros will always appear, unless replaced by actual parts of the number. For example, in a number style 0,000 10 will appear as 0,010 whereas if the style is defined as #,##0 it will appear as 10 .

Where characters after the decimal point are represented by hash signs, trailing zeros will not be displayed. For example, the format 0.## will cause 54.00 to be displayed as 54. If figures after the decimal point are displayed as zeroes, trailing zeros will be displayed. So for currency formats, where you want decimal places always to be displayed, you need to ensure that the format ends with .00 .

Enter the unit number format character as a zero character, especially if it is followed by a decimal point and places. This will ensure that numbers between zero and one have 0. in front of them.

Positive and negative numbers

A number format can have up to three different definitions, for positive and negative numbers and for zero. If you do not define a separate way of formatting negative numbers, they will be displayed in the same format as positive numbers but with a minus sign in front. In practice you need only specify a second format if you want negative numbers to be formatted differently from positive numbers. For example, in a financial worksheets number format it may be useful if negative numbers appear enclosed by brackets.

To create separate number formats, enter each format in the number format box separated by a semi-colon. The negative format appears after the semi-colon. So a format for currency with negatives in brackets would be:

£#,##0.00;(£#,##0.00)

Enter a second semi-colon after the first two formats and a third format can be entered to format the number zero. If you enter a second semi-colon and no format, zero will not be displayed. Equally, if you enter a semi-colon after the first number format, but no special format for negative numbers, negative numbers will not be displayed on the worksheet.

Percentage

This format expresses a number as a percentage. For example, 0.75 will appear as 75%.

Scientific number formats

You can choose to have numbers expressed as powers of 10, a standard scientific format. To do this, choose the definition

0.00e+00

from the predefined list in the Number formats pop-up menu.

Engineering format

This format expresses numbers so that they are always expressed in the neatest possible format with the standard SI abbreviations giving you the size of the number. To do this, choose the definition

g0.##

from the predefined list in the Number formats pop-up menu.

It will probably be useful to add the appropriate suffix for the measurement. For example, "m" for distance in metres.

Expressing Roman numerals

You can specify that numbers are displayed as Roman numerals in upper- or lower-case. Enter R# for upper-case Roman numerals or r# for lower-case. Roman numerals can only be displayed up to 3,999 and of course you can only display whole positive numbers.

Foreign formats

You can use different characters to represent the decimal point and thousands separator. To do this, in the number format box enter the comma or full stop in the UK position, and follow it with the character you want to appear in your document; for example:

1,.000.,00

which effectively swaps the full stop and comma.

Although this is what you are most likely to do with this facility, you can in face use any character as the thousands separator or decimal point.

Precision

Fireworkz rounds numbers for display (but not calculation) according to the values specified in the Number format.

Decimal places

If you want to see more (or fewer) decimal places, type in additional (or remove unwanted) # or 0 characters following the decimal point. For example:

#.00 displays two decimal places, with the numbers always shown whether you enter digits to fill them or not.
So if you typed 4, it would appear as 4.00.
#.####  displays four decimal places, which only appear when there are digits to fill them.
So 4 would appear as 4, 2.25 as 2.25, and 5.123456 as 5.1235.

Rounding large numbers

You can also reduce the value shown, for example showing millions as thousands (as you would in a financial report). To do this, prefix the usual definition with:

>to truncate numbers by three digits.
>>to truncate numbers by six digits.

Including text strings in number formats

You can include text strings as part of a number format. This is especially important if you want to use a character which the number format system interprets as a special character, such as r and e. Simply enclose text strings in quote marks (") or precede a single character with a backslash (\).

Use text strings to enter abbreviations for foreign currencies, or any other text you need.

Number formats summary

The following table includes examples of some of the possible number format configuations:

Definition Entered Appears as Description
0 23.2 23 No decimal places; numbers will be rounded up or down to the nearest integer
0.## 34.5
34
34.5
34
Up to two decimal places are available but trailing zeros will be dropped.
#,##0.00 34000 34,000.00 Trailing zeros to two decimal places will be displayed; multiples of 1000 separated by commas
0.##% 0.756 75.6% Displays number as a percentage
0"%" 75 75% Displays percentage with no multiplication
0.00e+00 150 1.5e+02 Scientific format
g0.## 1234 1.23k Engineering format
r# 4 iv Lower-case Roman numerals
x# 10 j Displays number as a letter of the alphabet
£#,###.00;
(£#,###.00;); 
23.446
-23.5
0
£24.45
(£23.50)
blank
Currency format; negative numbers are displayed in brackets rather than with a minus sign
#.00;(#.00); 0 Format for displaying zero appears after second semi-colon; here nothing is shown
£__0.00 5 £  5.00 Leaves the number of character spaces specified by underline characters between the currency symbol and decimal point
<# 1 1000 Shifts number to the left by three decimal places
># 1000 1 Shifts number to the right by three decimal places
(i.e. displays thousands)
>># 1000000 1 Shifts number to the right by six decimal places
(i.e. displays millions)

Saving a number format as a style

To change the number format defined in a style:

  1. Choose the style you want to edit and click the Change button. Or create a new style, giving it an appropriate name (such as Roman numbers, or Percentage), and then click the New button.
  2. Display the Number section of the Changing style/Define style dialogue box.
  3. There will be a number format already shown in the writable field. This is the current number format. Click the button to the right of this field to display a pop-up menu of other predefined number formats. If one of these is what you want, or is closer to what you want, click on it and it will appear in the Number format writable field.
  4. Edit the number format to your requirements. Type in hashes to represent numbers, and any punctuation marks or currency symbols you wish to appear. Remember to specify the precision required after the decimal place.
  5. Click Apply.
  6. The style will be changed as specified and any numbers entered in that style will be reformatted.

Creating your own custom number format

You may find that the alternatives within the list don't quite match your requirements. For example you may want to work in a currency format other than pounds sterling.

To do this you should:

  1. Display the Number section of the Style editor as described above.
  2. Select the standard number format which most closely relates to what you want to create.
  3. Edit the format in the Number format writable field, adding or removing the necessary characters.
  4. Click Apply to save the new number format as part of the style you were editing.

Date and time formats

Date and time formats are managed via the Number section of the Style editor as well. They can be entered separately, or as part of the same combined date and time format:

Your base style contains a date and time format which will be applied to dates and times which Fireworkz recognises.

Fireworkz can interpret some sequences of numbers and separator characters as times and dates automatically.

For other numbers, you will need to apply a style to them to format them as times or numbers. Define a style which displays times and numbers in the format you want. As with number formats, several predefined formats are available, or you can use one of the building blocks to design your own custom time and date formats.

Date format options

The following table includes examples of some of the possible date and time format configuations:

Definition Entered Appears as Description
d.m.yyyy 1.1.93 1.1.1993 No leading zero on single-digit days and months;
four digits of year
dd.mm.yy 1.1.93 01.01.93 Leading zero on single-digit days and months;
two digits of year
ddd m.yy 1.1.93 1st 1.93 Day appears as cardinal number
d Mmm yy 1.2.93 1 Feb 93 Three-letter abbreviated month name with capitalisation
d Mmmm yy  1.2.93 1 February 93 Full month name displayed as text
d MMM yy 1.2.93 1 FEB 93 Abbreviated text in capitals

Time format options

Definition Entered Appears as Description
hh:mm:ss 13:03:04 13:03:04 24-hour clock; hours, minutes and seconds have leading zeros where required
H:mm 18:03:04 18:03 24-hour clock; seconds not displayed; hours do not have leading zero
h:mm "am"; 
h:mm "pm"
17:39:25 5:39 pm 12-hour clock with no leading zeros and 'am' or 'pm' expressed by text strings - the semi-colon distinguishes between morning and afternoon
nn:ss 17:39:25 39:25 Minutes and seconds only

Displaying parts of a time

You do not have to display all of the time. You may choose only to show hours and minutes, or minutes and seconds. Build the format you want using

hhrepresents hours;
mmrepresents minutes except that
nnis used to represent minutes when these are the first part of the time displayed;
ssrepresents seconds.
: must be used to separate the components of the time.
This is the character which enables Fireworkz to know that it is dealing with a time.

12 hour and 24 hour clock

You can specify 12 hour or 24 hour clocks in your time formats. If you enter a single time format, times will be shown as the 24 hour clock. If you enter two formats, separated by a semi-colon (and no spaces) the 12 hour clock will be used. So

hh:mm:ss

will produce 24 hour numbers and

hh:mm:ss;hh:mm:ss

will produce 12 hour numbers. If you create 12 hour clocks, you should also use a text string in the format to indicate whether times are am or pm.

Text strings

You can enter text strings, for text to distinguish between am and pm. Remember to enclose the text string in double quotes.

Creating date and time formats

You can edit date and time formats just like number formats. When you enter a date into a number cell, it is formatted according to the date style defined in the Base style, but you can apply a different format to it by applying a different style to the cell.

Saving a date format as part of a style

You can save date formats in exactly the same way as number formats. Define a number format within a style, or create a new style specially for the time or date format you want to apply to certain cells in your worksheet.

Error and string formats

Beneath the section for Number and Date and time formats in the Number section of the Style editor is a section marked Text.

This section governs the way strings and Error messages are displayed in number cells. It does not affect the general display of text in your document.

There are two standard options:

@ displays the error message text, e.g. Circular reference
@;"ERR "@ displays the error message text prefixed by ERR, e.g. ERR Circular reference; strings you enter will not have a prefix

You could also edit the message which appears, adding any other text string enclosed by double quotes. The @ character must appear for the precise error message to be displayed in the cell.

Note that error messages also appear in the status line when you click on the affected cell.

New object

This option governs the way in which blank cells are treated in the document.

For spreadsheet documents this option is usually set to Number in the BaseSheet style. However, you may wish to create a style with New object set to Text, so that you can mark columns or rows to be used as headings to make it easier to type text into them.

Protecting cells

You will sometimes want to protect cells in your worksheet so that they cannot be edited. New data cannot be entered into protected cells and any formulae contained in them cannot be edited or replaced.

Protection is turned on using the Number section of the Style editor. You are advised to set up a style whose only attribute is protection, so that you can apply protection to cells as flexibly as possible. Or you may like to add a background colour to the number protection style, so that you can see on screen which cells have been protected.