Excel 2019: Replace the Comma Style in Book.xltx
March 05, 2019 - by Bill Jelen
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.
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.
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:
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.
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:
- While you are creating Book.xltx, as discussed in "Excel 2019 - Use Default Settings for All Future Workbooks"
Open the Cell Styles gallery. Near the bottom, choose New Cell Style...
- In the Style box that appears, type a descriptive name for your style, such as CommaGood.
- If you only want to apply the Number format, unselect the checkboxes for Alignment, Font, Border, Fill, and Protection.
Click OK to create the new style.
New styles appear at the top of the Cell Styles gallery, and you now have one-click access to the CommaGood style.
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.
- Open the Cell Style Gallery.
Right-click on the Comma style and choose Modify...
- In the Style dialog box, click Format... .
- In the Format Cells dialog, choose Number, 0 Decimal Places, Use 1000 Separator, and change Negative Numbers to the black -1,234.
- Click OK to close the Format Cells dialog.
- 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.
Any cell style modified using either method applies only to the current workbook, making this tip nearly useless.
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.
Title Photo: Javier Reyes at Unsplash.com