To show Excel values in thousands without changing the underlying numbers, apply a custom number format with a comma at the end: #,##0,. For example, 1,250,000 will display as 1,250; Excel still stores 1,250,000 for calculations.
How the thousands format works
In a custom number format, a comma immediately after the last digit placeholder scales the displayed value by 1,000. It is different from a comma between digit placeholders, which groups digits into thousands. In #,##0, the comma separates groups, so a value such as 1,250,000 displays as 1,250,000. In #,##0,, the final comma scales the display, so that same value appears as 1,250.
The format changes only how a number looks in the cell. It does not divide the stored value, change the formula result, or convert the value into text. A cell containing 1,250,000 can therefore display as 1,250 while a formula such as a sum still uses 1,250,000. This is useful for reports where figures are understood to be in thousands, but the workbook should retain exact amounts.
The comma is part of Excel’s custom number-format syntax, not a formula or a special setting for the whole workbook. If you need a different precision or a suffix such as K, you can adjust the format string while keeping the same underlying value.
Apply the format in Excel for Windows
In Excel for Microsoft 365 on Windows, select the cells you want to format, open the Format Cells dialog, and enter the custom format:
- Select the cells, range, or column. To format a whole column, click its letter at the top of the worksheet.
- Press
Ctrl+1to open Format Cells. You can also use the dialog launcher in the Home tab’s Number group. - Choose the Number tab, then select Custom in the Category list.
- Click the Type box, enter
#,##0,, and select OK.
The selected values should now appear in thousands with no decimal places. If the cells already use a format such as Currency or Accounting, entering a custom format replaces that display style, so choose a currency-specific format if you need to retain a currency symbol. To return to an ordinary display, reopen Format Cells and select a standard category such as Number or General.
On Excel for Mac, select the cells and press Command+1 to open Format Cells, then use Number > Custom and enter the format. The dialog may look slightly different from the Windows version, but the custom format code works the same way. In Excel for the web, use the number-format controls on the Home tab; if the web version does not offer the custom format you need, open the workbook in the desktop app to create or edit it.
Choose how much precision to display
The format without a decimal place rounds the scaled display to the nearest whole thousand. For example, 1,234,567 appears as 1,235 with #,##0,, while 1,234,499 appears as 1,234. The original values are not rounded; only the displayed result is. That distinction matters when a reader compares the visible figures with totals calculated from the full amounts.
To show one decimal place in thousands, use #,##0.0,. Then 1,234,567 appears as 1,234.6. Two decimal places use #,##0.00,, displaying the same value as 1,234.57. The decimal point and digit placeholders tell Excel how much precision to show after scaling; the final comma still means thousands.
#,##0,— whole thousands, such as 1,235.#,##0.0,— one decimal place, such as 1,234.6.#,##0.00,— two decimal places, such as 1,234.57.
Pick precision based on the purpose of the table. Whole thousands are often easier to scan in a high-level summary, while a decimal place can help when smaller differences matter. If the figures are close to a rounding boundary, consider showing another decimal or adding a note that values are displayed in thousands.
Add a K suffix or currency symbol
A suffix can make the unit clearer, especially when a table might otherwise be mistaken for exact amounts. To append K, use #,##0,"K". A value of 1,250,000 then displays as 1,250K. With one decimal place, #,##0.0,"K" displays it as 1,250.0K. The quoted K is literal text; without quotes, letters in a custom format can have other meanings or behave inconsistently.
For dollars, a simple format is $#,##0,, which displays 1,250,000 as $1,250. You can combine the currency symbol, precision, and suffix in $#,##0.0,"K", producing $1,250.0K. Use the symbol that matches the workbook’s currency and label the column or report so readers know the values are scaled. A number format does not perform currency conversion.
Symbols can vary by regional settings. In some Excel locales, the decimal and grouping marks are displayed differently, and Excel may show a localized version of the format. The underlying idea remains the same: use a trailing scaling comma, then test the result with a known value before applying it throughout a report.
Format negative values and zeros
Custom number formats can have separate sections for positive values, negative values, and zero, separated by semicolons. For example, #,##0,"K";[Red](#,##0,"K");- displays positive values with K, shows negative values in red parentheses, and displays zero as a dash. The first section applies to positive numbers, the second to negatives, and the third to zero. A fourth section, if supplied, is for text.
Use a format like this only when the report convention makes the meaning clear. Red text and parentheses are visual cues, not changes to the sign or value. A negative 250,000 still remains negative 250,000 even if the cell displays as (250K). Likewise, a dash for zero can be misread as missing data, so include a note or use a visible zero if that distinction matters.
Thousands, millions, and rounding limits
Two trailing commas scale a displayed number by 1,000,000. For example, #,##0,, displays 1,250,000 as 1, and #,##0.0,, displays it as 1.3. Add a literal suffix to clarify the unit: #,##0.0,,"M" displays 1.3M. The same principle applies to thousands with one trailing comma and a K suffix.
Check the scale and precision with both large and small sample values. When figures are close to zero, scaling to thousands can make them appear as 0 or -0 depending on the precision and rounding. A format cannot make a small number meaningful at a coarser scale; display more decimal places, use a smaller unit, or show the exact value in a separate column when needed.
Also consider totals. If individual rows are displayed as rounded thousands, their visible figures may not add up to the displayed total, because Excel calculates from the underlying full values and rounds each displayed cell separately. For instance, several amounts that each round up or down can produce a displayed subtotal that differs by one or more units from the sum of the visible row labels. This is a normal consequence of formatting, not necessarily a calculation error.
Number formatting versus dividing the values
Use a custom number format when the goal is presentation and the workbook should continue calculating with the original amounts. Use a separate calculation or converted data when downstream users need the cells themselves to contain the amounts in thousands—for example, when exporting the figures to another system that reads raw values. Formatting is generally reversible and preserves the source value, whereas replacing the original amounts with divided values changes the data.
A useful reporting practice is to label a heading “Amount (thousands)” or “$ in thousands” whenever the displayed values are scaled. This communicates the unit even if the K suffix is omitted. If exact amounts are important for auditing or later analysis, retain an unformatted source column or keep the original data on a separate sheet rather than relying on the display alone.
Common problems and checks
- The comma is showing instead of scaling: Confirm that it is after the final digit placeholder, as in
#,##0,, rather than between placeholders, as in#,##0. - The number looks unchanged: Check that the format was applied to the intended cells and that the cell contains a numeric value. Numbers stored as text may not respond like numeric cells.
- The value appears rounded unexpectedly: Add a decimal placeholder, such as
#,##0.0,. The stored value is still intact; the visible number is rounded to the chosen precision. - The format disappears after pasting: Pasting values or formats from another range can replace cell formatting. Reapply the custom format or use Paste Special options that preserve the desired format.
- The report total seems inconsistent: Compare the underlying values in the formula bar or temporarily switch to a full-number format. Rounded displays do not necessarily sum to the displayed total.
When checking a custom format, select one cell and inspect the Format Cells dialog’s Type box. That makes it easier to spot an extra space, a missing trailing comma, or a format applied to only part of the range. It is also sensible to test a positive number, a negative number, and zero before using a multi-section format in a final report.
Frequently Asked Questions
Does Excel’s thousands format change the actual number?
No. It changes only the cell’s display. Formulas and calculations continue to use the original value unless you separately change the data.
What format shows thousands with one decimal place?
Use #,##0.0,. For example, 1,234,567 displays as 1,234.6. The comma at the end scales the display to thousands.
How do I show thousands with a K after the number?
Use #,##0,"K" for whole thousands, or #,##0.0,"K" for one decimal place. The K is a label; it does not change the value.
Can I show values in thousands but keep the dollar sign?
Yes. A basic format is $#,##0,, which displays 1,250,000 as $1,250. Choose the currency symbol and decimal precision that fit the report, and identify the scale in a heading.
Why do my displayed rows not add up to the displayed total?
Excel calculates using the full stored values, but each cell’s display is rounded independently. Show more decimal places or compare the underlying amounts if the visible rounded figures do not reconcile.
Related
- Excel Interpolation: How to Estimate Values Between Points
- Microsoft Excel Invoice Template: Find and Customize
- Excel: Insert a Checkbox Without the Developer Tab
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 Date Format: Change and Customize DatesGoogle Sheets dates are stored as numeric values and displayed using a date format. To change how an…
- How to Make a Monthly Budget Plan in Google SheetsTo make a monthly budget plan in Google Sheets, create columns for spending categories, planned amounts, and actual…
- Google Sheets VLOOKUP: Formula, Examples, and FixesIn Google Sheets, VLOOKUP searches for a value in the first column of a range and returns a…
- Best Ways to Share an Outlook Calendar and What to KnowFor most people, the best way to share an Outlook calendar is to grant specific people access directly…
- How to Insert a Table of Contents in WordThe easiest way to insert a table of contents in Microsoft Word is to apply built-in Heading styles…
- Conditional Formatting in Excel: Color Coding GuideYes—conditional formatting in Excel automatically color-codes cells when their values meet rules you set. Select the cells, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.