How to Add Thousand Separator in Excel

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. Sample data set

 

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.Final output after adding thousand separator

 

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.Going to the Number Format window

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.Add Thousand Separator in Excel

3. And that’s it for the first method! That’s the easiest way to add a thousand separator in Excel.Add 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.Add Thousand Separator in Excel

5. Lastly, drag down to apply the formula to the rest of the rows to apply the thousand separator.Add Thousand Separator in Excel

7. And tada! We have successfully added a thousand separator in Excel to our data set.Add Thousand Separator in Excel

 

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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.


0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like