How to Add Thousand Separator in Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

In numbers with four digits or more (i.e., numerals over 999), commas can be used after every third digit from the right. Long numbers are simpler to read thanks to these “thousands of separators,” as we can quickly determine where the various groups of digits fall. In this article, we will show you how to add a thousand separator in an Excel formula.


How to Add Thousand Separator in Excel Formula: Easy Steps

Numerous predetermined formats are included in the flexible feature of number formatting. In this article, we will show you how to add a thousand separator in an Excel formula by applying the TEXT function with the 5 given steps below. Let’s say we have a sample data set to show you how to add a thousand Separator using step by step procedures.

Sample Data


1. Creating Data Set

  • Firstly, we will add here a new column for adding a thousand separator in an Excel formula.

Sample Data


2. Applying TEXT Function

  • Firstly, type the Equal (=) sign in the E5 cell.
  • Secondly, apply the TEXT function which has two arguments.
  • Now, start with the value argument where you will format the value you want.
  • Then, for the format_text argument, you will apply your desired format.

The general syntax for the TEXT function is given below.

=TEXT(value,format_text)

Sample Data


3. Inserting Value Argument

  • Here, to insert the value argument, click on the E5 cell first.

Sample Data


4. Inserting Format_text Argument

  • Now, in this section, you will insert the format_text argument.
  • E5 cell represents the value, and “#,####” displays thousand separator with no decimal places.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula


5. Final Results

After completing the above steps properly, you will have the option of adding a thousands separator in your Excel formula with or without decimal value. We will discuss these two sub steps here.

5.1 Thousand Separator Without Decimal Value

  • Firstly, click on the first cell F5 in the column where you wish the thousands separator to display.
  •  Here, “#,####” displays a thousand separator with no decimal places.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

  • Here, you will observe the outcomes of cell F5 by adding a thousand separator in the Excel formula.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

  • Now, use the Fill handle tool and drag it down from cell F5 to F10 to see the results of all the cells by adding a thousand separator.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

5.2 Thousand Separator with Decimal Value

  • Firstly, select the first F5 cell in the column where you want the thousands separator to appear by clicking there.
  • ” #,###.00″ displays a thousand separator with two decimal places.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

  • Now, you can see the results of adding a thousand separator in the Excel formula in cell F5 here.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

  • Finally, use the Fill handle tool and drag it down from cell F5 to F10 to see the results of all the cells by adding a thousand separator in the Excel formula.

Step-by-Step Procedures to Add Thousand Separator in Excel Formula

Read More: How to Apply Excel Number Format in Thousands with Comma


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, I’ve covered step by step process of how to add a Thousand Separator in an Excel formula. I sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


<< Go Back to How to Add Comma in Excel | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo