MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Replace the Comma Style in Book.xltx

February 17, 2020 - by

Excel Replace the Comma Style in Book.xltx. Photo Credit: Javier Reyes at Unsplash.com

The Excel team offers Currency, Percent, and Comma icons in the center of the Home tab of the Ribbon. The tooltip says the Comma Style formats with a thousands separator. I despise this icon.

On the Home tab of the Ribbon, in the Number group, there are icons for Dollar, Percent, and Comma. In this figure, the tooltop for the Comma Style says Format With A Thousands Separator.

Why do I despise this icon? Because it turns on Accounting style. Sure, that gives you a thousands separator, but it also adds several things that I hate:

  • It turns on two decimal places.
  • It uses a right indent of 1 character to move the last digit away from the right edge of the cell.
  • It uses parentheses for negative numbers.
  • It displays zero with a single dash about four spaces away from the right edge of the cell.
The effects of clicking the comma icon: You get a thousands separator (as expected). But also two decimal places, numbers displayed as a hypen, one space indent from the right edge, and negative numbers displayed in parentheses.

There is no way to replace the Comma icon with my own icon or even to change what style it applies. So, I find that I have to click the Dialog Launcher icon at the bottom right of the Number group:

Instead of clicking the Comma icon, use the Number Format dialog launcher just below the Decrease Decimal icon.

Tip

The Dialog Launcher icon is a diagonal arrow pointing down and to the right. It is found in many groups in the Ribbon and usually offers far more choices than are available in the Ribbon.

Then choose Number from the Category list, choose the checkbox for Use 1000 Separator, and click twice on the down arrow to change 2 decimal places to 0 decimal places. Click OK to close the Format Cells dialog. It takes six clicks to create a simple number format with a comma as the thousands separator. That is why I despise the Comma icon: People who can live with right indents, parentheses, and zeros displayed as dashes can apply that style in one click, but people who just want a comma have to go through six clicks.

In the Format Cells dialog, click the Number tab across the top. Choose the Number category along the left. Set the Decimal Places to 0. Choose Use Thousands Separator.

The great news: There are two solutions. The bad news: Microsoft makes it hard to use the solution. The good news: If you add the solution to the Book.xltx file, the solution will become mostly permanent for all files that you create. Here is what you do:

  1. While you are creating Book.xltx, as discussed in "Excel 2020 - Use Default Settings for All Future Workbooks"

  2. Open the Cell Styles gallery. Near the bottom, choose New Cell Style...

    Open the Cell Styles gallery. Near the bottom is a menu item for New Cell Style.
  3. In the Style box that appears, type a descriptive name for your style, such as CommaGood.
  4. If you only want to apply the Number format, unselect the checkboxes for Alignment, Font, Border, Fill, and Protection.

  5. Click OK to create the new style.

    This figure reveals that your newly formatted numbers are selected and the Style dialog is shown. The new style name is CommaGood. In the section called Style Includes (By Example), only Number Format is selected. The other choices for Alightment, Font, Border, Fill and Protection are not selected.

New styles appear at the top of the Cell Styles gallery, and you now have one-click access to the CommaGood style.

At the top of the Cell Styles gallery, before Normal, your custom style of CommaGood appears.

Update from John Matzak: Astute reader John pointed out that the Comma style is stored in the same gallery and you can edit the comma style.

  1. Open the Cell Style Gallery.

  2. Right-click on the Comma style and choose Modify...

    Comma Style in Cell Style Gallery
  3. In the Style dialog box, click Format... .
  4. In the Format Cells dialog, choose Number, 0 Decimal Places, Use 1000 Separator, and change Negative Numbers to the black -1,234.
  5. Click OK to close the Format Cells dialog.
  6. Click OK to close the Style dialog.

For the rest of the life of this workbook, clicking the comma icon in the Home tab will only add a 1000 separator.

Caution

Any cell style modified using either method applies only to the current workbook, making this tip nearly useless.

Tip

If you add the CommaGood style to your Book.xltx file, the CommaGood style will be available on all future workbooks that you create with Ctrl+N.

Thanks to Jo Ann Babin for an idea similar to this one.

Title Photo: Javier Reyes at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.