The best way to Use the Hundreds Separator in Microsoft Excel

The hundreds separator is a personality used to separate teams of three digits in bigger numbers, such because the commas in 1,000,000. In Microsoft Excel, you possibly can format numbers to incorporate the hundreds separator. On this information, I will present you ways.

The principle cause utilizing the hundreds separator is the popular choice is that it makes numbers simpler to learn, because it breaks lengthy strings of digits into smaller teams. What’s extra, the hundreds separator reduces the prospect of errors once you’re typing prolonged numbers manually, because it’s simpler to confirm that you have typed the right variety of digits.

Typing the Hundreds Separator Manually in Excel

When you’re getting into knowledge into clean cells, you possibly can insert the hundreds separator by typing a comma manually.

After urgent Enter, you possibly can confirm that Excel acknowledges your enter as a numerical worth by choosing the cell and glancing on the Quantity group of the Residence tab on the ribbon.

Additionally, when you press Ctrl+1 to launch the Format Cells dialog field, you possibly can see that the Quantity format is utilized, and the hundreds separator checkbox is checked.

Now, you possibly can copy this formatting to different cells, reasonably than typing a comma every time you enter a price. First, choose the cell with the hundreds separator, and click on the “Format Painter” icon within the Residence tab on the ribbon.

Then, choose all the opposite cells whose values you need to include the hundreds separator.

The hundreds separator is now utilized persistently.

Including the Hundreds Separator in Excel Utilizing a Keyboard Shortcut

Keyboard shortcuts are a good way to work more efficiently in Excel, and by chance, there’s one for the hundreds separator.

First, choose the cells containing or that can include the numbers you need to format with the hundreds separator utilizing Shift with the Arrow keys (or Ctrl+Shift+Arrows to pick out ranges).

Then, press Ctrl+Shift+1 so as to add the hundreds separator.

Discover how this methodology additionally provides two decimal places to the values by default. To take away these, with the cells nonetheless chosen, click on the “Lower Decimal” icon within the Quantity group of the Residence tab on the ribbon.

Including the Hundreds Separator in Excel through the Format Cells Dialog Field

One other method so as to add the hundreds separator to numerical values is to change the quantity format within the Format Cells dialog field.

First, choose all of the cells to which you need to apply this modification, and click on the Format Cells dialog field launcher icon within the backside nook of the Quantity group within the Residence tab on the ribbon. Alternatively, with the cells chosen, press Ctrl+1.

Then, click on “Quantity” within the Class menu, and verify the “Use 1,000 Separator” checkbox. You would possibly discover that checking this field provides two decimal locations to the values within the chosen cells. To take away these, sort 0 into the “Decimal Locations” area, or click on the decrease scroll arrow twice.

Lastly, double-check the pattern within the panel above to verify the values seem as anticipated.

While you click on “OK,” the numbers within the chosen cells could have adopted the hundreds separator. I’ve opted for 2 decimal locations within the screenshot under.

Including the Hundreds Separator in Excel Utilizing the Comma Icon

The hundreds separator is so helpful that Microsoft determined so as to add a devoted button within the Quantity group of the Residence tab on the ribbon.

Particularly, choosing a cell and clicking this icon applies the comma model. So, how the cell behaves is dependent upon the style’s settings. In my case, once I apply the comma model to a cell, the quantity format adjustments to Accounting, and two decimal locations are added.

Nevertheless, I would like the cell formatted as a quantity with no decimal locations. If you end up in the identical scenario, develop the “Cell Kinds” drop-down menu within the Kinds group of the Residence tab on the ribbon, right-click the “Comma” model, and click on “Modify.”

Then, within the Type dialog field, click on “Format.”

Subsequent, within the Quantity tab of the Format Cells dialog field, click on “Quantity,” make sure the hundreds separator checkbox is checked, evaluate and alter (if essential) the variety of decimal locations, and click on “OK” twice to shut each dialog bins.

Now, each time you choose a cell and click on the “Comma” icon, the newly outlined formatting—together with the hundreds separator—will probably be utilized.

Making the Hundreds Separator the Default Excel Quantity Formatting Conduct

Sadly, there is not a button or checkbox to mechanically add the hundreds separator to all numerical values by default in Excel. Nevertheless, you can pace up the hundreds separator formatting course of by creating and using a custom Excel template.

On the one hand, you possibly can create a template the place all of the cells within the worksheet are formatted as a quantity with a hundreds separator, primarily altering the default quantity format from Basic to Quantity. Then again, you possibly can create a template with a modified comma model, that means you possibly can click on the “Comma” icon on the ribbon to use the specified formatting shortly and at any time.

Template 1: Formatting All Cells as a Quantity

To create a template the place all of the cells within the worksheet are formatted as a quantity with a hundreds separator, open a brand new, clean workbook, and press Ctrl+A to pick out all of the cells. Then, press Ctrl+1 to open the Format Cells dialog field, or click on the icon within the backside nook of the Quantity group within the Residence tab on the ribbon.

Subsequent, click on “Quantity” within the Class checklist, and verify “Use 1,000 Separator” in the principle part. Additionally, resolve whether or not you need numerical values to have decimal locations. If not, change the quantity to zero. Then, click on “OK.”

Now, the hundreds separator formatting is utilized to each cell within the worksheet.

To avoid wasting this workbook as a template, click on File > Save As (or press F12), and choose “Excel Template” within the Save As Kind area. Give the template a reputation within the File Title area, and click on “Save.”

The file is now saved in your gadget’s default Workplace template location, so you possibly can shut the file fully.

Now, reopen Excel, and click on “New” within the left-hand menu. Then, find and double-click the template you simply saved (you would possibly have to click on “Private” to seek out it).

This opens a brand new Excel workbook, that means any adjustments you make will not have an effect on the template. While you sort a quantity with 4 or extra digits and press Enter, the hundreds separator is utilized immediately.

Template 2: Modifying the Comma Type

To create a template with a modified comma model, first, open a brand new, clean Excel workbook. Then, click on the “Cell Kinds” down arrow within the Kinds group of the Residence tab on the ribbon, navigate to and right-click the “Comma” model, and select “Modify.”

Subsequent, within the Type dialog field, click on “Format.”

Now, click on “Quantity” within the left-hand Class menu, and guarantee “Use 1,000 Separator” is checked. When you’re there, state the variety of decimal locations you need numbers on this model to have. Then, click on “OK” twice to shut each dialog bins.

Now, to save lots of this workbook with the modified comma model as a template, click on File > Save As (or press F12), and choose “Excel Template” within the Save As Kind area. Title the template within the File Title area, and click on “OK.”

Shut the template you simply created earlier than reopening Excel and clicking “New” within the left-hand menu. Then, find and double-click the template you simply saved.

This opens a brand new Excel workbook, leaving the template unaffected. Since you’ve created the workbook from the template with the modified comma model, when you choose a cell and click on the “Comma” icon, the worth within the cell will undertake the hundreds separator.

Altering the Character Used for the Hundreds Separator in Excel

By default, the character used for the hundreds separator in Excel is about based on the regional settings of your working system. Whereas most English-speaking nations use a comma, others use a interval or an area. In consequence, you would possibly want to vary the hundreds separator utilized in a workbook when you plan to share the file with individuals from different nations.

To do that, launch the Excel Choices dialog field by clicking File > Choices, or utilizing the keyboard shortcut Alt > F > T. Then, open the “Superior” tab.

Subsequent, within the Enhancing Choices space, uncheck “Use System Separators,” and change the default character within the Hundreds Separator area with considered one of your alternative. Then, click on “OK.”

Now, once you apply the hundreds separator to a price in a cell utilizing one of many strategies above, the character you selected will probably be displayed.

Utilizing the hundreds separator is not the one method to format your Excel worksheet to make it clear and readable. For instance, adjusting column widths and row heights, including custom cell borders, utilizing conditional formatting to spotlight necessary knowledge, or aligning textual content can all contribute to enhancing a spreadsheet’s format.

OS

Home windows, MacOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 contains entry to Workplace apps like Phrase, Excel, and PowerPoint on as much as 5 units, 1 TB of OneDrive storage, and extra.