Change number format

Apply a number format

In the Spreadsheet Editor, you can easily change the number format, i.e. the way the numbers appear in a spreadsheet. To do that,

1. select a cell, a cell range with the mouse or the whole worksheet by pressing the Ctrl+A key combination,

   Note: you can also select multiple non-adjacent cells or cell ranges holding down the Ctrl key while selecting cells/ranges with the mouse.

2. drop-down the Number format button list situated on the Home tab of the top toolbar or right-click the selected cells and use the Number Format option from the contextual menu. Select the number format you wish to apply:
   • General - is used to display the data as plain numbers in the most compact way without any additional signs,
   • Number - is used to display the numbers with 0-30 digits after the decimal point where a thousand separator is added between each group of three digits before the decimal point,
   • Scientific (exponential) - is used to keep short the numbers converting in a string of type d.dddE+ddd or d.dddE-ddd where each d is a digit 0 to 9,
   • Accounting - is used to display monetary values with the default currency symbol and two decimal places. To apply another currency symbol or number of decimal places, follow the instructions below. Unlike the Currency format, the Accounting format aligns currency symbols to the left side of the cell, represents zero values as dashes and displays negative values in parentheses.

     

     Note: to quickly apply the Accounting format to the selected data, you can also click the Accounting style icon on the Home tab of the top toolbar and select one of the following currency symbols: $ Dollar, € Euro, £ Pound, ₽ Rouble, ¥ Yen, kn Croatian Kuna.

   • Currency - is used to display monetary values with the default currency symbol and two decimal places. To apply another currency symbol or number of decimal places, follow the instructions below. Unlike the Accounting format, the Currency format places a currency symbol directly before the first digit and displays negative values with the negative sign (-).
   • Short Date - is used to display date in the DD.MM.YYYY format,
   • Long Date - is used to display date in the DD.month in full.YYYY format,
   • Time - is used to display time,
   • Percentage - is used to display the data as a percentage accompanied by a percent sign %,

     Note: to quickly apply the percent style to the data, you can also use the Percent style icon on the Home tab of the top toolbar.

   • Fraction - is used to display the numbers as common fractions rather than decimals.
   • Text - is used to display the numeric values as a plain text with as much precision as possible.
   • More formats - is used to create a custom number format or to customize the already applied number formats specifying additional parameters (see the description below).
   • Custom - is used to create a custom format:
     • select a cell, a range of cells, or the whole worksheet for values you want to format,
     • choose the Custom option from the More formats menu,
     • enter the required codes and check the result in the preview area or choose one of the templates and/or combine them. If you want to create a format based on the existing one, first apply the existing format and then edit the codes to your preference,
     • click OK.
3. change the number of decimal places if needed:
   • use the Increase decimal
     icon situated on the Home tab of the top toolbar to display more digits after the decimal point,
   • use the Decrease decimal
     icon situated on the Home tab of the top toolbar to display fewer digits after the decimal point.

Note: to change the number format you can also use keyboard shortcuts.

Customize the number format

You can customize the applied number format in the following way:

1. select the cells whose number format you want to customize,
2. drop-down the Number format button list on the Home tab of the top toolbar or right-click the selected cells and use the Number Format option from the contextual menu,
3. select the More formats option,
4. in the opened Number Format window, adjust the available parameters. The options differ depending on the number format that is applied to the selected cells. You can use the Category list to change the number format.

   

   • for the Number format, you can set the number of Decimal points, specify if you want to Use 1000 separator or not and choose one of the available Formats for displaying negative values.
   • for the Scientific and Percentage formats, you can set the number of Decimal points.
   • for the Accounting and Currency formats, you can set the number of Decimal points, choose one of the available currency Symbols and one of the available Formats for displaying negative values.
   • for the Date format, you can select one of the available date formats: 4/15, 04/15, 4/15/06, 04/15/06, 4/15/2006, 04/15/2006, 4/15/06 0:00, 04/15/06 0:00, 4/15/06 12:00 AM, A, April 15 2006, 15-Apr, 15-Apr-06, Apr-06, April-06, A-06, 06-Apr, 15-Apr-2006, 2006-Apr-15, 06-Apr-15,  06-4-15, 06-04-15, 2006-4-15, 2006-04-15, 15/Apr, 15/Apr/06, Apr/06, April/06, A/06, 06/Apr, 15/Apr/2006, 2006/Apr/15, 06/Apr/15, 06/4/15, 06/04/15, 2006/4/15, 2006/04/15, 15 Apr, 15 Apr 06, Apr 06, April 06, A 06, 06 Apr, 15 Apr 2006, 2006 Apr 15, 06 Apr 15, 06 4 15, 06 04 15, 2006 4 15, 2006 04 15.
   • for the Time format, you can select one of the available time formats: 12:48:58 PM, 12:48, 12:48 PM, 12:48:58, 48:57.6, 36:48:58.
   • for the Fraction format, you can select one of the available formats: Up to one digit (1/3), Up to two digits (12/25), Up to three digits (131/135), As halves (1/2), As fourths (2/4), As eighths (4/8), As sixteenths (8/16), As tenths (5/10) , As hundredths (50/100).
5. click the OK button to apply the changes.

Return to previous page