Excel number format: Formatting numbers – Currency & Accounting

Excel number format: YOUR OBJECTIVE

You want monetary values in your table to display with appropriate currency symbols – for dollars, euros, pounds, etc..

In this learning module “Excel number format ”, you will discover howto format numbers with a currency symbol.

 

Excel number format: HOW DOES IT WORK?

In Excel, you should not type currency symbols into cells manually. If you do, Excel will view the cell’s contents as text and will consequently be unable to make numeric calculations with them. Generally, values representing currency should first be entered as numbers and then formatted as currency.

Excel number format: HINT (1)

You can tell if Excel is interpreting the contents of a cell as text because, if a cell has not been explicitly formatted, numbers are automatically aliged to the right and text to the left. If you type in a currency symbol, the cell’s contents will be understood to be text and be aligned to the left. And text cannot be used for arithmetic calculations.

Excel number format:HINT (2)

The dollar sign ($) is an exception to this rule. Excel recognizes the dollar sign as a currency symbol and automatically applies a suitable format. This can only happen if you type the $ sign before the number. If you enter it after the number, the cell’s contents will be formatted as text:

If you see the currency symbol in a cell, you can tell if it is there as a result of formatting or it is part of a text entry. After selecting a cell, look at the formula bar. If the number appears in the formula bar without a currency symbol, you may conclude that the displayed currency symbol results from formatting. If the currency sign appears in the formula bar, then it a part of the cell’s text. In the next illustration, the upper image shows only a number in the formula bar, indicating that the cell contains a number; the currrency sign in the formular bar in the lower image proves that the sign is a part of the cell’s text.

To format a cell or cell range with a currency symbol, first select the cell or cell range. Then, on the Home tab, in the Number group, click the Accounting Number Format button.

 

All selected cells will be formatted with a dollar sign.

 

Excel number format:HINT (4)You can also use the Number format selection list on the Home tab, in the Number group to set the format for the selected cells. Just click the small black button at the right of the field:

To obtain a numerical format that uses the dollar sign ($), select either Accounting or Currency.

To apply the currency symbol used in another country – the European euro (texteuro) or the British (textlira) pound, for example – select the relevant cell range, and then click the small arrow on the button. The following selection list will appear:

 

Now, to format the selected cell or cells as euros, click.

 

Additional currency symbols can be obtained by clicking the arrow of the button and then selecting . This causes the Format Cells dialog box to appear: 

 

In the Symbol selection list, you will find a number of international currency symbols. Just click the currency type you want to use:

 

Excel number format:HINT (5)You can also select a currency symbol using the Format Cells dialog box. Right-click on the selected cell or cells, and then, on the Numbers tab, in the Currency category, choose a currency sign or a country in the Symbol selection list. The Accounting and Currency formats are very similar. When using the Currency format, however, no space is displayed between the monetary symbol and the number.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen