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
- No Grouping – No grouing is specified. In windows numbers are not grouped.
- 3 Digit Grouping – Thousand seperator is added after 3 digits.
- One 3 Digit Grouping – Only first 3 digits (i.e after thousand seperator is added).
- 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
In case the Custom Format code gets mixed up, due to formatting, here is the code