Filter by Categories
Select all
Budget
Business Statements
Calendars
Federal Income Tax
Gantt Chart
GST India
Health, Logs and Personal Trackers
Inventory
Investments
Invoice/ Bill
Maintenance
Payroll & HR
Project Reports & Timelines
Purchase Order/Requisition
Quotation
Rental Ledger
Risk Registers
Sales/ Marketing
SWOT Analysis
Timesheet/ Attendance
UAE VAT
UK VAT
Filter by File Types






How to apply accounting number format in Excel ?

How to apply accounting number format in MS Excel

Background

In Excel, Accounting Format and Currency format are similar concepts with minor differences. You can learn to apply accounting number format in excel with the help of this tutorial blog.

Whenever you are working with a data set that has currency or money figures, there are two formatting options available – the Currency format and the Accounting number format.





The main point that sets apart the Accounting number format from other number formats is that it displays numbers with the currency symbols and decimal points aligned perfectly in a column. This gives the dataset an aesthetic look, enhanced visualization and makes it easy to read. Refer the image below to understand how a data set can look like – when you apply accounting format in excel

Accounting Format in Excel

Methods for applying accounting number format in excel

Before jumping into the tutorials for applying accounting number format in Microsoft Excel, let us first understand what methods are available for this –

  • Accounting Number Format With a Ribbon Option
  • Accounting Number Format With a Drop-Down Menu
  • Accounting Number format by formatting cells option

Now we will briefly discuss each of the methods highlighted above and master the art of applying accounting number while working with dataset that contains money figures or amount –

Apply the Accounting Number Format With a Ribbon Option

Excel has an option in its ribbon tab to help you quickly use the accounting number format in your spreadsheets and excel workbooks. This is the easiest and quickest way to apply the accounting number format.



  • To use it, first, open your excel workbook or spreadsheet with Microsoft Excel.
  • Now, select the cells containing the numbers that you desire to turn into accounting numbers.

Click on Home and then on Dollar sign

  • After selecting the cells, Go to the Home tab in Excel.
  • In Home Tab, Under the Number group [Refer the image above], you will see the Accounting number format shortcut button (Symbolized by a dollar sign).
  • Now, select the button if you want to apply the dollar ($) sign. If you want to apply any other currency symbol, you can click on the dropdown arrow next to the button and select the currency you need from the dropdown list that appears. By default, there are other currencies available for quick selection like Pound, Dollars, Euro, Yen etc.

Select the currency you want to show

  • Your selected cells should now be formatted with the Accounting number format. You will notice your currency symbol that you have selected to the extreme left of the selected cells.

Dollar sign at the left of each cell

  • You will also notice the number formatted to two decimal points. If you desire to change the number of decimal places. You can click the ‘Increase decimal’ or ‘Decrease decimal’ buttons to increase or decrease the number of decimal places as per your requirement.

Click on decrease increase decimal

Remember – You will also notice a thousand separators. As long as you’re in the Accounting number format, you cannot remove this separator from your number.

Apply accounting number format with a Drop-Down Menu

In Continuity to the above tutorial on applying accounting number format in excel, the Number group (under the Home tab) provides another easy shortcut to format numbers in your workbook and spreadsheet, and it is through the dropdown menu located right above the Accounting number button.

In order to use this method, you can follow the steps below –



  1. First, Select the cells in your workbook and spreadsheet which you want to format.
  2. Go to the Home tab.
  3. In Home Tab, under the Number group, you will see the format dropdown menu, as shown in the figure below.Click on the General drop down
  4. Selecting the menu displays a list of different formats that you can apply to your selected cells.
  5. Now, you have to select the Accounting’ option from the dropdown list.Click on Accounting option
  6. After clicking the “Accounting” option, your selected cells should now be formatted with the Accounting number format. You will notice the currency symbol to the extreme left of the selected cells.Dollar sign at the left of each cell
  7. Just like in the tutorial above where we used the shortcut to change the currency figures, If you want to apply any other currency symbol, you can click on the dropdown arrow next to the button with the dollar sign (‘$’). Select the currency you need from the dropdown list that appears.Select the currency you want to show
  8. Also, You will also notice the number formatted to two decimal points. You can click the ‘Increase decimal’ or ‘Decrease decimal’ buttons to increase or decrease the number of decimal places according to your requirement.Click on decrease increase decimal

Now, You will also notice a thousand separators. As long as you’re in the Accounting number format, you cannot remove this separator from your number.

Applying accounting number format by formatting cells option

This is another cool method to apply accounting number format in our excel workbook and spreadsheet. The Format cells dialog box is a versatile dialog box that lets you perform all formatting for a cell or range of cells (including alignment, font, borders, etc.) from one place.




In order to apply the Accounting number format to your cells using the Format cells dialog box, you can follow the steps below:

  1. First, select the cells in your workbook or excel spreadsheet which you want to format.
  2. Then, mouse Right-click on your selection cells or range.
  3. From the context menu that appears, select the ‘Format cells’ option.Right click and then click on Format cells
  4. This will open the Format cells dialog box.
  5. From the dialog box, select the ‘Number’ tab. (Refer below)Make sure you are in the Numbers tab
  6. In Format Cells Dialog box, Under ‘Categories’, select the ‘Accounting’ option.Click on Accounting opton in the dialog box
  7. This will display a sample on the right-hand side of the dialog box so that you can see how your number is going to look after formatting.Sample Preview of accounting format
  8. By default, the number should be formatted with a dollar sign. If you want to apply any other currency symbol, you can click on the dropdown menu next to ‘Symbol’ and select the currency you need from the dropdown list that appears.Select the currency symbol
  9. You will also notice the number formatted to two decimal points. You can increase or decrease the number of decimal places according to your requirement by pressing the up or down arrows next to ‘Decimal places’.Specify the decimals you want
  10. Click OK to close the Format cells dialog box.
  11. Your selected cells should now be formatted with the Accounting number format.Accounting format result

Why choose accounting number format over currency format ?




This is one important topic which everyone should consider and understand to master the formatting options in excel. Both the Currency and the Accounting Number, in the first look, look like they’re more or less the same. However, if you look closely, you will observe the following differences  –

  1. The Currency format displays zero values as ‘0.00’, whereas the Accounting number format displays zero values as a dash ‘-‘.
  2. Now, The Currency format displays the currency symbol right next to the number. The Accounting number format, on the other hand, displays the currency symbol at the extreme left of the cell.
  3. Also, The Currency format displays negative numbers with a minus ‘-‘ symbol, while the Accounting number format displays negative numbers within parentheses.

The above three differences make the numbers formatted with the Accounting number format better suited for accounting applications.

Refer the visual example below to understand the differences between currency formatting and accounting number formatting in Microsoft excel –

Visual difference between currency and accounting format

Content

Macro Codes in Excel

Learn Excel Quickly
Get full list of Excel Macro Codes