Using the Accounting Number Format in Excel is easy and can be done in a variety of ways.
You will learn to apply accounting number format in Excel by using the Number Format button, the Format Cells window, and the drop-down menu in the number group.
Another option is to use the TEXT function in the formula bar to use the accounting format. You will also learn to add the Quick Access Toolbar to do this task easily, which will save you time.
For additional customization, use the Custom Format to create your format pattern. These settings allow you to easily customize the Accounting Number Format in Excel to your individual needs.
Download Practice Workbook
You can download our practice workbook here for free!
What Are the Ways to Apply Accounting Number Format in Excel?
In this article, we will show several ways to apply the accounting number format in Excel. We will use the Number Format button, the Format Cells window, the drop-down menu in the number group, and many ways in Excel. You can follow these methods with suitable examples to learn details about Excel accounting number format.
How to Apply Accounting Number Format Button?
To show the appropriate currency in the corresponding value, use the Accounting Number Format button in Excel.
- Select a range of cells you want to format.
- Then, go to the Home tab.
- Click on the Accounting Number Format button.
- We have chosen the accounting currency “$English (United States)”. If you need to use a different currency, go to the dropdown menu next to the Accounting Number Format button and select the appropriate currency.
- You will see the output in the following image.
How to Apply Accounting Number Format Using Drop-Down Menu in Number Group?
You can apply currency format in your specific asset value from the Number group.
- We will select the range of cells from cell C5 to cell C10.
- Find and select the Dropdown menu button in the Number group on the Home tab.
- Now, in the Dropdown menu, select Accounting from the list of formats.
- You will see all the selected cells have been formatted in the below image.
How to Apply Accounting Number Format Using Format Cells Window?
To display the correct currency alongside its corresponding value, use the Format Cells window.
- Choose the range of cells to be formatted.
- Right-click on the selection or press Ctrl+1 to open the Format Cells window.
- Now, you will see the following window.
- Click on the Format Cells option.
- In the Format Cells window, choose Accounting from the Number tab.
- Select your desired currency format and click OK.
- In the following image, you will see the result.
How to Apply Accounting Number Format Using Keyboard Shortcut?
If you want to show the proper currency next to a value, use the Keyboard Shortcut technique.
- Select the range of cells.
- Press the keys Alt+H+N+A. This will select the Accounting format from the Home tab’s dropdown menu. Then, in order to accept the formatting selection, press the Enter key.
- Now, you will see the output here.
How to Apply Accounting Number Format Using Quick Access Toolbar?
You can add a Quick Access Toolbar to apply the correct currency format according to your wish.
- Find and Right-click the Dropdown menu button in the Number group on the Home tab.
- Choose the Add to Quick Access Toolbar option.
- This will add a little dropdown list to the Quick Access Toolbar area, which you may utilize whenever you want.
- Now, choose the Accounting format from the Quick Access Toolbar after selecting the range of cells, and you will see the output.
How to Apply Custom Format As Accounting Number Format?
We will create a custom accounting format to apply the desired currency format next to our cell value.
- Choose the numbers to format.
- To access the Format Cells menu, use the Ctrl+1 keyboard shortcut.
- Navigate to the Number tab.
- In the Category drop-down menu, choose Custom.
- Copy and paste the format string into the Type input.
_ $* #,##0.00 _ ;[Red] $* (#,##0.00)_ ;_ $* "-"?? _ ;_ @_
- Click the OK button.
- Now, you will notice all the number cells have been formatted.
How to Apply Accounting Number Format Using TEXT Function?
Apply the TEXT function to show the value with the appropriate currency format.
- Select cell D5 and write down the following formula.
="$"&TEXT(C5,"#,##0.00_);(#,##0.00)")
- Click Enter.
- Now, drag the Fill Handle icon downward to get all the results.
- Finally, you will find that all cells have been formatted in the following image.
What Are the Ways to Increase/Decrease Decimal Numbers in Excel Accounting Number Format?
In this part, we will look at how to increase and decrease decimal numbers in Excel.
How to Use Format Cells Window?
If you want to Increase or decrease the decimal values according to your requirements.
- Select the number cells where you can see two decimal places.
- Press Ctrl+1 to open the Format Cells window.
- Now, we will decrease the two decimal places to zero (0) decimal places. You can increase decimal values according to your needs.
- In the Category drop-down menu, choose the Accounting option under the Number tab.
- So, write zero (0) in the Decimal Places option.
- Then, click on the OK button.
- You will see the difference in the following image.
How to Use Decimal Button in Ribbon?
Here, we will show how to Increase or decrease the decimal values according to your requirements.
- Select the number of cells, like in the following image.
- Go to the Home tab.
- Choose the Decrease Decimal button from the Number group.
- We will decrease two decimal places to zero (0) decimal places.
- That is why, we will double-click on the Decrease Decimal button.
- Now, see the result.
Also, we will show how to increase decimal places for a better understanding.
- Choose the number of cells, like in the following image.
- Select the Increase Decimal button from the Number group under the Home tab.
- We will increase zero (0) decimal places to three decimal places.
- Now, we will click three times on the Increase Decimal button.
- You will see that all the zero decimal places numbers have been converted into three decimal places numbers.
How to Remove or Change Symbols in Excel Accounting Number Format?
To know how to change or remove the accounting number format in Excel, follow this section accordingly.
- Select the number cells, and all the cells are in Accounting format with United States currency. We will change this format to another currency format.
- Go to the Home tab.
- Click on the Number Format button in the Number group.
- Now, choose the Euro currency.
- You will find all the numbers changed to Euro currency in the following image.
Things to Remember
- When inputting values, the user can choose whether or not to include decimal places.
- When you use a custom Excel number format, you change the visual look of a cell’s value without changing the recorded value. When a format is modified, a copy of that format is created; the original format remains unmodified and cannot be erased.
- Custom number formats simply affect how numbers appear in the worksheet; they have no effect on the original numeric value.
Frequently Asked Questions
1. What are the advantages and disadvantages of Excel’s Accounting Number Format?
The Accounting Number Format in Excel offers benefits such as clear alignment and formatting, increased readability, simple currency symbol insertion, consistent decimal places, and negative number handling.
However, It has some drawbacks, such as restricted customization possibilities, probable calculation concerns, incompatibility with formulas, a lack of globalization, and data analysis difficulties.
2. What are the differences between Excel Accounting number format and Currency format?
The Accounting Number Format aligns currency symbols and decimal places, uses parentheses for negative numbers, and ensures that all values have consistent decimal places.
The Currency Format, on the other hand, simply adds the currency sign to the numbers without any alignment or special treatment for negative numbers.
3. Can I use conditional formatting along with the Accounting Number Format?
Yes, you can apply conditional formatting alongside the Accounting Number Format to highlight cells that meet specific criteria. Conditional formatting allows you to visually emphasize certain data based on the rules you define.
Conclusion
In this article, we have shown simple ways to apply accounting format and demonstrated different types of ways by giving multiple examples with proper explanations. You will discover how to employ the accounting number format in Excel through methods such as the Number Format button, the Format Cells window, the dropdown menu in the Number group, and many more. Each method has advantages and is appropriate for different situations. You can browse our website and check our knowledge hub section for more valuable and informative articles about how to apply accounting formats in Excel.
Excel Accounting Number Format: Knowledge Hub
<< Go Back to Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!