Tips and Tricks about Number Format in Excel

Tips and Tricks about Number Format in Excel
Last Updated on 25/07/2023
Reading time: 3 minutes

Number Format Menu

Working with Excel is working with numbers. That's why Excel offers a whole list of formats to present your numbers.

In the Tab Home, you have the following menu

Menu Format Number
  • General
  • Number
  • Currency
  • Accounting
  • Percentage
  • Fraction
  • And Scientific

You can also customize any Number Format with the Custom Number Format dialog box. We will see some situations in this article.

Custom Format Cells Dialog Box

Date and Time are specific numbers and their format will be described in two other articles.

Let's study the different number formats and discover some tricks.

Persistent Number Format in the cells

When you open a new workbook, all the cells have the General Format.

But if you open an existing document, you must know that the format is recorded in each cell where a number format has been applied.

For instance, we will insert the number 0.75 in 4 different cells. But not all of them have the same Format Number.

Persistent number format

When you insert a number, Excel reuses the format previously defined.

The General format or the "none format at all".

By default, when you insert a number in a cell, it is displayed as you write it. No specific format is applied, and it is called General Format.

But, it is also helpful to "clean" the previous cells' format.

Here, each time you insert a number, it has 2 digits. To avoid this, you can clean the previous cells' format by applying the General format.

General format to clean cells format

The Number format

The Number format is for you if you want to automatically add decimals to your number.

Whatever the number you will write in cells with the Number format, the numbers will be displayed with 2 decimals.

Number format with 2 decimals

You can change the number of decimals in the Custom Format Cells dialog box.

Change the number of decimals

The comma thousands separator

Have you noticed the check box Use 1000 Separator in the previous picture? This option is very helpful.

But you can find this option in the ribbon with the following icon.

Format with a thousand separator

Not only you add a comma for the thousands, but also, you now have 2 decimals.

The Percentage Format

This format will add the symbol % after your number. But there is a mistake to avoid.

For the first time you want to write a percentage, you must write your number divided by 100. Because the percentage format automatically adds two zeros.

Bad use of percentage format
Correct application of the percentage format

But, if your cell is already formatted as a percentage, you have to write your value without the division by 100.

Insert number in a cell with the percentage format

Currency and Accounting Format

Here, both formats will add your currency symbol to your number. Your Windows settings manage the currency symbol.

The accounting format adds currency symbol

The differences between Currency and Accounting are

  • The $ sign► Floating in currency and Fixed in Accounting
  • Zero Values ► 0.00 in Currency and dash – in Accounting
  • Negative Values ► red or brackets in Currency and minus to the left side of $ in Accounting

1 Comment

  1. Abdul Nuru Amarquaye
    02/05/2023 @ 16:18

    My name is Nuru Amarquaye i would like to acquire new skills in excel that will build my knowledge in data analysis with excel and other tools in order to bring about decision.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Tips and Tricks about Number Format in Excel

Reading time: 3 minutes
Last Updated on 25/07/2023

Number Format Menu

Working with Excel is working with numbers. That's why Excel offers a whole list of formats to present your numbers.

In the Tab Home, you have the following menu

Menu Format Number
  • General
  • Number
  • Currency
  • Accounting
  • Percentage
  • Fraction
  • And Scientific

You can also customize any Number Format with the Custom Number Format dialog box. We will see some situations in this article.

Custom Format Cells Dialog Box

Date and Time are specific numbers and their format will be described in two other articles.

Let's study the different number formats and discover some tricks.

Persistent Number Format in the cells

When you open a new workbook, all the cells have the General Format.

But if you open an existing document, you must know that the format is recorded in each cell where a number format has been applied.

For instance, we will insert the number 0.75 in 4 different cells. But not all of them have the same Format Number.

Persistent number format

When you insert a number, Excel reuses the format previously defined.

The General format or the "none format at all".

By default, when you insert a number in a cell, it is displayed as you write it. No specific format is applied, and it is called General Format.

But, it is also helpful to "clean" the previous cells' format.

Here, each time you insert a number, it has 2 digits. To avoid this, you can clean the previous cells' format by applying the General format.

General format to clean cells format

The Number format

The Number format is for you if you want to automatically add decimals to your number.

Whatever the number you will write in cells with the Number format, the numbers will be displayed with 2 decimals.

Number format with 2 decimals

You can change the number of decimals in the Custom Format Cells dialog box.

Change the number of decimals

The comma thousands separator

Have you noticed the check box Use 1000 Separator in the previous picture? This option is very helpful.

But you can find this option in the ribbon with the following icon.

Format with a thousand separator

Not only you add a comma for the thousands, but also, you now have 2 decimals.

The Percentage Format

This format will add the symbol % after your number. But there is a mistake to avoid.

For the first time you want to write a percentage, you must write your number divided by 100. Because the percentage format automatically adds two zeros.

Bad use of percentage format
Correct application of the percentage format

But, if your cell is already formatted as a percentage, you have to write your value without the division by 100.

Insert number in a cell with the percentage format

Currency and Accounting Format

Here, both formats will add your currency symbol to your number. Your Windows settings manage the currency symbol.

The accounting format adds currency symbol

The differences between Currency and Accounting are

  • The $ sign► Floating in currency and Fixed in Accounting
  • Zero Values ► 0.00 in Currency and dash – in Accounting
  • Negative Values ► red or brackets in Currency and minus to the left side of $ in Accounting

1 Comment

  1. Abdul Nuru Amarquaye
    02/05/2023 @ 16:18

    My name is Nuru Amarquaye i would like to acquire new skills in excel that will build my knowledge in data analysis with excel and other tools in order to bring about decision.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *