7 Easy Ways to Use Comma in Excel Formula

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.

how to Use Commas in Excel Formula


📁 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.

Dataset to use commas in excel

method

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.

Selecting 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.

Using the 1000 separator

  • Finally, we get the result in column E.

Cell values with comma

📕 Read More: 2 Easy Ways to Add Thousand Separator in Excel Formula

method

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.

Using the Accounting format

  • Finally, we get our result in column E.

Cells with comma📕 Read More: 6 Easy Ways to Put Comma After 5 Digits in Excel

method

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.

Using the Comma Style

  • Finally, we get the return values with commas in column E.

Cells with comma

📕 Read More: 10 Ways to Put a Comma After 3 Digits in Excel

method

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.

Using the keyboard shortcut

  • We again find the shortcut keys on display.
  • Now we press K for selecting the Comma Style.

Selecting the Comma Style

  • Finally, we get the updated result in column E.

Cell values with commas.

📕 Read More: 9 Tricks to Put Comma After 2 Digits in Excel

method

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.

Using the FIXED function

  • After that, we use the Fill Handle tool to drag the formula.

Using the Fill Handle tool

  • Finally, we get the result in column F.

Cell values with comma

method

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.

Using the TEXT function

  • After that, we use the Fill Handle tool to drag the formula.
  • Finally, we get our result in column F.

Cell value with comma

method

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.

Using the DOLLAR function

  • We get our result in column F.

Cell values with a comma


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.

Using the SUBSTITUTE function

  • Finally, we get our result in column D.

A comma between texts.


📄 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

(Visited 66 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo