Forum Discussion

IvanWilson's avatar
IvanWilson
Iron Contributor
Jul 22, 2024
Solved

Can you change the default comma number format to exclude decimals?

Is there any way to configure Excel to not add decimal places when you use the "comma" format option in the ribbon? 

 

I always have to click comma and then click the decrease decimal places twice. I believe the formatting is based on your regional settings, but it would be nice if there was an option to override the decimal places with the application.

  • IvanWilson 

    Comma is a style. You can modify this style to have 0 decimal places.

    On the Home tab of the ribbon, click Cell Styles, then right-click Comma.

    Select Modify..., then click Format...

    Change Decimal places to 0, then cick OK.

    This will apply to the active workbook only.

    But you can create a new workbook, modify the Comma style as described, then save the workbook as an Excel template named Book.xltx in your startup folder for Excel  - by default %appdata%\Microsoft\Excel\XLSTART. The name Book.xltx is obligatory (but it depends on the language).

    It will then be used when you start Excel, and also when you create a new workbook by pressing Ctrl+N or by clicking New on the Quick Access Toolbar.

  • IvanWilson 

    Comma is a style. You can modify this style to have 0 decimal places.

    On the Home tab of the ribbon, click Cell Styles, then right-click Comma.

    Select Modify..., then click Format...

    Change Decimal places to 0, then cick OK.

    This will apply to the active workbook only.

    But you can create a new workbook, modify the Comma style as described, then save the workbook as an Excel template named Book.xltx in your startup folder for Excel  - by default %appdata%\Microsoft\Excel\XLSTART. The name Book.xltx is obligatory (but it depends on the language).

    It will then be used when you start Excel, and also when you create a new workbook by pressing Ctrl+N or by clicking New on the Quick Access Toolbar.

    • IvanWilson's avatar
      IvanWilson
      Iron Contributor

      HansVogelaar, I've tried creating the Bool.xltx file and saving it to %appdata%\Microsoft\Excel\XLSTART. It was automatically moved into my "Default personal templates location" folder. However, it doesn't appear to be used when I create a new workbook. Any suggestions?

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        IvanWilson 

        The template should be named Book.xltx, not Bool.xltx

        In File Explorer, move the template from your default templates location to %appdata%\Microsoft\Excel\XLSTART

Resources