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.

Built-in Excel Format Options by Jeff Lenning

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.

Excel Financial Statement by Jeff Lenning

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.

Excel Macro by Jeff Lenning

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.

Record Excel Macro by Jeff Lenning

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.

No Symbol Accounting Macro by Jeff Lenning

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.

Excel QAT by Jeff Lenning

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.

 

 

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

13 Comments

  1. Rita H on January 21, 2016 at 5:05 am

    Jeff,
    Thanks for the idea! I have been recording macro’s for years but I never considered adding them to the QAT or storing them in the personal Macro workbook. I keep them in the workbook where I wrote them. I don’t need to use the format ones you listed here but I am constantly setting the row height on my workbooks so I am going to record that and add that to the QAT. Keep up the great work and ideas please!

    • jefflenning on January 21, 2016 at 5:07 am

      🙂

  2. Melody on January 21, 2016 at 5:56 am

    Thanks, Jeff. One place I am constantly re-formatting cells is in pivot table value field settings. Do you know way to automate that? Or to set a default format for pivot tables?

    • jefflenning on January 21, 2016 at 6:39 am

      Hi Melody!
      Actually, this question comes up quite a bit, so, I’ll actually use this as a topic for an entire new blog post…thanks!
      Thanks,
      Jeff

      • Melody on February 5, 2016 at 8:21 am

        I just read the new post today answering my question with a macro. Thanks!!

        • jefflenning on February 5, 2016 at 8:22 am

          Glad it helped 🙂

  3. Jared on January 21, 2016 at 8:23 am

    Is there is a difference/benefit to using the Accounting format over the Number format or Currency format? For the numbers format, it easy to click on comma button to apply the number format without the $.

    • jefflenning on January 21, 2016 at 8:28 am

      Jared,
      The main difference between the accounting and currency formats is that with the accounting format, the currency symbols and decimals will be lined up within the column. The currency format will place the currency symbol immediately before the first digit, so, when one cell has 100 and another has 10,000 the symbols won’t be lined up. That is the main difference….thanks for the question!
      Thanks,
      Jeff

  4. Kevin on January 21, 2016 at 10:14 am

    Very helpful! Thank you.

  5. Linda on January 25, 2016 at 8:50 am

    You just saved me a lot of time! Thank you!

  6. Ирина on March 31, 2016 at 2:41 pm

    The “comma” button on the toolbar sets the accounting format which shows two decimal places. Otherwise that would work for my needs.

  7. Evan on September 14, 2017 at 8:32 am

    Jeff, you are awesome

    • Jeff Lenning on September 14, 2017 at 8:33 am

      Thanks 🙂

Leave a Comment