Excel Custom Number Format for Thousands (k)

How-to basics · Updated November 19, 2026

To show Excel numbers in thousands with a “k” suffix without changing their stored values, select the cells, press Ctrl+1, choose Custom, and enter #,##0.0,"k". For example, 1,250 displays as 1.3k; the cell still contains 1,250, and Excel rounds the displayed figure to one decimal place.

How the thousands format works

Excel custom number formats control how a value appears; they do not divide, replace, or otherwise edit the underlying number. In a format such as #,##0.0,"k", the comma after the decimal-place placeholder is a scaling comma. Each scaling comma tells Excel to display the number divided by 1,000. The quoted k is a literal suffix, not a calculation or a unit Excel recognizes.

The earlier comma in #,##0 serves a different purpose: it adds the familiar thousands separator to a number that is not being scaled. Position matters. A comma used between digit placeholders groups digits, while a comma after the final digit placeholder scales the displayed value. The format above therefore combines ordinary digit grouping for large values with a final scaling comma.

Because the cell retains its original value, formulas continue to use the full number. A cell containing 1,250 formatted as 1.3k still contributes 1,250 to a SUM, and sorting uses the actual values rather than the rounded labels. That makes this approach useful for compact reports and charts where the underlying precision should remain available.

Choose a format for the level of detail

The best format depends on how much precision readers need. These examples use the same scaling rule but show different amounts of detail:

  • #,##0,"k" shows 12,500 as 13k. It is compact, but rounds to the nearest thousand.
  • #,##0.0,"k" shows 12,500 as 12.5k. It retains one decimal place in thousands.
  • #,##0.00,"k" shows 12,500 as 12.50k. It retains two decimal places, which may be useful when small differences matter.
  • 0.0,"k" shows 12,500 as 12.5k but does not add a thousands separator to a larger scaled result.
  • #,##0.0, "k" includes a space before the suffix, so the result appears as 12.5 k. The space is part of the format.

For example, using #,##0.0,"k", 1,250 appears as 1.3k, 12,500 as 12.5k, and 1,234,567 as 1,234.6k. The final result may look unusual if the intended reporting unit is millions; in that case, use two scaling commas and an M suffix instead.

Apply the format in Excel for Microsoft 365 on Windows

  1. Select the cells you want to format. To apply the format to a whole column, select the column heading; to format a range, drag across the cells.
  2. Press Ctrl+1 to open Format Cells. You can also right-click the selection and choose Format Cells.
  3. On the Number tab, choose Custom in the Category list.
  4. Click the Type box, enter a format such as #,##0.0,"k", and select OK.

The existing values should now appear with the k suffix. If you want to reuse the format elsewhere in the workbook, select the other cells and choose it from the Custom list, or copy the formatted cells and use Paste Special > Formats. Applying the format to a range does not change the values in that range.

In Excel for Mac, select the cells and press Command+1 to open the Format Cells dialog, then choose Custom and enter the format in the Type field. Excel for the web can display many custom formats made in desktop Excel, but it does not provide the same full custom-format creation and editing workflow as the desktop app. If you cannot enter or edit the format in the browser, open the workbook in the desktop version.

Control decimals, rounding, and negative values

The number of zero placeholders after the decimal point sets how many decimal places Excel displays. One zero in 0.0,"k" means one displayed decimal place; two zeros in 0.00,"k" mean two. A # placeholder can suppress an unnecessary trailing zero: 0.#,"k" can show 12,000 as 12k and 12,500 as 12.5k. Excel rounds the displayed result to the specified precision, but does not round the stored value.

To specify different appearances for positive and negative numbers, separate format sections with semicolons. For example, #,##0.0,"k";-#,##0.0,"k" uses the first section for positive values and the second for negative values. A third section can control zero values. This complete example shows positives and negatives with one decimal place and displays zero as a dash: #,##0.0,"k";-#,##0.0,"k";"–". Section order matters: positive;negative;zero;text. You do not need separate sections unless you want different treatments.

Remember that display rounding can hide small differences. If two values both appear as 1.2k, they may still be different when used in calculations. For a financial report or a close comparison, retain more displayed decimals or include a note that figures are rounded. Do not treat a compact display as a substitute for the precision required by the underlying task.

