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:
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.
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.
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.
All worksheets have a number format specified as part of their base style. To see this:
Click the button on the toolbar.
Choose BaseSheet from the style list and click .
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.
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 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.
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.
This format expresses a number as a percentage. For example, 0.75 will appear as 75%.
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.
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.
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.
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.
Fireworkz rounds numbers for display (but not calculation) according to the values specified in the Number format.
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. |
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. |
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.
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) |
To change the number format defined in a style:
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:
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.
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 |
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 |
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
hh | represents hours; |
mm | represents minutes except that |
nn | is used to represent minutes when these are the first part of the time displayed; |
ss | represents 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. |
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.
You can enter text strings, for text to distinguish between am and pm. Remember to enclose the text string in double quotes.
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.
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.
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.
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.
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.