Select Page

Thousand Separator in Excel

Thousand Separator in Excel

Sometimes we come accross a situation, where we want to change thousand seperator for a particular excel file. In this post, I shall discuss all about thousnd selerator in excel and how to configure it permanetly as well as for a single situation.

Introduction to Thousand Separator

When you enter 12345678 in Excel, it will be very difficult to spell out the number. Compare this to number presented with comma as 12,34,567. You can easily see the ease in readability.

A Thousand separator is used to display comma (or for that matter any other symbol) when displaying a number in excel.

Configure Thousand Separator  Setting

To modify settings go to Control Panel in Windows 7> then Region and Language> then on Formats Tab> Additional Settings. You will see the screen below.

Let us understand how these setting will affect the display of comma in Excel. We are only concerned with 2 settings on this dialogue box. > Digit grouping symbol and Digit grouping

Digit grouping symbol

As the name suggest lets you change the grouping separator. The standard setting for this is a comma.

Digit grouping

This allows you to specify how the numbers are to be grouped.

In digit grouping we have 4 options

  1. No Grouping – No grouing is specified. In windows numbers are not grouped.
  2. 3 Digit Grouping – Thousand seperator is added after 3 digits.
  3. One 3 Digit Grouping – Only first 3 digits (i.e after thousand seperator is added).
  4. 2 Digit – Thousand seperator is added after 3 digits.

Override Thousnad Separator Setting

Sometimes we come across a situation, where we want to change thousand separator for a particular excel file. Say for example, in your day to day work, you need 2 digit separator and one particular Excel file, you need to use 3 digit separator, then you will need to use a custom format.

[>=10000000]###\,###\,##0;[>=1000000]##\,###\,##0;##,##0

About The Author

CA, ISA, CISA, BCAF. Friends call me Techno Savvy Chartered Accountant. I work at EY in System Audit

1 Comment

  1. In case the Custom Format code gets mixed up, due to formatting, here is the code

    [>=10000000]###\,###\,##0;[>=1000000]##\,###\,##0;##,##0

Archives

Subscribe To my Newsletter

Subscribe To my Newsletter

Join the mailing list to receive the latest news and updates from the blog

You have Successfully Subscribed!