Md. Ahaduzzaman
In our daily life sometimes we require to deal with a very large dataset where the number entry can also be very large. If they are too large sometimes it is very difficult for us to read and understand them. So we need to put commas in them for better understanding. In this article, we are going to discuss briefly seven different ways to use the comma in the excel formula. In the first 4 methods, we will use features such as Format Cells, Accounting Number Format, Comma Style & Keyboard Shortcut. In the final 3 methods, we will take the advantage of Excel functions like FIXED, TEXT, DOLLAR, etc. You can find the overview snapshot of what we are trying to achieve below.
📁 Download Excel File
Download the practice file from here.
Learn to Use Comma in Excel Formula with These 3 Methods
In our following section, we will discuss the seven ways to use the comma in the excel formula. Let’s assume we have the dataset of the product order sheet of a company. We have a total cost column where the cost value is without any commas so it is difficult for us to understand it properly. So we will put commas in the values to comprehend it better. The sample dataset for our demonstration purpose is as follows.
1. Applying Format Cells Option to Add Comma in Numbers
In this section, we will use the Format Cells option to use the comma in the excel formula. This method is beneficial when we want to possess total control over how many decimal places we want to keep on display. If our data contains negative values we can also determine how we want to see them. So the steps for this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select our data range.
- After that, we go to the Format Cells option.
- Following that, we go to the Number category.
- After that, we select the Use 1000 Separator option.
- Then we press OK.
- Finally, we get the result in column E.
📕 Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula
2. Applying Accounting Format to Add Comma
Here we will apply the Accounting Format of the number to use the comma in the excel formula. We can use this format if our dataset contains currency-type data because it puts a “$” symbol before the data. If our dataset contains another type of data like scientific value or date then we should avoid it. The steps for this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select the data range.
- After that, we go to the Number Format dropdown bar.
- Following that we select the Accounting format.
- Finally, we get our result in column E.
📕 Read More: 6 Easy Ways to Put Comma After 5 Digits in Excel
3. Implying Comma Style to Put Comma After 3 Digits
We will learn how to imply the Comma Style to use the comma in the excel formula in this section. If we only want to add commas without hampering data types or adding any additional symbols this method is the right option for it. The steps to imply Comma Style are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select our data range.
- Then we select the Comma Style as shown below.
- Finally, we get the return values with commas in column E.
📕 Read More: 10 Ways to Put a Comma After 3 Digits in Excel
4. Enforcing Shortcut Key to Use Comma
In this particular step, we will use the Keyboard Shortcut to use the comma in the excel formula. This method comes in handy when we don’t have a mouse or it doesn’t respond. Then we can apply this method to add a comma. So the steps for using this particular method are given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we press the Alt key from the keyboard.
- Now we will see the shortcut keys on the screen. We press the H key.
- We again find the shortcut keys on display.
- Now we press K for selecting the Comma Style.
- Finally, we get the updated result in column E.
📕 Read More: 9 Tricks to Put Comma After 2 Digits in Excel
5. Applying FIXED Function to Put Comma in Numbers in Excel Formula
We will apply the FIXED function in this section to use the comma in the excel formula. Using this function we can decide beforehand whether we want any decimal values or how many decimal values we want to keep. Therefore the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the formula box of cell F6 and type this formula.
=FIXED(E6,0)
- Here E6 is our number and 0 means no decimal value after rounding.
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get the result in column F.
6. Applying TEXT Function to Put Comma
In this method, we will use the TEXT function to use the comma in the excel formula. This function is useful if we want to apply any particular text format to our data. So the breakdown steps of this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the formula box of cell F6.
=TEXT(E6,”#,#”)
- Here E6 is our value and “#,#” means the text format.
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get our result in column F.
7. Implying DOLLAR Function to Add Comma
In our final section here we will imply the DOLLAR function to use the comma in the excel formula. This function is also useful for currency-type data. For datasets other than the currency we better avoid this function. Therefore the steps for using this method are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we put this formula in the formula box of cell F6.
=DOLLAR(E6,0)
- Here E6 denotes our number and 0 indicates no decimal value after rounding.
- We get our result in column F.
How to Add Comma Between Names or After Text in Excel
Here in this section, we will learn how to add the comma between names/after text in Excel using the SUBSTITUTE function. This function gives us the option to substitute any character with another character. So if we want to replace any character with something else we can use this function. So the steps for using this function are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell D6 and write this formula.
=SUBSTITUTE(C6,” “,”,”)
- Here C6 is our name text.
- ” “,”,” means we simply put a comma instead of the space between texts.
- Finally, we get our result in column D.
📄 Important Notes
🖊️ When we use Excel functions we have to be careful about the function arguments.
🖊️ While using the keyboard shortcuts we need to be careful about case sensitivity.
📝 Takeaways from This Article
📌 We can apply the Format Cells option to use the comma in the excel formula.
📌 Accounting Number Format is another option to use the comma in the excel formula.
📌 We can imply the Comma Style to use the comma in the excel formula.
📌 Also we can use the Keyboard Shortcut to use the comma in the excel formula
📌 FIXED function is a way to use the comma in the excel formula.
📌 We can apply the TEXT function to use the comma in the excel formula.
📌 DOLLAR function is another way to use the comma in the excel formula.
Conclusion
In this article, we have learned 7 different methods to use the comma in the excel formula. We can use the features like Format Cells, Accounting Format, Comma Style, Keyboard Shortcut, etc. Also, we can use Excel functions like FIXED, TEXT, DOLLAR, etc to use the comma in excel formulas. Users can either use the function or simply use the features available in Excel. If you have any questions regarding this particular article you can comment in our comment box. If you are interested in further Excel-related problems and solutions then visit our website www.Excelden.com.
Related Articles
- 5 Tricks to Insert Comma Between Words in Excel
- 5 Easy Ways to Add Comma in Excel to Concatenate Cells
