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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Step-by-Step Procedures to Add Thousand Separator in Excel Formula
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.
1. Creating Data Set
- Firstly, we will add here a new column for adding a thousand separator in an Excel formula.
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)
3. Inserting Value Argument
- Here, to insert the value argument, click on the E5 cell first.
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.
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.
- Here, you will observe the outcomes of cell F5 by adding a thousand separator in the 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.
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.
- Now, you can see the results of adding a thousand separator in the Excel formula in cell F5 here.
- 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.
Read More: How to Put Comma in Numbers in Excel (7 Easy Ways)
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. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.