Typing the Thousands Separator Manually in Excel

If you’re entering data into blank cells, you can insert the thousands separator by typing a comma manually.

After pressing Enter, you can verify that Excel recognizes your input as a numerical value by selecting the cell and glancing at the Number group of the Home tab on the ribbon.

Also, if you press Ctrl+1 to launch the Format Cells dialog box, you can see that the Number format is applied, and the thousands separator checkbox is checked.

Now, you can copy this formatting to other cells, rather than typing a comma each time you input a value. First, select the cell with the thousands separator, and click the “Format Painter” icon in the Home tab on the ribbon.

Then, select all the other cells whose values you want to contain the thousands separator.

The thousands separator is now applied consistently.

Adding the Thousands Separator in Excel Using a Keyboard Shortcut

Keyboard shorcuts are a great way to work more efficiently in Excel, and luckily, there’s one for the thousands separator.

First, select the cells containing or that will contain the numbers you want to format with the thousands separator using Shift with the Arrow keys (or Ctrl+Shift+Arrows to select ranges).

Then, press Ctrl+Shift+1 to add the thousands separator.

Notice how this method also adds two decimal places to the values by default. To remove these, with the cells still selected, click the “Decrease Decimal” icon in the Number group of the Home tab on the ribbon.

Adding the Thousands Separator in Excel via the Format Cells Dialog Box

Another way to add the thousands separator to numerical values is to modify the number format in the Format Cells dialog box.

First, select all the cells to which you want to apply this change, and click the Format Cells dialog box launcher icon in the bottom corner of the Number group in the Home tab on the ribbon. Alternatively, with the cells selected, press Ctrl+1.

Then, click “Number” in the Category menu, and check the “Use 1,000 Separator” checkbox. You might find that checking this box adds two decimal places to the values in the selected cells. To remove these, type 0 into the “Decimal Places” field, or click the lower scroll arrow twice.

Finally, double-check the sample in the panel above to make sure the values appear as expected. When you click “OK,” the numbers in the selected cells will have adopted the thousands separator. I’ve opted for two decimal places in the screenshot below.

Adding the Thousands Separator in Excel Using the Comma Icon

The thousands separator is so useful that Microsoft decided to add a dedicated button in the Number group of the Home tab on the ribbon.

Specifically, selecting a cell and clicking this icon applies the comma style. So, how the cell behaves depends on the style’s settings. In my case, when I apply the comma style to a cell, the number format changes to Accounting, and two decimal places are added.

However, I want the cell formatted as a number with no decimal places. If you find yourself in the same situation, expand the “Cell Styles” drop-down menu in the Styles group of the Home tab on the ribbon, right-click the “Comma” style, and click “Modify”.

Then, in the Style dialog box, click “Format”.

Next, in the Number tab of the Format Cells dialog box, click “Number,” ensure the thousands separator checkbox is checked, review and change (if necessary) the number of decimal places, and click “OK” twice to close both dialog boxes.

Now, whenever you select a cell and click the “Comma” icon, the newly defined formatting—including the thousands separator—will be applied.

Unfortunately, there isn’t a button or checkbox to automatically add the thousands separator to all numerical values by default in Excel. However, you can speed up the thousands separator formatting process by creating and using a custom Excel template.

On the one hand, you can create a template where all the cells in the worksheet are formatted as a number with a thousands separator, essentially changing the default number format from General to Number. On the other hand, you can create a template with a modified comma style, meaning you can click the “Comma” icon on the ribbon to apply the desired formatting quickly and at any time.

Template 1: Formatting All Cells as a Number

To create a template where all the cells in the worksheet are formatted as a number with a thousands separator, open a new, blank workbook, and press Ctrl+A to select all the cells. Then, press Ctrl+1 to open the Format Cells dialog box, or click the icon in the bottom corner of the Number group in the Home tab on the ribbon.

Next, click “Number” in the Category list, and check “Use 1,000 Separator” in the main section. Also, decide whether you want numerical values to have decimal places. If not, change the number to zero. Then, click “OK”.

Now, the thousands separator formatting is applied to every cell in the worksheet.

To save this workbook as a template, click File > Save As (or press F12), and select “Excel Template” in the Save As Type field. Give the template a name in the File Name field, and click “Save”.

The file is now saved in your device’s default Office template location, so you can close the file completely.

Now, reopen Excel, and click “New” in the left-hand menu. Then, locate and double-click the template you just saved (you might need to click “Personal” to find it).

This opens a new Excel workbook, meaning any changes you make won’t affect the template. When you type a number with four or more digits and press Enter, the thousands separator is applied instantly.

Template 2: Modifying the Comma Style

To create a template with a modified comma style, first, open a new, blank Excel workbook. Then, click the “Cell Styles” down arrow in the Styles group of the Home tab on the ribbon, navigate to and right-click the “Comma” style, and choose “Modify”.

Next, in the Style dialog box, click “Format”

Now, click “Number” in the left-hand Category menu, and ensure “Use 1,000 Separator” is checked. While you’re there, state the number of decimal places you want numbers in this style to have. Then, click “OK” twice to close both dialog boxes.

Now, to save this workbook with the modified comma style as a template, click File > Save As (or press F12), and select “Excel Template” in the Save As Type field. Name the template in the File Name field, and click “OK”.

Close the template you just created before reopening Excel and clicking “New” in the left-hand menu. Then, locate and double-click the template you just saved.

This opens a new Excel workbook, leaving the template unaffected. Because you’ve created the workbook from the template with the modified comma style, when you select a cell and click the “Comma” icon, the value in the cell will adopt the thousands separator.