Accounting Number Formats QAT

Excel has cell formatting designed just for accountants. Not surprisingly, it is called the Accounting Number Format. The built-in ribbon commands apply the format with two decimals and the currency symbol. This post demonstrates how to set up two handy QAT icons so that we can quickly apply this format with no decimals and without a currency symbol.

Objective

Before we get to the mechanics, let’s be clear about our goal. The accounting number format can be applied with the Accounting Number Format ribbon icon or by selecting Accounting from the Number Format drop-down. Both of these built-in Home ribbon tab options are shown below.

Both of these commands apply the accounting number format with a currency symbol and two decimals. While this format works well in many situations, sometimes, we need additional options. Consider a basic financial statement, as illustrated below.

Here, we don’t want to display any decimals and we only want the currency symbol displayed in the first and last rows. After applying the standard accounting format we need to make some updates manually. Namely, we need to remove the decimals (perhaps by clicking the Decrease Decimal icon twice) and update the formatting so symbols are displayed as desired.

To help us apply the desired formats more quickly, we’ll set up two new Quick Access Toolbar (QAT) icons to execute two new macros. One will insert the accounting number format without decimals and one will apply the format without a currency symbol.

Macros

First, let’s do the macros. Begin by selecting any cell in any worksheet. Then, fire up the macro recorder by using the following ribbon icon:

• View > Macros > Record Macro

In the resulting Record Macro dialog box, you can enter a descriptive macro name, but avoid spaces and funky characters. A macro name like AccountingNoDecimal would be perfect. Then opt to store your macro in the Personal Macro Workbook, which is a hidden workbook always open when Excel is open. We want to save our macro here so that we can run it anytime Excel is open.

Next, click the OK button.

Now…Excel is watching and recording everything you do…so do NOT click around to a different cell, click a different worksheet, click any icons, or do anything. Except, apply an accounting format with no decimals…you can quickly do this with the Format Cells dialog box. Open the dialog by pressing Ctrl+1 or the following ribbon icon:

• Home > Format > Format Cells

In the Format Cells dialog, on the Number tab, click Accounting and then 0 decimals, as shown below.

Click OK to dismiss the dialog and apply the format.

Now…Excel is still watching, so, before doing anything else, stop the recorder. You can quickly stop it by clicking the following Ribbon icon.

• View > Macros > Stop Recording

Congratulations…you just recorded a macro that you can use anytime to apply the accounting number format without decimals. Before we set up an icon to make it easy to execute, we may as well record another macro to apply the accounting number format without a currency symbol. This will work very much like our last macro.

Open the Record Macro dialog (View > Macros > Record Macro), enter a macro name such as AccountingNoCurrency, save it to the Personal Macro Workbook, and click OK. Then, immediately apply an accounting format with no decimals or currency symbol, as shown below.

Then stop the recorder.

With the two new macros recorded, all we need to do is make them easy to execute. For this, we’ll set up two new icons in the QAT.

QAT

To customize the QAT, open the Excel Options dialog. You can do this either by selecting More Commands from the little drop-down on the right side of the QAT or by using the File > Options command. Once the Excel Options dialog is open, be sure that the Quick Access Toolbar category on the left is selected.

Next, you want to select Macros from the Choose commands from drop-down. Then, select the desired macro, such as AccountingNoDecimal, and click Add>> to add it to the QAT. Do the same thing for the AccoutingNoSymbol macro. If you’d like, you can Modify the icon images to make them distinct and thus easy to tell apart once on the QAT. For example, there is a little dollar symbol icon that you can use as well as many other choices.

Once you click OK, you’ll see the new macro commands added to the QAT. Which is great, because now applying the desired accounting number format is a single click away!

I hope this enables you to get your formatting completed faster than before! If you have any other formatting tips, please share by posting a comment below…thanks!

Note: watch for a dialog next time you exit Excel asking if you want to save changes to the Personal Macro Workbook. In fact, you may want to intentionally close Excel now, while you are thinking about it, so that you remember to click Yes. If you dismiss this dialog without saving the Personal Macro Workbook changes your macros will need to be re-recorded.

Notes

• Sample Excel file (includes the macros): AccountingFormats.xlsm
• If you don’t want to record a macro to apply the accounting number format without decimals, you can use the built-in cell style Currency [0] instead.
• In addition to clicking the QAT icons with your mouse, you can press the Alt key on your keyboard to discover the assigned Alt shortcut key.