This tutorial will explain the basics of custom number format in LibreOffice Calc.
Calc provides different types of number formats which is explained in earlier tutorials e.g. Decimal, Numbers, Date and Time etc. All the formats that comes as default caters to basic needs.
But if you want to format your numbers as per your need which is not available, you can create custom formats and use. Lets look at some of them.
All below formatting is done using Format cell dialog and entering the custom format. This is how you can open the format cell dialog and enter custom format.
Decimal Places
The decimal places can be controlled using custom number format. Custom format 0 is for number with nearest integer. Use custom format 0.0 for one decimal place. Use 0.00 for two decimal places and so on.
Example
- Enter any decimal number e.g. 4.5 in cell A1.
- Right click A1 and open format cell dialog and enter format code as 0. Press OK.
- You can see 4.5 is displayed as 5 which is the nearest integer though the internal value remains as 4.5.
Leading Zeros
You can also control the display of an integer with leading zeros. The custom format code would be number of zeros you want.
Example
- Enter any number – say 42 in cell A1.
- Open format cell dialog and add custom format as 00000 and press OK.
- You can see 42 is shown with leading zeros as 00042 though the internal value remains as 42.
Add text beside numbers
Adding any text beside numbers often needed for readability purposes when representing data to respective users. This is also possible using custom format. You need add the text which you want to show in custom format dialog.
Example
- Enter a number – say 42 in cell A1.
- Open custom format and add format code as
0 "miles". Press OK. - You can see the number is shown as 42 miles in cell A1, though the internal value remains as 42.
Large Numbers
While working with large numeric numbers – such as thousands, millions, it is better to represent them as 1000 K or 1 M rather than 1,000,000. To achieve this, use format code as 0, "K" for thousands and 0,, "M" as millions. Single comma (,) denotes thousands and double comma (,,) denotes millions.
Example
- Enter 1000000 in both cells A1 and B1.
- Apply format code as 0, “K” in A1 and 0,, “M” in B1. Press OK.
- Now you can see A1 shows 1000 K and B1 shows 1 M though both the cells’ internal value remains 1000000.
This concludes the basics of custom formats which you can use as per your need. With the basis of this tutorial, you can create more advanced level of custom format.
Feel free to drop a comment using comment box below for any questions/suggestions.
