In this tutorial we will try to understand different number formatting options that we have in excel.
One thing to remember here is formatting does not have any impact on how the numbers are stored internally or in other words there will not be any change in the underlying value of the cell by applying different formatting.
Suppose we have a number 0.3 in a cell as shown below in the figure and by applying different formatting option we could display that number in a number of ways as shown below.
Excel by default has a General format to display any value and we could change the appearance by using different formatting options we will explain one by one.
Percent Format
Select the cell or range of cells and click on percent style icon in the Home tab to change the number to percentage format as shown below.
You could increase or decrease the number of digits after decimal by clicking on Increase Decimal/Decrease Decimal just beside the percent style option as explained below.
Decimal Format
We could control the number of decimal places that we want to show in any value by using Increase Decimal/Decrease Decimal options in excel in the Home tab as explained above.
Below is an example where we have 5 digits after the decimal place and we want to display only 2 digits after decimals, so to do that we will have click on the Decrease Decimal icon 3 times.
As you could see below only two digits are shown after the decimal.
If you again want to show 5 digits after decimal, click on Increase Decimal icon similarly.
Currency Formatting
IN this section we will see how to display a number in a dollar format or any other currency format.
We have different currencies available in the Home tab in excel which will convert the number to corresponding currency format.
Select the cell or range of cells that you want to format and select the desired currency format from Accounting Number Format drop down as shown below.
You could see we have got dollar format as I selected dollar format from drop down.
Comma Formatting
In the earlier section where we saw the currency formatting and we saw that excel has automatically added comma separator when we applied the currency formatting and in this section we will see how we could add comma separator in excel.
Select the number that you want to format with comma separator and click on comma icon in the Home tab in excel as shown below.
By default excel will add a decimal and 0’s which you can remove by clicking on Decrease Decimal icon as explained earlier in this post. We have successfully added comma separator as you can see below.
