# Tips and Tricks about Number Format in Excel

#### Frédéric LE GUEN

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

• 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.

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.

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.

## 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.

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

## 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.

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.

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

## Currency and Accounting Format

Here, both formats will add your currency symbol to your number. Your Windows settings manage the 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.

# 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

• 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.

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.

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.

## 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.

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

## 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.

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.

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

## Currency and Accounting Format

Here, both formats will add your currency symbol to your number. Your Windows settings manage the 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.

{"mobile_concate":{"mobile-menu":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/mobile-menu.min.css"},"tf_base":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/base.min.css","v":"7.3.1"},"themify_common":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/themify.common.min.css","v":"7.3.1"},"builder-styles-css":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/themify-builder-style.min.css","v":"7.3.1"},"tb_tbp":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/tbp-style.min.css","v":"3.1.2"},"tf_theme_site-logo":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-logo.min.css","v":"7.1.4"},"tf_theme_site-description":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-description.min.css","v":"7.1.4"},"tf_search_form":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/search-form.min.css","v":"7.3.1"},"tb_featured-image":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image.min.css","v":"7.3.1"},"tb_divider":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/divider.min.css","v":"7.3.1"},"tb_post-content":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.3.1"},"wp-block-library":{"s":"https:\/\/www.excel-exercise.com\/wp-includes\/css\/dist\/block-library\/style.min.css","v":"6.3.1"},"tb_comments":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/modules\/comments.min.css","v":"3.1.2"},"tb_text":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.3.1"},"theme-style":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/style.min.css","v":"7.1.4"},"themify-media-queries":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/media-queries.min.css","v":"7.1.4","m":"(max-width:1200px)"}}