To display Excel values rounded to the nearest thousand without changing the stored numbers, apply the custom number format #,##0,. Select the cells, press Ctrl+1, choose Custom, and enter that code; for example, 1,234,567 will display as 1,235. The underlying value remains 1,234,567, so use a rounding formula instead if you need to change the actual number.
What the trailing comma does
In an Excel custom number format, a comma can serve two different purposes. Between digit placeholders, it marks a thousands grouping; at the end of a number format, it scales the displayed value down by 1,000. The code #,##0, combines both jobs: the final comma scales the number, while the comma between the placeholders groups the displayed thousands.
Excel then displays the scaled value with zero digits after the decimal point. That means it rounds the display to the nearest whole thousand-unit, rather than simply chopping off the last three digits. For example, 1,234,567 is displayed as 1,235 because 1,234.567 thousand rounds to 1,235 thousand. The value in the cell is still 1,234,567.
This is often useful in reports where the exact values remain important for calculations, but a compact view is easier to scan. Sales, populations, budgets, and other large figures can take up less space when presented in thousands.
Apply the format in Excel for Microsoft 365
- Select the cells you want to format. To apply it to an entire column, select the column heading.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, choose Custom from the Category list.
- Enter
#,##0,in the Type box and select OK.
You can also open Format Cells from the Home tab: select the small dialog box launcher in the Number group, then choose Number > Custom. If the selected cells contain dates or percentages, applying a number format may make them display differently, so check the result before applying it broadly.
On Excel for Mac, select the cells and press Command+1 to open Format Cells; the Custom category and Type box work in a similar way. In Excel for the web, an existing custom format can be displayed, but if you cannot create or edit the code in your current browser interface, open the workbook in the desktop app and apply it there.
Choose the display you need
The basic code is a starting point. You can add decimal places, a label, or other visual conventions while preserving the underlying value. The comma that scales the number remains important: place it after the final digit placeholder in each numeric section.
#,##0,displays 1,234,567 as 1,235.#,##0.0,displays 1,234,567 as 1,234.6, giving one decimal place in thousands.#,##0, "K"displays 1,234,567 as 1,235 K. The space and quoted K are literal text.$#,##0,displays 1,234,567 as $1,235.
For the one-decimal example, the value is first displayed in thousands (1,234.567) and then rounded to one decimal place. The result is 1,234.6, not 1,234.5. If you use a suffix, decide whether the number and unit should have a space between them; the quoted text controls what appears. In a different regional setting, Excel may show a local currency symbol or use a localized decimal separator.
Custom formatting versus rounding the value
A custom number format changes presentation, not data. A cell formatted with #,##0, can look like 1,235 while the formula bar still shows 1,234,567. A sum or lookup using that cell uses the stored value, not the shortened display. This distinction matters when the displayed number is copied into a report or when someone expects the data itself to have been rounded.
If you want a separate cell to contain the value rounded to the nearest thousand, use a formula. With the original number in A2, enter =ROUND(A2,-3) in another cell. If A2 contains 1,234,567, the formula returns 1,235,000. The negative 3 tells ROUND to round three places to the left of the decimal point. The result is a real numeric value, which can be used in later calculations; format it with #,##0, too if you want it to display as 1,235.
Those are different outcomes: the format shows thousands as a compact unit, while ROUND returns a number rounded to a multiple of 1,000. Use a display format for a report where precision must remain intact, and a formula when the rounded value is genuinely the figure you need. If a downstream calculation must use the rounded number, reference the formula result rather than the original cell.
Negative numbers, zeros, and format sections
Excel custom formats can use up to four sections separated by semicolons: positive numbers; negative numbers; zero values; and text. A single-section code such as #,##0, applies the same general pattern to numeric values, including negative values and zero. If a report needs a particular style for each case, define separate sections.
For example, #,##0,;[Red](#,##0,);- displays positive values as whole thousands, negative values in red parentheses, and zero as a dash. For 1,234,567, the display is 1,235; for -1,234,567, it is (1,235); and for zero, it is a dash. The negative section includes its own scaling comma. If you leave out a section, Excel generally uses the positive section’s pattern for the values not covered by a separate section.
Custom formats are a presentation choice, not a substitute for clear labels. If a dash represents zero or the figures are in thousands, say so in the column heading or a note. Otherwise, readers may mistake a display of 1,235 for an exact value of 1,235 rather than 1,235 thousand.
Common mistakes and checks
- Putting a comma in the wrong place: A comma between digit placeholders groups thousands; a trailing comma scales by 1,000.
#,##0displays 1,234,567 as 1,234,567, while#,##0,displays it as 1,235. - Expecting the stored value to change: Formatting does not round the cell’s underlying number. Check the formula bar or use the ROUND formula if you need an altered value.
- Forgetting the rounding threshold: With zero displayed decimal places, a scaled value of 1,234.5 rounds to 1,235. A value of 1,234.4 displays as 1,234. The display can therefore differ from simply removing three digits.
- Misreading the suffix: A format that adds K is only adding text to the display. It does not convert the stored value or attach a unit that Excel can use in calculations.
- Formatting text instead of numbers: Values imported as text may not respond like numeric cells. If the format appears to do nothing, check whether the cell contains a number rather than text.
- Overlooking regional conventions: Excel may display decimal and grouping marks differently according to regional settings. The format’s scaling behavior is the goal; confirm the visible result in the workbook’s environment.
When a format is the right choice
Use a custom format when the purpose is a more readable view, not a change to the data. It is especially handy for dashboards, printed summaries, and worksheets where users may still need to sort, sum, or audit the full-precision values. Since the values remain intact, you can change the format later to show more detail without rebuilding the data.
Use a formula or a separate reporting table when rounded numbers need to be exported, used in further calculations, or treated as the official reported values. Before sharing a workbook, make the unit explicit in a heading such as “Revenue ($ thousands).” That makes the scaling clear even when the workbook is viewed in software that handles custom formats differently.
Frequently Asked Questions
Does #,##0, round or truncate?
It rounds the displayed value to a whole number of thousands. For instance, 1,234,600 displays as 1,235, while 1,234,400 displays as 1,234. It does not truncate the underlying cell value.
How do I show one decimal place in thousands?
Apply #,##0.0,. A value of 1,234,567 displays as 1,234.6, with the cell still storing 1,234,567.
How can I add K after the number?
Use a format such as #,##0, "K" to display 1,234,567 as 1,235 K. The K is a label, not a change to the stored number.
How do I actually round a value to the nearest thousand?
If the value is in A2, enter =ROUND(A2,-3) in another cell. For 1,234,567, the formula returns 1,235,000.
Why does the formula bar show a different number?
The custom format changes only what the cell displays. The formula bar shows the full stored value, which Excel continues to use in calculations unless you create and use a separately rounded value.
Related
- Excel Column K Number: What It Means and How to Find It
- ICD-10 Z Codes List in Excel: Find, Import, and Check
- Excel Color Scales: How Conditional Formatting Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Install XLOOKUP in Excel and Check CompatibilityYou do not install XLOOKUP as a separate Excel feature or add-in: it is already built into supported…
- Excel FILTER Function: Find Rows Using Partial TextYes. In Excel for Microsoft 365, FILTER can return rows whose text contains a word or phrase by…
- Freeze a Formula in Excel: Keep Cell References FixedTo freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and…
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Excel Formula for a List of Numbers: 10 Useful OptionsTo create a list from 1 to 10 in Excel, enter =SEQUENCE(10) in an empty cell; it spills…
- Hide Columns in Excel Based on a Formula AutomaticallyExcel formulas cannot hide columns by themselves. To hide or show columns when a formula’s result changes, put…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.