Number Formatting

You are here:
< Back

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.

table showing different formats in excel

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.

apply percent format in excel

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.

Increase decimal and decrease decimal in excel

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.

showing n digit after decimal in excel

As you could see below only two digits are shown after the decimal.

applying format to decrease the number of digits after decimal in excel

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.

apply currency format in excel

You could see we have got dollar format as I selected dollar format from drop down.

apply dollar formatting in excel

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.

apply comma format in excel

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.

how to apply comma format to a number in excel

Random Posts