The thousands separator is a character used to separate groups of three digits in larger numbers, such as the commas in 1,000,000. In Microsoft Excel, you can format numbers to include the thousands separator. In this guide, I’ll show you how.
The main reason using the thousands separator is the preferred option is that it makes numbers easier to read, since it breaks long strings of digits into smaller groups. What’s more, the thousands separator reduces the chance of errors when you’re typing lengthy numbers manually, as it’s easier to verify that you’ve typed the correct number of digits.
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.
If the comma is typed in an invalid place within the number, Excel converts the cell input into a text value.
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.
Double-click “Format Painter” to copy the formatting to non-contiguous cells or ranges.
Adding the Thousands Separator in Excel Using a Keyboard Shortcut
Keyboard shortcuts 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.
Making the Thousands Separator the Default Excel Number Formatting Behavior
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 create a template containing various worksheets with the thousands separator formatting applied to every cell, right-click the tab of the worksheet you just formatted, select “Move Or Copy,” check “Create A Copy,” and click “OK.” Then, repeat this process until you have as many formatted worksheets as you need.
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.
Remember to adjust the number formatting if you input another data type—such as a date or text—into another cell.
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.
Cell style changes apply to all worksheets within the active workbook.
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.
Changing the Character Used for the Thousands Separator in Excel
By default, the character used for the thousands separator in Excel is set according to the regional settings of your operating system. While most English-speaking countries use a comma, others use a period or a space. As a result, you might need to change the thousands separator used in a workbook if you plan to share the file with people from other countries.
To do this, launch the Excel Options dialog box by clicking File > Options, or using the keyboard shortcut Alt > F > T. Then, open the “Advanced” tab.
Next, in the Editing Options area, uncheck “Use System Separators,” and replace the default character in the Thousands Separator field with one of your choice. Then, click “OK.”
Now, when you apply the thousands separator to a value in a cell using one of the methods above, the character you chose will be displayed.
Changes to the default thousands separator character in Excel Options apply to all previous Excel workbooks when reopened, as well as all future workbooks.
Using the thousands separator isn’t the only way to format your Excel worksheet to make it clear and readable. For example, adjusting column widths and row heights, adding custom cell borders, using conditional formatting to highlight important data, or aligning text can all contribute to improving a spreadsheet’s layout.
- OS
-
Windows, MacOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
