The accounting number format in Excel is useful when you’re working with numbers with a currency symbol.
The accounting number format makes it easier for you to read currency data, especially with larger tables. The format lines up the currency symbols and decimal points in a column.
Excel offers various options for formatting numbers in a spreadsheet. Numbers can be shown as a date, a currency, a percentage, or even as a fraction. When handling monetary values, Excel offers the Accounting number format, which is a quick way to make your spreadsheet look cleaner and easier to browse.
We’ll first look into a quick example where we might want to use the Accounting Number format in Microsoft Excel.
If you are working with a spreadsheet with columns full of transactions or financial data, it might be difficult to make sense of it. Stating the currency is important, but it often crowds the cell and makes it difficult to read numbers.
Values for financial data should also have commas between digits to make it easier to compare data in the millions and billions range.
The Accounting Number format is a quick way to convert your financial data into an easy-to-read format.
This number format is common in all kinds of spreadsheets, including transaction data, cash flow statements, and inventory trackers.
Now that we know when to use the accounting number format, let’s look into how it looks on an actual spreadsheet.
A Real Example of Using the Accounting Number Format in Excel
Let’s take a look at a real example of the Accounting number format being applied in an Excel spreadsheet.
In the example below, we have a table showing a list of orders. The Accounting Number format was applied to the Amount column. The decimal places and commas of each value are aligned, making it easier to compare each amount.
Values equal to 0 are shown as a “-” but still retain the original value of 0. The format also shows two decimal places, even if the number itself is not a decimal.
The usefulness of this format becomes more apparent when dealing with numbers of larger magnitude. In the example below, you can see the difference between how numbers are shown by default in Excel and how they are presented with the Accounting Number Format.
The inclusion of the two decimal places and commas makes it easier to compare values across the entire column.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to test out the Accounting Number format in Excel, let’s learn how to write it step-by-step!
How to Use the Accounting Number Format in Excel
This section will guide you through each step needed to use the Accounting Number format in Excel. You’ll learn two ways to make your spreadsheet easier to read. We’ll also show you how you can change the currency of your financial data.
We’ll be using the simple table below for this example. We have a list of income sources and their corresponding contribution.
Simply follow these steps to start applying the Accounting Number format on monetary values:
- First, we should select the range that includes the data we want to format. In this example, we select C2:C5.
- In the Home tab, you’ll find a dropdown box under the Number section. Click on the dropdown menu and select the Accounting option.
- Your selected range should now follow the Accounting Number Format.
Here is an even simpler way to format your financial data:
- Select the range you would like to format. Like in the prior method, we’ll be selecting the cell range C2:C5.
- Next, look for the currency icon in the Number block. Clicking on it will automatically format your values. You can also select a different currency by clicking on the drop-down arrow.
- You can also find this icon by right-clicking on your selection.
- To choose a currency, click on “More Accounting Formats..” in the dropdown menu seen earlier. You will find a Format Cells pop-up where you can select which symbol you would like to use for the Accounting Number Format. You may also change the number of decimal places to include in the formatted cell.
Frequently Asked Questions (FAQ)
- What is the difference between the Currency format and the Accounting format?
Similar to the Currency format, the Accounting number format is best used when dealing with values with a currency.
The main difference between the two is that the Accounting symbol aligns the currency symbols to the left and displays zero values with a hyphen. - Does the original value change when I apply a new format?
Applying the Accounting number format to a cell does not change the cell’s original value. The format simply changes how the user sees the value in the spreadsheet.
That’s all you need to remember to start applying the Accounting Number format in Microsoft Excel. Use this step-by-step guide to make your financial spreadsheets easier to read.
The Accounting number format is just one of many formatting options in Excel. With so many other Excel functions and features out there, you can indeed find a few that can help improve your worksheet.
Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
