To show Excel values in thousands without changing the stored numbers, apply the custom format #,##0,. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, enter the format in Type, and select OK; a value such as 12,500,000 will display as 12,500.
How the trailing comma works
In an Excel custom number format, commas can have two different jobs. A comma between digit placeholders acts as a thousands separator, as in #,##0, which displays 12500 as 12,500. A comma placed after the final digit placeholder scales the displayed number down by 1,000. That is why #,##0, displays the same stored value, 12500, as 13: Excel rounds the scaled result to a whole number.
The scaling is for display only. Excel does not divide the cell’s underlying value, rewrite the formula, or change the number used in calculations. If A1 contains 12500 and you use #,##0,, the cell looks like 13, but a formula that adds A1 still uses 12500. This distinction is useful for compact reports, but it can confuse someone who assumes the visible number is the actual value.
Each additional trailing comma scales by another factor of 1,000. For example, #,##0,, displays 12,500,000 as 13, while #,##0.0,, displays it as 12.5. Use one trailing comma for thousands and two for millions. The comma used to group digits remains part of the format too, so a displayed value can include separators even after scaling.
Apply the format in Excel for Windows
- Select the cells, range, or entire column you want to format. You can select non-adjacent ranges by holding Ctrl while selecting them.
- Press Ctrl+1 to open Format Cells. Alternatively, use Home > Number group dialog launcher to open the formatting dialog.
- Choose the Number tab, then select Custom in the Category list.
- Click the Type box, enter
#,##0,, and check the Sample preview. - Select OK. The selected cells now show a rounded, whole-thousands display.
To show one decimal place instead, enter #,##0.0,. For example, 12500 displays as 12.5, and 125500 displays as 125.5. The decimal point shown in a custom format is interpreted according to the regional settings used by Excel; in some locales, a comma is the decimal separator and a different character separates groups. If the preview is unexpected, check the number format and your Windows or Excel regional settings.
On Excel for Mac, select the cells and use Command+1 to open Format Cells, then choose Number > Custom and enter the code. Excel for the web has fewer formatting controls in some views and builds; if you cannot find a custom Type box, open the workbook in the desktop app to create the format. The formatting is a cell display setting, not a special formula.
Choose whole numbers, decimals, or a K suffix
Choose the format according to how much precision readers need and what unit the report uses. The format does not add a unit automatically unless you include literal text in the code.
#,##0,shows whole thousands. A value of 1,234,567 appears as 1,235.#,##0.0,shows thousands to one decimal place. The same value appears as 1,234.6.#,##0.00,shows two decimal places. The same value appears as 1,234.57.#,##0,"K"adds a K after the displayed number, so 12500 appears as 13K.$#,##0,displays a dollar sign and whole thousands; 12500 appears as $13.
In the K example, the quoted K is literal text, and the trailing comma still performs the scaling. It does not mean that the stored value has been converted into a new unit. For financial or operational reports, label the column clearly as something like Revenue ($000s) or Units (thousands), especially if you omit the K from each cell. A clear heading helps prevent readers from mistaking a display shortcut for a change in the data.
Rounding is another choice to make deliberately. With zero decimal places, 12,499 displays as 12 and 12,500 displays as 13. Excel rounds the displayed result; it does not discard or round the underlying value. If a small amount of detail matters, use one or two decimal places rather than relying on a whole-thousands display.
Customize negative numbers and zero values
A custom number format can contain up to four sections separated by semicolons: positive numbers, negative numbers, zero values, and text, in that order. The simple format #,##0, applies the same general pattern to numeric values. You can define separate appearances when a report needs them.
For example, #,##0,;[Red](#,##0,);- displays positive values as rounded thousands, negative values in red parentheses, and zero as a dash. The semicolons divide the positive, negative, and zero sections. The dash is literal text in the zero section. This changes only how the cells look: a zero displayed as a dash is still zero for calculations, and a negative amount in parentheses remains a negative number.
Use custom sections carefully. If a format has only one section, Excel generally uses it for all numbers; adding several sections makes the code harder to maintain. Keep the full, unscaled value available in the formula bar or in a separate detail column if colleagues may need to inspect exact amounts. You can edit an existing custom format from Format Cells > Number > Custom by selecting it and changing the Type box.
What changes—and what does not
Custom number formatting is a visual presentation layer. It leaves the number, its precision, and any formulas that refer to it intact. That makes it a good choice when a summary should be compact but calculations must continue to use exact values. It also means the displayed figure may not be suitable for copying into a message or report without its unit label.
Because the number is not divided, formulas should not compensate for the display format by dividing the same values again. If B2 contains 12500 and has the thousands format, a total such as =SUM(B2:B10) still totals the original amounts. Conversely, changing a cell’s appearance to thousands does not make a formula that expects input in thousands automatically correct. Keep the units consistent in formulas and communicate the display scale separately.
Formatting is stored in an Excel workbook, but a plain-text export such as CSV does not preserve Excel’s custom number-format instructions. A CSV recipient may see the underlying numeric values rather than the shortened display. If you need to share the presentation exactly as shown, use an Excel workbook or an appropriate fixed-format export, and check the exported result before distributing it.
Common problems and how to fix them
- The value looks about 1,000 times too small. A trailing comma is scaling the display. Remove that comma if you intended only a thousands separator, or use the column heading to make the scale explicit.
- The number is rounded more than expected. Add a decimal place, for example by changing
#,##0,to#,##0.0,. The stored value is still precise. - Excel shows the code instead of applying it. Confirm that you entered the code under Number > Custom, not as cell content. Also check that the cells are numeric; text that looks like a number will not behave like a numeric value.
- The commas or decimal point look different. Regional settings can affect which characters Excel uses for decimal and grouping separators. Review the Sample preview rather than assuming every computer will display punctuation identically.
- Some cells keep the old appearance. Check whether the format was applied to the full intended range. A pasted format or a different cell style may also have replaced the custom format.
- A negative amount or zero looks wrong. Inspect the semicolon-separated sections. A format with separate negative or zero sections can intentionally use parentheses, color, or a dash instead of the standard appearance.
When troubleshooting, select one affected cell and open Format Cells. The Custom category shows the active format code, while the formula bar helps distinguish a stored number from its shortened display. If the formula bar itself shows text, the problem is likely the cell’s contents rather than the number format.
Custom format or a formula?
Use a custom format when the same underlying values must remain available for totals, sorting, charts, and later calculations while appearing in a compact report. It is quick to apply to a range and easy to reverse: choose General or another format to restore a different display. The main limitation is that the scale can be less obvious unless the label or suffix spells it out.
Use a formula when you genuinely need new values expressed in thousands—for example, when building a separate output table for a system that expects the number 12.5 rather than 12,500. That approach creates different data and may require adjusting formulas, units, or rounding. Do not replace source values simply to make a worksheet look shorter if a display format will do the job.
Another option is to leave the values unscaled and use a chart or report layout designed for the audience. For charts, format the axis separately if its labels need a thousands or millions scale; changing the worksheet cells does not necessarily set the chart axis scale. Whichever option you choose, state the unit in a title, axis label, or column heading and preserve enough precision for the decisions readers need to make.
Frequently Asked Questions
How do I show thousands with one decimal place in Excel?
Apply the custom format #,##0.0, through Format Cells > Number > Custom. A value of 12500 displays as 12.5, while its stored value remains 12500.
Does Excel’s thousands format change the actual number?
No. A trailing comma scales only the display. Formulas, sorting, and calculations continue to use the original value in the cell.
How do I display thousands as K?
Use #,##0,"K" for whole thousands followed by K, or #,##0.0,"K" for one decimal place. Add a heading or note explaining the scale where needed.
How do I display millions instead of thousands?
Use two trailing commas, such as #,##0,, for whole millions or #,##0.0,, for one decimal place. For example, 12,500,000 displays as 13 or 12.5, respectively.
Why does 999,999 display as 1,000?
With #,##0,, Excel divides the displayed figure by 1,000 and rounds it to a whole number. 999,999 therefore rounds to 1,000 in the display; the cell still contains 999,999.
Related
- Excel RANK vs RANK.EQ: Differences, Ties, and Examples
- Excel RANK.AVG vs RANK.EQ: Which Should You Use?
- Excel Rank Ties Without Skipping Numbers
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Double Space in Word (Microsoft 365)To double-space text in Word for Microsoft 365, select the text—or press Ctrl+A on Windows or Command+A on…
- Excel Flash Fill for Number Sequences: How It WorksExcel Flash Fill can sometimes extend a number pattern when you show it examples, but it is designed…
- Avery Labels in Word: How to Create and Print ThemTo create Avery labels in Microsoft Word, open the Mailings tab, choose Labels, select the Avery product number…
- Conditional Formatting Based on Text in Excel: RulesYes. In Excel for Microsoft 365, select the cells you want to format, then choose Home > Conditional…
- How Long Does Outlook Archive Keep Emails?Outlook does not set one universal expiration date for archived emails. Messages in an Archive folder generally stay…
- How to Hide Columns in Excel for the WebTo hide columns in Excel for the web, select the column letter or letters, right-click the selection, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.