Currency Vs Accounting Format in Excel
MS Excel or Microsoft Excel is currently the most popular and powerful spreadsheet software used to record data within cells across multiple worksheets. It enables users to store huge amounts of data in each worksheet. Moreover, it supports various number formats, such as numerical values, text, percentage, fraction, dates, etc. In Excel, Currency and Accounting are two different number formats used to change the view of numbers within the cells. Although they seem quite similar in Excel cells, there are significant differences between them.
This article discusses some of the essential differences between currency and accounting formats of MS Excel. The article also includes practical examples that will help us understand or identify whether the numbers entered within cells are formatted as currency or accounting.
What is the Currency and Accounting Format in Excel?
The Currency format refers to a built-in number format of MS Excel used particularly for currencies like prices. It adds a currency sign before the entered values within the formatted cells. The currency format is most commonly used for denoting general monetary values in Excel cells. We can choose to add decimal places accordingly.
The Accounting format refers to another special built-in number format that seems and even works in the same way as currency format; however, unlike the currency format, the accounting format aligns (or lines up) the currency sign and values in each respective cell or column in the worksheet. It is very easy to read or understand the prices within cells with the accounting format used.
Note: The currency sign in both currency and accounting formats is added based on the geographic location selected in the respective device. However, it can be changed according to the need. Likewise, we can choose decimals places to include in cell values.
Difference between Currency and Accounting Format
The major differences between the currency and accounting formats of Excel are tabulated below:
| Excel Currency Format | Excel Accounting Format |
|---|---|
| The Currency format aligns the currency sign at the left edge of the value in the cell. | The Accounting format aligns the currency sign at the left edge of the cell, nearest to the border. |
| We can choose to align values in left, right, or center. | We cannot choose to align values. It strictly keeps the values right justified. |
| There are no extra spaces between the currency sign and the value entered in the cell in currency format. | In Accounting format, extra spaces appear between the currency sign and the values. |
| The currency format displays zero values. | The accounting format displays a dash (-) for zero values. |
| Currency format allows displaying negative values with a minus sign, in red color, with parentheses, or in red color with parentheses. | Accounting format only allows displaying negative values in parentheses. |
| The currency format is flexible for formatting purposes but less preferable for visibility. | The accounting format does not allow much flexibility in formatting, but it has great visibility, making it easy to find amounts or currencies in sheets with large data sets. |
How to implement Currency or Accounting Format in Excel?
When we need to apply currency or accounting format in desired Excel cells, we must perform the following steps:
- First, we need to select/ highlight all the cells we need to format in the sheet. We can hold down the Ctrl or Shift key while selecting the cells for non-contiguous or contiguous Excel cells, respectively.
- After selecting the effective Excel cells, we need to launch the Format Cells dialogue box. We must press right-click on cells and choose the Format Cells option from the list to open this. Alternately, we can use the keyboard shortcut ‘Ctrl + 1’.
- Once the format cells dialogue box is displayed, we must select the desired format (Currency or Accounting) under the Category section from the Number tab.
- After selecting the desired format, we must choose the currency sign and decimal places, check the sample/ preview, and click the OK button.
Explanation of Currency Vs Accounting Format with Examples
Although we have discussed the differences between Excel’s Currency and Accounting formats, it is always better to understand a concept with an example. Let us take an example Excel sheet with some amounts or prices in columns and format the same data in Currency and Accounting format separately. This will help us get a clear view of both the formats, explaining how they differ.
Let’s take the following worksheet for example purpose:
Example of Currency Format
First, we will change the amounts to a Currency format. For this, we need to perform the following steps:
- First, we select the effective cells or a range B2:D13.
- Next, we launch the Format Cells dialogue box using the shortcut ‘Ctrl + 1’.
- In a dialogue box, we choose the Currency format from the list under Category, choose a dollar to be used as the currency sign and put decimals up to two digits. It will look like this:
- After making all the changes for the Currency format, we click the OK button. After that, we see that the dollar sign is added to all the cells in a selected range B2:D13. The currency format adds the currency sign ($) just before the amounts of numbers, as shown below:
Example of Accounting Format
Now, we will change the amounts to Accounting format. For this, we need to perform the following steps:
- Like the previous method, we first select the effective cells or a range B2:D13.
- After that, we open the Format Cells dialogue box using the shortcut ‘Ctrl + 1’.
- We choose the Accounting format from the list under Category in a Format Cells dialogue box. Additionally, we choose the dollar as the currency symbol and keep the decimals up to two digits, similar to the currency format arrangement. It looks like this:
- After making all the changes to the Accounting format, we click the OK button. Although the dollar sign is added to all the cells in a selected range B2:D13, however unlike the currency format, it is strictly aligned to the left edge of corresponding cells. This way, the amounts, and currency symbols are more clearly visible, as shown below:
Looking at the examples of both formats side by side, we can easily see the difference in currency symbols. While the currency format adds the currency symbol without any alignment, the accounting format aligns the currency symbol to the left of the corresponding cells, as shown below:
