Number formats

When you create a spreadsheet, all cells are assigned the General format by default. When you enter data into a cell, MyOffice Spreadsheet always tries to identify its format automatically.

You can set a different cell format. For example, if you want to change the cell format to Currency, add an abbreviated title, code or symbol of the currency and press Enter (  Return). For more details on the supported currencies, see Appendix A.

To modify the format of a cell, do the following:

1.Select a cell or a range you want to assign the format to.

2.Select the desired format from the drop-down list which appears:

•On the Format command menu, select Number format.

•Use the Toolbar.

•Right-click with the mouse to open the Number format command from the context menu.

3.Use the Settings menu on the Format command menu, and then select Number format to adjust the format settings. Or use the Toolbar.

The format drop-down list next to the format name displays an example of the way data are displayed in the cell if the selected option is applied to the cell. The current format of the selected cell is indicated in the list. If a range of cells is selected, the format of the upper left cell is indicated as the current format.

The General format is assigned to all cells by default when the sheet is created. The General format is universal, that is it can be used in all formulas and functions.

In this format, the first 9 characters of a number are displayed in a cell, and the rest are available for viewing in the formula bar. For fractional numbers in the General format, non-significant zeros in the fractional part are not displayed.

The following parameters can be set for the Number format:

•Displaying numbers with a thousands separator.

•The number of characters displayed after the dot (.) delimiter in fractional numbers.

•Negative number presentation type.

To customize the format:

1.Select the Format menu or the Toolbar and click Number format. Select Settings.

2.In the opened window, select the Number format.

3.Check the Use thousands separator check box if you want large numbers to be divided into thousands (in this case a comma will be displayed between every three figures).

4.In the Decimal places box, use the buttons to set the number of characters to be displayed after the dot.

5.Select the type of negative number representation (e.g. highlight with a color or display a minus sign (-) before a number).

The Currency format is used to display numbers with a currency sign or code. By default, the entered numbers are supplemented by the currency name, which corresponds to the language settings in the profile and the regional settings of the application. If you want to use another currency in a cell, enter the currency code manually after the number. For a complete list of supported currencies and their codes, see Appendix A.

To change the cell format to Currency:

1.Select a cell or a range you want to change the format of.

2.On the Toolbar, select .

In the Currency format, you can customize the way numbers are displayed in the cell (the number of characters displayed after the separator, the currency sign to be displayed, and the way negative numbers are displayed):

1.In the drop-down list of formats, select Settings.

2.Make sure that the right format is selected in the window that opens.

3.In the Decimal places box, use the buttons to to set the number of characters to be displayed after the dot.

4.From the drop-down list, select the symbol of the currency that you want to use.

The Accounting format of a cell is used for numbers used in accounting documents. In the Accounting format, the entered number is automatically supplemented with the name of the currency, which corresponds to the language settings in the profile and the regional settings of the application.

Negative numbers in the Accounting format are placed in parentheses in a cell but remain the way they were entered into the formula bar.

In the Accounting format, you can customize the way numbers are displayed in the cell (the number of characters displayed after the separator, the currency sign to be displayed):

1.In the drop-down list of formats, select Settings.

2.Make sure that the right format is selected in the window that opens.

3.In the Decimal places box, use the buttons to to set the number of characters to be displayed after the dot.

4.From the drop-down list, select the symbol of the currency that you want to use.

If you enter a number in the date, time or date and time format, the Date and time format is automatically assigned to the cell.

If necessary, you can change the format of the date and/or time display by clicking Format menu and selecting Number format > Settings. Or use the Toolbar.

•Select the date format and time format

•If only the date or time is to be specified, select the None option for the format that will not be specified. Please note that if you enter the None option for both formats, the Date field will automatically display the first option from the list.

Date and time formats may depend on regional settings. Locale-independent formats are marked in the lists by tooltips. If you select a locale-independent format and then set the date that depends on regional settings, the entire format will be converted to locale-independent.

Using formats in calculations

The "original numbers" are required in calculations. Otherwise cells with dates would be considered as text cells and could not act as arguments in formulas and functions.

An original number corresponds to all data in the Date and Time formats. Its starting point for all dates is December 30, 1899, whose original number is 0. For example, the original number for the date of January 1, 1900 is 2.

This format is used to represent numbers as percentages. When applying the Percentage format, the entered number is multiplied by 100 and is supplemented with the percentage sign (%).

In percentage cell format, you can customize the appearance of numbers in a cell by specifying the number of characters displayed after the separator

To change the cell format to Percentage, do the following:

1.Select the cell or range you want to change the format of.

2.Click Percentage on the Toolbar.

You can set the cell format (including setting the way numbers are displayed in the cell) in a usual way.

This format is used to display numbers as fractions.

With Fraction format, you can customize the way of numbers are displayed in a cell by selecting the fraction type.

To change the format to Fraction:

1.Select the cell or range you want to change the format of.

2.Select the Fraction format from the drop-down list which appears:

•On the Format command menu, select Number format.

•Use the Toolbar.

•Open the Number format command from the context menu.

3.To set the parameters of the selected format, use the Settings command on the Format > Number format menu. Or use the Toolbar.

The Scientific format displays the entered data in exponential form, replacing part of the number by E + n, where E stands for exponential representation (multiplication of the previous number by 10 in degree n). For example, the exponential four decimal place format displays 55991212142 as 5.5991E+10, that is 5.5991 multiplied by 10 to the tenth degree.

In the Scientific cell format, you can customize the way numbers in the cell are displayed by specifying the number of characters displayed after the separator.

You can set the cell format (including setting the way numbers are displayed in the cell) in a usual way.

In the Text format, the data is not converted and is displayed the same way in a cell and the formula bar.

Unlike General, data in the Text format is not taken into account in the calculation process, even if there is a number inside a cell, unless the cell is included in the calculation using Text functions (see Appendix B). This format is useful when you want to exclude some numbers from an array of arguments.

For the numbers in the Number, Currency, Accounting, Percentage, and Scientific formats it is possible to increase or decrease the number of characters displayed after the delimiter.

When decreasing the decimals, the last visible decimal is automatically rounded up or down depending on the nearest value. Standard number rounding rules are applied.

You can adjust the number of decimals displayed after the dot (.) delimiter using the following tools:

•The Decimal places field in the cell format settings window

•Toolbar buttons

To increase or decrease the number of characters after the dot (.) delimiter using the buttons on the Toolbar, follow the steps below:

1.Select a cell or a cell range. The format of the elements must support changes to the decimal places.

2.To increase the number of characters after the delimiter, click  Increase Decimals. To decrease the number of characters after the delimiter, click .