What Is Comma Style Formatting In Excel?
The Comma Style in Excel is a numeric value formatting feature that helps add Commas as a 1000s separator. And the style works with numbers, such as decimals and negative values, and number formats, such as Currency and Accounting.
Users can use the Excel Comma Style option when working with financial and accounting figures to make them more understandable and interpretable.
For example, the table below shows the trial results of an experiment.
However, reading the column D values can be challenging as they are large.
To overcome the issue, we can choose the required data and format with Comma Style in Excel from the Home tab.
In the above example, we select the cell range D4:D8 and click the Comma Style option in the Home tab. The above action will insert the default 1000s separator, the Comma symbol, in each value in the chosen range.
And once we format with Comma Style in Excel, we can select the numbers and set the required decimal places to suit our requirements. And for that, we can use the Increase Decimal and Decrease Decimal options in the Home tab.
Table of contents
Key Takeaways
- The Comma Style in Excel is an option that enables one to insert Commas as a 1000s separator, with the default two decimal places.
- Users can use the Excel Comma Style option with positive and negative numbers and number formats, such as Accounting and Currency, to make them more readable.
- We can use the Comma Style option and Accounting number format in the Home tab to set the required comma style in the chosen cell or range. However, the Accounting number format also inserts the default currency symbol, ‘$’, before the numbers.
- After applying the Comma Style option in the chosen cell or range, we can use the Format Cells window to set the required Currency and decimal places settings. Also, we can use the Custom tab in the Format Cells window to set customized comma styles in the selected cell or range.
How To Apply Comma Style In Excel (Accounting Format)?
The steps to apply and change Comma Style in Excel (Accounting number format in Excel) are as follows:
- Choose the cell range containing values for which we must insert Commas using the Comma Style option.
- Choose the Home tab → click the Comma Style option to add Commas as a 1000s separator in each value in the chosen cell range.
And once we click the Comma Style option, the Number Format option in the Home tab will show the chosen data format as Accounting instead of General.
[ Alternatively, we can choose the cell range containing values for which we must insert Commas. And then, select the Home tab → click the Number Format drop-down → select Accounting.
The numbers in the chosen cell range will show with the default currency symbol, ‘$’, in the front, with Commas as a 1000s separator and default decimal places.
The alternative method is useful when the data values are accounting figures.]
[ Otherwise, we can choose the required cell range and select the Home tab → click the Cell Styles option.
And then, click Comma, Comma [0], Currency, or Currency [0].
The first two options will show the numbers in the chosen range with the Comma as a 1000s separator, with and without two decimal places. And the last two options will show the numbers in the chosen range with the ‘$’ symbol, Comma as a 1000s separator, with and without two decimal places.]
Furthermore, if we want to change the decimal places, we can use the Increase Decimal and Decrease Decimal options next to the Comma Style option in the Home tab.
But, if we wish to change the decimal places and the currency symbol settings, we can select Home → Number Format → More Number Formats.
The above action will open the Excel Format Cells window.
Otherwise, we can select the required cell range and right-click to choose Format Cells from the contextual menu to open the Format Cells window.
Next, as the selected cell number format will be Accounting, the Accounting category will open in the Number tab in the Format Cells window.
Use the Decimal places and Symbol options to set the required settings in the Number tab in the Format Cells window.
Finally, clicking OK will close the Format Cells window, and we can view the chosen numbers with Commas and in the required format.
Further, if we must change comma style in Excel. Then the steps are as follows:
- Select File in the Excel ribbon → Choose Options to open the Excel Options window.
- Select Advanced from the left menu to open the Advanced tab in the Excel Options window.
- Unselect the Use system separators box to enable the Thousands separator option.
- Click inside the Thousands separator option box to change comma style in Excel.
For example, we shall change the separator from Comma to Semi-colon.
Finally, we can click OK.
And now, when we use the Comma Style option or set the Number Format option as Accounting, we will see the Semi-colon as the 1000s separator.
Basic Example
The table below contains a firm’s invoice details.
And column G contains the invoices’ amount data. But the values can be difficult to interpret, as they are large figures without Commas.
So, here is how we can make them readable.
- Step 1: Select cells G3:G7 and choose Home à Comma Style.
The chosen values will appear with the Comma as the thousands separator, and the Number Format in the Home tab will show the selected cell range data format as Accounting.
Alternatively, we can choose cells G3:G7 and select Home → Number Format → Accounting.
The chosen data will appear with the default currency symbol, ‘$’, Comma as a 1000s separator, and default decimal place settings.
[ Alternatively, select cell range G3:G7 and choose Home → Cell Styles.
And then, choose Comma, Comma [0], Currency, or Currency [0] in the Number Format section to suit our requirement.
The Comma and Currency options show Commas as a 1000s separator and two decimal places, but without and with the ‘$’ symbol, respectively. On the other hand, the Comma [0] and Currency [0] options give the same results as Comma and Currency, except the results do not show two decimal places.]
Next, consider the requirement to remove the currency symbol, ‘$’, and the two decimal places in the chosen data. Then, the following steps will help us achieve the required outcome.
- Step 2: Choose the required data and select Home → Number Format → More Number Formats to access the Format Cells window.
Otherwise, we can right-click the chosen cell range and choose the Format Cells option from the contextual menu.
- Step 3: Set the Decimal places to 0.
And then, click the Symbol option drop-down button and choose None.
Finally, clicking OK will result in the following output.
Furthermore, consider a value in a cell to be zero or negative. Then, the Comma Style option shows zero as ‘–‘ (Dash) and the negative value in a bracket with the Commas inserted as the 1000s separator.
For example, if cells G3 and G4 in the source dataset contain 0 and a negative number -1520000.
Then, selecting cells G3:G7 and choosing Home → Comma Style will result in the following output.
Excel Shortcuts To Use Comma Style Format
The shortcuts to use comma style format are as follows:
- Select the required cell range and press Alt.
Next, press H to open the Home tab and K to choose the Comma Style option.
Also, we can use Alt + H + 0 and Alt + H + 9 to increase and decimals to set the required decimal places setting.
- Select the required cell range and press Alt + H + N to access the Number Format option drop-down list.
And then press A to set the Number Format as Accounting.
- Press the key combination Ctrl + 1 to access the Format Cells window. And then, click inside the Category field in the Number tab and press A to choose the Accounting category to set the required decimal places and currency symbol settings.
- Press Alt + H + J to choose the Cell Styles option. And then, choose the required option from the Number Format section according to our comma style requirements.
Advantages
The advantages of the option Comma Style in Excel are as follows:
- The option improves the readability of large numbers. As a 1000s separator, the Comma groups digits in a number into sets of three. So, it helps differentiate between different digits in the number and count them more precisely.
- The option helps highlight significant digits in a number, which is quite resourceful while working on financial reports.
- As the option makes large numbers more readable, it ensures we analyze the data for trends with enhanced accuracy.
- The option enables us to apply consistent formatting across the worksheet, which avoids confusion while dealing with massive datasets containing large figures.
Disadvantages
The advantages of the option Comma Style in Excel are as follows:
- The option always inserts the Comma as a separator for 1000s.
- When we use the Comma Style option or apply the Accounting number format in a cell or range, the resulting data in the chosen cell or range will appear right-aligned.
Important Things To Note
- The Comma Style in Excel option will work for valid numbers, unprotected sheets, and the correctly updated Thousands separator option in the Excel Options window.
- The Comma Style option inserts Commas only as a 1000s separator by default.
- The Comma Style option shows zero as a Dash and negative numbers in brackets.
- Applying the Comma Style option or setting the Number Format as Accounting in the chosen cell or range will show the result with the right alignment.
Frequently Asked Questions (FAQs)
We can change Comma style in Excel from Lakhs to Million using the Digit grouping option in the Region settings option in the Control Panel.
Let us see the steps with an illustration.
The table below shows a firm’s monthly sales figures.
Assume we selected the cell range E3:E14 and chose Home → Comma Style to insert Commas in the chosen numbers as a 1000s separator.
But the Comma style, in this case, is Indian. And hence, the numbers display in Lakhs.
So, the steps to change the Comma style from Lakhs to Million is as follows:
• Step 1: Enter the phrase Control Panel in the Search field next to the Microsoft Windows icon in the computer’s taskbar.
Choose the Control Panel option in the search results to open the Control Panel window.
• Step 2: Choose the Clock and Region option.
• Step 3: The Clock and Region window opens, where we must click the Region option.
The Region window will open, where we must click the Additional settings button in the Formats tab.
The Customize Format window will open, where we must click the Currency tab to open it.
• Step 4: Select the Million option in the Digit grouping field, as shown below.
• Step 5: Click Apply and OK in the Customize Format window.
Clicking Apply will show the updated Million Comma style in the Example field.
• Step 6: Click Apply and OK in the Region window.
So now, if we open the active worksheet, we will see the Comma style changed from Lakhs to Million.
[ Alternatively, we can right-click the chosen cell range containing the numbers with the Commas in the Indian style (the numbers show in Lakhs). And choose the Format Cells option from the contextual menu.
Next, choose the Custom category in the Number tab in the Format Cells window.
And then, select the following custom number format if available in the existing custom format list. Otherwise, please enter it in the Type field to insert the Commas and show numbers in Million instead of Lakhs.
#,##0.00
Finally, clicking OK will show the numbers in Million instead of Lakhs.]
You can do a Comma in Excel Indian style using the following steps:
1) Select the cell or range containing the numbers where you must insert Commas in the Indian style.
2) Right-click the chosen cell or range to choose Format Cells from the contextual menu.
3) The Number tab in the Format Cells window will open, where you must select the Custom category in the Category section.
4) Enter the following custom number format in the Type field.
[>=10000000]”₹ “##\,##\,##\,##0;[>=100000]”₹ “##\,##\,##0;”₹ “##,##0
Or
[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0
5) Click OK in the Format Cells window to add the abovementioned custom number format to the custom number format list in the Custom category. And the data in the chosen cells will appear with Commas in the Indian style.
While both number formats insert Commas to the chosen numbers in the Indian style, the first format also inserts the Indian currency symbol. So, you can use it for accounting and financial figures.
On the other hand, the second format does not insert the Indian currency symbol, and it applies to regular data.
Excel Comma Style is not working, perhaps due to the following reasons:
• The worksheet or workbook is password-protected.
• The entered numeric data is invalid.
• The Thousands separator under Use system separators in the Advanced tab in the Excel Options tab is set incorrectly.
Download Template
This article must be helpful to understand the Comma Style In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Comma Style In Excel. We learn how to apply it in accounting format, shortcuts, with an example & points to remember. You can learn more from the following articles –
Leave a Reply