To show thousands separators in Excel without changing the underlying numbers, select the cells and apply the custom number format #,##0. For two decimal places, use #,##0.00. In Microsoft 365 for Windows, you can also select the cells and choose Home > Number > Comma Style.
What the comma does in an Excel number format
A number format controls how a numeric value appears in a cell; it does not normally change the value stored there. The comma in #,##0 tells Excel to group digits in thousands. For example, a cell containing 1234567 appears as 1,234,567, while its stored value remains 1234567. A formula using that cell still works with the original number.
The symbols around the comma matter too. The # character displays a digit when one is present but does not force a zero into an otherwise empty position. The 0 character requires a digit, so #,##0 displays zero as 0 and rounds values to whole numbers for display. Rounding in the display does not round the stored value.
Excel uses your regional settings to determine which character appears as the grouping separator and which appears as the decimal separator. In many English-language settings, a comma separates thousands and a period marks decimals. In other regions, Excel may display a period for grouping and a comma for decimals, even when the format code uses the same underlying pattern.
Choose a format for the numbers you have
The right format depends on whether decimal places matter and how much precision readers need to see. These common custom formats cover many everyday uses:
#,##0displays whole numbers with grouping, such as 25,000. It rounds the displayed value to the nearest whole number.#,##0.00displays exactly two decimal places, such as 25,000.50. It also shows trailing zeros.#,##0.##allows up to two decimal places but omits unnecessary trailing zeros: 25,000.5 stays 25,000.5, while 25,000 displays without decimals.0,000forces at least four digits, so 25 appears as 0,025. This is useful only when fixed-width presentation is intended.
For measurements or prices, choose decimal places that reflect the precision readers need. A value such as 8.746 displayed with #,##0.00 appears as 8.75, but the full value remains available to calculations. If the extra precision is important, show more decimal places rather than relying on the displayed rounding.
Apply the comma format from the ribbon
For a quick, standard format in Excel for Microsoft 365 on Windows, select the cells and use Home > Number > Comma Style. This applies a grouped number format, typically with two decimal places. Use the Increase Decimal or Decrease Decimal buttons in the same Number group if you want to adjust the displayed precision.
For a custom format, follow these steps:
- Select the cells, range, column, or table values to format.
- On Windows, press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, open the Number group’s dialog launcher. In Excel for Mac, use Format > Cells and open the Number tab.
- Choose Custom in the Category list.
- Enter a format such as
#,##0or#,##0.00in the Type box, then select OK.
To format an entire column, select its column heading before applying the format. If the column contains headers or text, those entries remain text; the number format affects numeric values. You can also copy the formatting to other cells with Format Painter, but check that the destination cells contain numbers if you expect the grouping to appear.
Comma Style compared with a custom format
Comma Style is convenient when its default appearance suits the worksheet. It applies grouping with a standard decimal display, and the ribbon buttons make it easy to change decimal places. It is a good choice for a fast, familiar presentation, especially in a report where several ranges should use the same general style.
A custom format gives you more control. You can decide whether to show decimals, force zeros, display negatives differently, or use a suffix. It is also useful when you need a consistent display such as whole numbers with no decimal digits. Neither method changes a numeric cell into text, and both can be changed later by applying another number format.
Choosing Home > Number > Number and enabling the thousands separator in Format Cells is another route to a grouped display. The Number category is useful when you want to specify decimal places through familiar controls rather than entering a format code. For a repeatable presentation across a workbook, however, a custom format makes the desired pattern explicit.
Display grouping is not the same as changing a value
Applying #,##0 does not insert commas into the cell’s stored value. The cell remains numeric, so it can be added, sorted numerically, used in charts, and referenced by formulas. You can verify this by selecting a formatted cell and looking at the formula bar: it shows the underlying value, not the formatted display.
This distinction matters when exporting or copying data. A numeric value may be exported without the visual formatting, depending on the file type and application. If you need commas to become literal characters in a text string, that is a different task from formatting a number. Converting values to text can affect sorting and calculations, so keep data numeric unless a text output is specifically required.
It also explains why typing a comma into a cell does not always behave as expected. Excel interprets typed entries according to regional number settings and may recognize a comma as a decimal mark, a grouping mark, or a list separator in other contexts. For data entry, type the value in the format Excel expects, then apply number formatting to control its appearance.
Regional settings and separator differences
The format code may contain a comma while the worksheet visibly uses another grouping symbol. Excel follows the applicable regional settings for number separators; those settings can differ between computers or workbooks opened in different environments. As a result, a workbook may show 1,234 on one setup and 1.234 on another while representing the same numeric value.
If a result looks wrong, first check the cell’s actual value and the system’s decimal and thousands separators. In Excel for Windows, the separator options are under File > Options > Advanced, in the Editing options area. Excel can use system separators or custom separators there. Changing these options affects how numbers are interpreted and displayed, so avoid changing them casually in a shared-workbook workflow.
Excel for the web and Excel for Mac can expose settings differently, and the displayed separators may depend on regional or application settings. If a workbook is used by people in multiple locales, agree on a display convention and test the file in the environments that matter. A format code alone cannot guarantee that every locale will show the same punctuation character.
Show large figures in thousands or millions
A comma placed at the end of a custom number format has a different purpose from a comma between digit placeholders. A trailing comma scales the displayed number by 1,000 for each comma, while leaving the stored value unchanged. For example, a format such as #,##0, displays 1,234,567 as 1,235, because the display is in thousands and rounded to a whole displayed unit.
To label the scale, you can include quoted text in the custom format. For example, #,##0.0, "K" displays 1,234,567 as 1,234.6 K. Two trailing scaling commas show millions; a format such as #,##0.0,, "M" displays the same value as 1.2 M. These formats are useful for compact charts and dashboards, but they can hide detail. Label the scale clearly so readers do not mistake the displayed number for the full value.
Scaling commas do not convert the underlying value or alter calculations. If a reader copies the cell’s numeric value into another application, the original full number may be used rather than the shortened display. Use a separate note, chart axis label, or column heading to explain whether figures are shown in thousands or millions.
Common problems and how to fix them
- No comma appears: Values below 1,000 do not need a grouping separator. Check that the selected cells are numeric and that the format was applied to the intended range.
- The value appears rounded: The format may hide decimals. Increase the displayed decimal places or use a code such as
#,##0.00. The underlying value is usually still unchanged. - The comma is a period, or vice versa: Check regional separator settings. The visible punctuation depends on locale, not just on the characters entered in the format code.
- The format has no effect: The cell may contain text that looks like a number. Number formats do not turn text into numeric values. Convert the data to numbers using a suitable method, then apply the format.
- Long numbers show as hash marks: The column may be too narrow to display the formatted value. Widen the column or use Home > Format > AutoFit Column Width.
When troubleshooting, select one affected cell and inspect its formula bar and Number Format settings before changing an entire column. This helps distinguish a display issue from text data, an unexpected value, or a narrow column.
Frequently Asked Questions
What is the Excel format code for thousands with commas?
Use #,##0 for grouped whole numbers or #,##0.00 for two decimal places. The punctuation displayed can vary with regional settings.
How do I add a comma to every thousand in Excel?
Select the numeric cells and choose Home > Number > Comma Style, or apply a custom format such as #,##0 through Format Cells > Custom.
Does adding a comma change the number in the cell?
No. A number format changes the display, not the stored numeric value. Formulas continue to use the underlying value.
Why does my Excel comma format show a period?
Excel follows regional separator settings. A period may be the thousands separator in your locale, while a comma is used for decimals.
How can I show 1,000 as 1K?
Use a custom format with a trailing scaling comma and quoted text, such as #,##0,"K". The displayed number is scaled, but the stored value is still 1,000.
Related
- Excel Number Format to Millions: Steps and Examples
- Excel Highlight Active Cell Row and Column Automatically
- Excel: How to Calculate Workdays Between Two Dates
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets ARRAYFORMULA: How It Works in PracticeIn Google Sheets, ARRAYFORMULA applies a formula to a range of cells so one formula can return results…
- How to Add a Heading in WordTo add a heading in Microsoft Word, put the heading text in its own paragraph, select or click…
- Company Invoice Template in Excel: Setup and TipsYes. You can make a reusable company invoice template in Excel by laying out your business and customer…
- Find and Replace in Word: Open and Use the Dialog BoxIn Microsoft Word for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace; in current versions,…
- Google Gradebook Spreadsheet: Build One in Google SheetsA Google gradebook spreadsheet is a grade-tracking table you create in Google Sheets; it is not a separate…
- Auto Loan Amortization Schedule in Google SheetsYes—Google Sheets can create an auto loan amortization schedule that shows each monthly payment’s interest, principal, and remaining…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.