This guide will discuss how to add a thousand separator in Excel using two methods.
When inputting numbers in Excel, there usually would not be any separator between the numbers. Meanwhile, a thousand separator refers to the comma that comes between every three digits, or simply the comma used when numbers reach the thousand place.
So adding a thousand separators in your values in Excel will make it easier to read longer numbers. Surprisingly, it is very easy to add a thousand separator in Excel. But, there is also another method that will make use of the TEXT
function.
Let’s take a sample scenario.
Suppose you are creating a data set containing values that are in the thousands and hundred thousand range. And you realize that seeing all the numbers together makes reading difficult. So you added a thousand separator to all the values to solve that issue.
Now we have mentioned two methods to add a thousand separator. Hence, one is simply formatting the number. While the second is utilizing the TEXT
function to add a thousand separator. Additionally, the TEXT
function will also allow us to choose to display decimal places or not, depending on how we write the format.
Before we dissect a real example of adding a thousand separator, let’s first learn how to write the TEXT
function.
The Anatomy of the TEXT Function
The syntax or the way we write the TEXT
function is as follows:
=TEXT(value;format_text)
Let’s dissect this formula and understand what each term means:
- = the equal sign is how we start any function or equation in Excel.
- TEXT() is our
TEXT
function. This function will convert any value to a text in a specific number format. - value refers to the number we want to format. This can be a number, a formula that evaluates to a number, or a cell reference containing a number.
- Format_text refers to the specific number format we input. This is usually from the Number tab in the Number Format dialog box. In this case, we follow the thousand separator format, which is #,### or #,###.00 with decimal places.
Great! Now we have learned the syntax and parts of the TEXT
function. Let’s dive into a real example of adding a thousand separator in Excel.
A Real Example of Adding Thousand Separator in Excel
Let’s take a sample data set containing numerical values that go up to the thousands or hundred thousand.
The easiest method to add a thousand separator is to change the number format and apply a thousand separator simply.
The second method is using the text function. With the TEXT
function, we can input a numerical format with a thousand separator which is #,###. If we want to include decimals in our values, we simply add decimal places in the format. So the format will become #,###.00.
You can make your own copy of the spreadsheet above using the link attached below.
Awesome! Now let’s learn the steps of adding a thousand separator in Excel.
How to Add Thousand Separator in Excel
This section will tackle the step-by-step process of adding a thousand separator in Excel using two methods.
1. First, we can simply select all the cells containing the numerical values we want to apply a thousand separator. After highlighting them, right-click and select Number Format.
2. Second, go to Number under Category. Then, simply check the box for Use thousand separator. If you want to add decimal places, ensure the Decimal places dropdown menu value is at 2. If you don’t want to show any decimal places, just change the value to 0. Lastly, click OK to apply the changes.
3. And that’s it for the first method! That’s the easiest way to add a thousand separator in Excel.
4. For the second method, select a blank cell beside the values. First, type an equal sign to start the function. Then, input the TEXT
function and select the cell containing the numerical value you want to add a thousand separator. In this case, let’s select A2.
Finally, input the format “#,###” to add a thousand separator or “#,###.00” if you want to show decimal values. So the entire formula would be “=TEXT(A2,”#,###”)”. Press the Enter key to return the result.
5. Lastly, drag down to apply the formula to the rest of the rows to apply the thousand separator.
7. And tada! We have successfully added a thousand separator in Excel to our data set.
That’s pretty much it! Wasn’t it quite easy? Now you have the option to add a thousand separator in Excel to your data set when needed.
Are you interested in learning more about what Excel can do? You can now use the TEXT
function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.