As described in the previous section, you can use the direct commands of the command group Number on the ribbon tab Home to set the number format of a cell in order to determine how numbers should be displayed in this cell. For advanced options, you can also open the dialog box by clicking on the group arrow
in the bottom right corner of the command group Number.
The following number formats are available:
Default
This displays the number as it was entered.
More precisely: The number is displayed right-aligned; otherwise, no changes will be made to its formatting. It is displayed with its actual number of decimal places (but with a maximum of five decimal places).
| Example: | 42 |
Number
This is the same as the Default number format, except that you can specify several formatting options (see previous section, options of the dialog box). For example, you can set the number of decimal places to be displayed.
| Example: | 42.00 |
Note: The number will only be displayed rounded, but it is actually not rounded. Thus, it still retains all of its decimal places. To actually round the number, use arithmetic functions like the ROUND function.
Currency
This displays the number with a currency symbol and two decimal places.
Only available via the dialog box: You can select the desired currency from the dropdown list Currency.
| Example: | €42.00 |
Note: Depending on the country settings of your system, the display may differ.
Accounting
This is the same as the Currency number format (see above), except that numbers are displayed in accounting format.
The difference to the currency format is that negative amounts have the minus sign in the left edge of the cell, separated from the number. Currency symbols are also aligned flush with each other.
| Example: | - €42.00 |
Note: Depending on the country settings of your system, the display may differ.
Date/Time
This displays the number as a date and/or time.
Only available via the dialog box: The dropdown list Format offers numerous different date and time formats, depending on your system's regional settings.
Directly via the dropdown list of the command group Number:
▪Short date: The date is displayed in the 11/22/2018 format
▪Long date: The date is displayed in the Thursday, November 22, 2018 format
▪Time: The time is displayed in the 22:30:00 format
Percentage
This displays the number as a percentage.
Apart from adding a percent sign (%) to the number, the number will also be multiplied by 100 when displayed. For example, 0.5 will be displayed as 50%.
| Example: | 4200% |
Scientific
This displays the number in scientific notation.
| Examples: | 5E+03 (equals 5 * 10^3, which is 5000) |
| 4.2E-01 (equals 4.2 * 10^-1, which is 0.42) |
Fraction
This displays the number as a fraction.
Only available via the dialog box: The format of the fraction can be selected in the dropdown list Format.
Note: The number will be displayed as a rounded value if necessary. If, for example, you select the format As halves and then enter the number 2.3, it will be displayed as 2 1/2.
| Examples: | 2 1/2 |
| 5 9/10 |
Boolean
This displays the number as a Boolean value (logical value).
If the number is zero, FALSE will be displayed. If it is any other value, TRUE will be displayed.
| Examples: | TRUE |
| FALSE |
Text
This number format presents a special case: It forces PlanMaker to treat a number as text – not as a number.
The number will be left-aligned and displayed exactly the way it was entered. Unlike all other number formats, numbers will not be automatically reformatted after they are entered. Leading zeros will not be removed; dates will not be reformatted automatically, etc.
This can be useful, for example, when entering numbers with leading zeros (for example, phone area codes or zip codes). If you enter a number like "00043682" into a cell, PlanMaker will usually remove the three leading zeros automatically. If you first apply the Text number format to the cell, the leading zeros will not be removed.
Caution: For compatibility reasons, this number format has been implemented in the same way as in Microsoft Excel. Thus, it will lead to the same unexpected results in calculations:
If you apply this format, for example, to cells A1 and A2, the result of the calculation A1+A2 will be the sum of these two cells. However, the calculation SUM (A1:A2) will return zero. The reason for this is that text generally has the "value" zero for most calculation functions!
Custom
Only via the dialog box: This displays the number in a custom number format.
For more information, see the next section.