It is not a surprise to see that Excel has a dedicated formatting style designed just for accounting purposes: that’s the Accounting Number Format. In this guide today, the Software Accountant wants you to understand everything you need to know about this number format and how to use it productively.
Table of Contents
What is Number Formatting?
Number formatting in Microsoft Excel is the process of changing the appearance of numbers.
What is Accounting Number Format?
Accounting Number Format is one of the number formatting options that applies the following display features to selected cells:
- Adds a currency symbol to the left (The dollar sign is the symbol by default)
- Separates thousands with a comma
- Displays a fixed number of 2 decimal places
In other words, the Accounting number formatting is used to choose a currency symbol for a cell or group of cells, use commas to separate the cell values, and display a fixed number of decimal places.
Accounting Vs Currency Number Format?
These are two different number formats that are so similar that they can easily confuse you. In order to avoid this confusion, below are the factors that differentiate the Accounting Format from the Currency Format:
- With the Accounting Number Format (ANF), the dollar symbol is aligned to the left of the cells away from the values. But with the Currency Format, the dollar symbol is attached or close to the values. (see screenshot)
- By default, the ANF always displays negative values in parentheses, which is an accounting standard. But the Currency Format, on the other hand, displays negative numbers with a minus sign. (see screenshot)
- Finally, instead of displaying the number Zero (0), the ANF uses a dash. But the Currency Format shows Zero if it is Zero. (see screenshot)
Accounting Number Format Vs Currency Format
Think of it as a personal decision and choose whichever will fit on your worksheet. There’s really no reason to prefer one over the other. But if you are an accountant, then I think you should stick with the accounting format for your namesake.
How to apply this Number Format
Applying Accounting Number Formats (ANF) is very simple and straightforward. It only requires a couple of mouse clicks. Below are the steps to apply these number formats in Excel.
Step #1: Highlight the cells you wish to format
Step #2: Go to Home→Number→Accounting (See Screenshot)
Or just click the dollar sign (see screenshot)
At this point, you should see a dollar sign displayed in the selected cells with the values in 2 decimal places.
How to modify Accounting Number Format
Modifying this Format can also mean changing the currency sign or changing the number of decimal places and font settings. By default, Excel uses the dollar sign and two decimal places for this Number Format. But for the font, Excel uses the font settings you choose for your worksheet. However, you can modify this behavior by changing the currency and number of decimal places to use when the Accounting Format is applied. Below shows you how.
How to change the currency, number of decimals and font settings of the Accounting Number Format
Step #1: Press Ctrl+1
This shortcut will display the Format Cells dialog box. (see screenshot)
Step #2: On the number tab, select Accounting (see screenshot)
Step #3: Select the number of decimal places you want and change the currency to your choice, then click OK to apply changes (see screenshot).
You can also move to the Alignment, Font, Border, Fill, or Protection tab to fine-tune the display of the cells.
At this point, if you apply the accounting Number Format to any cell, the currency and the decimal places will change according to the choice you just made.
Speeding up things with the Quick Access Toolbar (QAT)
If you are an accountant, then you are probably using the Accounting Number Format more often than someone who isn’t an accountant. So instead of going through the whole process each time you want to modify and apply your own Accounting formats, one can just modify it to his or her satisfaction like we did by changing the currency and decimal places, record and save it as macro and add it to the QAT for easy access. Below example will explain my point very well.
Here’s the situation:
Below are two sets of numbers in two different columns. For some reasons, we want to apply the Accounting Number Format to both columns so that the first column will take up to 3 decimal places and a Dollar ($) sign whilst the second column will take 4 decimal places and a Euro (€) sign.
Here, you’ll need to use some macro to perform the task. If you have no idea about what Macro is or how to record Macro, then you’ll see it now.
Step #1: Fire up the Macro recorder
To begin recording a macro, select View→Macros→Macros→Record Macro (see screenshot)
Once the macro recorder is on, it means Excel is watching your every step. So just do only the things you want the macro to do and refrain from anything that you don’t want the macro to do. For instance, we want the macro to apply Accounting Number Format to a cell or selected cells with only 3 decimal places and a dollar symbol in action. So during the recording of the Macro, we are only going to take the required steps to do just that.
Step #2: Select the name and location to store the macro
I am going to name this macro as ThreeDecimalDollar.
Here, you’ll want to give your macro a name that is reasonable and simple. Avoid space or symbols when naming a macro. Also never begin a macro name with a letter or an underscore.
Step #3: Apply the Accounting Number Format and modify it
It is time to finally record the macro. I have already covered the steps to apply accounting number format near the beginning of this tutorials. I also explained how to modify the accounting number format by changing the currency sign and the number of decimal places. Follow the same steps to make it three decimal places and assign a dollar sign to it.
Step #4: Stop the Macro recording
Now after taking those steps, stop recording the macro by navigating to View→Macros→Macros→Stop Recording (see screenshot)
Now repeat the same process and record a macro that will apply Accounting Format to a cell and further modify it to show 4 decimal places and a Euro (€) sign as currency.
Step $5: Add the recorded macros to the Quick Access Toolbar (QAT)
At this point, we have successfully recorded our macros that holds two sets of different Accounting Formats: one with the Dollar ($) sign and the other with the Euro (€) sign. Now we are going to add these macros to the quick access toolbar to make things easy whenever we want to apply a Format with Euro or with Dollar.
To add your recorded macros to the Quick Access Toolbar, Click the drop-down arrow in the Quick Access toolbar, and then choose More Commands (see screenshot)
After selecting the More Commands, the Excel Options window opens on the Quick Access Toolbar page.
Select a category from the “Choose commands from” drop-down list. Here we are selecting the Macros category because that’s where all our recorded macros are located.
Once you select the macros category, Excel will display all the macros you recorded. If this is the first time you record a macro in Excel, then only the two macros will be available. Search and pick the two macros (FourDecimalEuro and ThreeDecimalDollar) commands from the list, and then click Add. The command moves from the list on the left to the list on the right, placing it on the Quick Access toolbar (see screenshot)
For identification purpose, you can modify the names and icons of your macros on the Quick Access Toolbar. From the list on the right, select the threeDecimalDollar, and click on modify just below the list on the right. Give it a name you desire and select one of the icons for that macro. The icon you select is what you’ll see on the Quick Access Toolbar. Take the same steps for the other macro and click OK to apply your actions (see screenshot)
Highlight your cells containing values and click your macro buttons to see your macros in action. This technique will work best for you if you perform the same task everyday.
Note: Adding commands to the Quick Access toolbar isn’t a lifetime commitment. To get rid of a command you don’t want anymore, right-click it, and then choose Remove from Quick Access Toolbar.