Show millions, currency, or a different suffix

Two scaling commas divide the displayed value by 1,000,000, so #,##0.0,,"M" shows 1,250,000 as 1.3M. Three scaling commas divide by 1,000,000,000; for instance, 0.0,,,"B" displays 2,500,000,000 as 2.5B. Choose suffixes that are clear to your audience, and state the unit in a chart title, column heading, or note if readers might not know what k, M, or B means.

You can combine scaling with currency symbols. For example, $#,##0.0,"k" displays 12,500 as $12.5k in a workbook using a dollar sign. This is a visual convention, not a currency conversion: Excel does not change the currency value or exchange rate. For currency-specific or regional presentation, consider using the Currency or Accounting category, or confirm that the symbol and separators match the intended locale. A custom format is primarily a display choice; it is not a reliable way to label values with a different currency.

Literal suffixes can also be words, provided they are enclosed in quotation marks. For example, #,##0.0," thousand" displays 12,500 as 12.5 thousand. Quoting literal text helps Excel distinguish it from format characters. If a suffix appears in an unexpected way, check that its quotation marks are present and that the scaling comma is after the digit placeholders.

Custom number formats versus formulas

A custom format is generally the simplest choice when the goal is only to make a table or chart label shorter. It preserves numeric values, so totals, averages, sorting, and chart calculations can still use the original data. It is also easy to change the precision later by editing the format.

A formula that divides a value by 1,000 creates a different numeric result. That can be appropriate if a separate column is deliberately meant to contain values in thousands, but it changes what downstream formulas use unless they refer to the original column. A formula using TEXT to attach a suffix produces text rather than a numeric value, which can complicate arithmetic, sorting, and charting. Avoid converting numbers to text just to make them look compact when a custom format will do.

There are cases where a separate scaled column is useful, such as exporting a dataset that specifically requires figures in thousands. In that case, make the unit explicit in the heading and keep the original values if they are needed for audit or later calculations. The format itself should not be mistaken for a data transformation.

Common problems and how to fix them

  • The value shows as 1,250k rather than 1.3k: The format probably has a quoted suffix but no scaling comma after the placeholders. Check for #,##0.0,"k", not a version with the comma missing.
  • The result has too few digits: A format without a decimal placeholder rounds more aggressively. Use #,##0.0,"k" for one decimal or add another zero for two.
  • A small value appears as 0.0k: The value is being divided by 1,000 for display and rounded to one decimal place. Show more decimals, use a conditional format with a different scale, or use a separate display column if small values need a different unit.
  • The suffix is missing or behaves oddly: Put literal text in quotation marks, for example "k". Also check for an extra comma, which can scale the value further.
  • Nothing changes after applying the format: Confirm the cells contain numeric values rather than numbers stored as text. Custom number formats affect numeric values, not text that merely looks like a number.
  • Some entries show dashes or errors: Check whether those cells contain zero, blank text, or an error value. A custom number format can control numeric zero display, but it does not turn an error into a number or necessarily affect an empty cell.

A quick way to verify the result is to click a formatted cell and inspect the formula bar. It shows the underlying value rather than the compact display. You can also temporarily switch the cell to General from the Number format menu to see the stored number, then reapply the custom format.

Frequently Asked Questions

Does Excel’s k format change the actual number?
No. A custom number format changes only how a numeric value is displayed. A cell showing 12.5k can still contain 12,500, and formulas use 12,500.

What is the Excel format code for thousands with no decimal places?
Use #,##0,"k". The scaling comma divides the displayed value by 1,000, and the quoted k is added as text.

How do I show one decimal place, such as 1.2k?
Use #,##0.0,"k". It rounds the scaled display to one decimal place, so 1,250 appears as 1.3k rather than 1.2k.

Can I use lowercase k and uppercase M?
Yes. Put the desired literal suffix in quotation marks, such as #,##0,"k" or #,##0.0,,"M". The letter is a label; the number of scaling commas determines the divisor.

Why does the formula bar still show the full number?
That is expected. The formula bar shows the stored value, while the cell uses the custom format for its on-sheet display. The difference is what lets the number remain usable in calculations.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.