Excel Show Numbers in K: Custom Formats and Formulas

Formulas & functions · Updated November 19, 2026

To show Excel numbers in thousands with a K suffix while keeping the original values intact, select the cells, press Ctrl+1, choose Custom, and enter #,##0,"K". For one decimal place, use #,##0.0,"K": for example, 12,500 displays as 12.5K, but Excel still stores and calculates with 12,500.

What Showing a Number in K Means

The letter K is a compact way to indicate thousands: 1K means 1,000, 25K means 25,000, and 1,250K means 1,250,000. In Excel, there are two different ways to produce that display. A custom number format changes how a numeric value looks without changing the value. A formula can divide the value by 1,000 and return a scaled result, or turn it into text with a K suffix.

For most worksheets, use a custom number format. It is usually the better choice for reports, dashboards, and charts because the cells remain numeric. Formulas such as SUM and AVERAGE can still use the full values, and you can change the display later without restoring data that was divided or converted to text.

Apply a Custom Number Format in Excel

In Excel for Microsoft 365 on Windows, apply a format like this:

  1. Select the cells or range you want to display in thousands.
  2. Press Ctrl+1 to open Format Cells. On a Mac, press Command+1.
  3. Choose the Number tab, then select Custom in the Category list.
  4. Click the Type box, enter #,##0,"K", and select OK.

The comma immediately before the quoted K is a scaling instruction. It tells Excel to display the number divided by 1,000. The quoted letter is a literal suffix, so it appears after the displayed number. The format changes the presentation, not the stored value: a cell containing 12500 still contains 12500 even when it appears as 13K.

To display one digit after the decimal point, enter #,##0.0,"K". Two decimal places can be shown with #,##0.00,"K". Excel’s decimal and thousands separators depend on regional settings, so the separators shown in the cell may differ from the format code you enter.

Choose the Right Format and Rounding

The right format depends on how much detail readers need. A whole-number display is compact, while decimal places make smaller differences visible. For example, using #,##0,"K", 12,450 displays as 12K and 12,750 displays as 13K. Using #,##0.0,"K", those values display as 12.5K and 12.8K. The underlying values have not been rounded in either case; only the displayed figures are rounded.

  • #,##0,"K" is useful for concise tables where approximate thousands are sufficient.
  • #,##0.0,"K" is a practical choice when tenths of a thousand matter.
  • #,##0.00,"K" gives more detail, but can make a dense report harder to scan.

Keep the scale and precision consistent across a column or chart when readers are comparing values. If one value displays as 1K and another as 1.25K, the difference in precision can distract or suggest that the numbers were prepared differently. A heading such as “Revenue (USD, thousands)” can clarify both the unit and the currency; a K suffix alone does not identify a currency or measurement unit.

Handle Negative Values and Zeros

A basic custom format applies the same style to positive and negative numbers, with a minus sign for negatives. If you want negative amounts in parentheses and zeros shown as a dash, use a format with separate positive, negative, and zero sections: #,##0.0,"K";(#,##0.0,"K");"-". The sections are separated by semicolons, in the order positive; negative; zero. With this format, 12,500 displays as 12.5K, -12,500 as (12.5K), and zero as a dash.

That dash is only a display choice: the cell still contains the numeric value zero, and calculations treat it as zero. If you need a different format for text entries, a fourth section can be added after the zero section. For most ordinary reports, the three sections are enough. Check that a dash will not be mistaken for missing data; if that distinction matters, use a visible zero or explain the convention in a note.

Keep the Original Value or Divide It?

Custom formatting is not the same as changing the data. Suppose a worksheet contains sales of 12,500 and 8,000. After applying a K format, the cells might show 12.5K and 8K, while a total still uses the original values and returns 20,500. That behavior is often desirable: readers see compact figures, but the workbook retains precision for calculations and later reporting.

Sometimes you genuinely need the values in thousands—for example, when exporting a table to a system that expects scaled numbers. In that case, use a formula in a separate column rather than overwriting source data. With the original amount in A2, enter =A2/1000 in B2; if A2 contains 12500, B2 returns 12.5. Fill the formula down for other rows. Format the result column with a suitable number format, or label it clearly as “Thousands.” This formula returns a number, so it can still be used in arithmetic.

Dividing values in place can cause confusion later, especially if someone forgets that the figures have already been scaled. Keep original data where possible, and make the unit explicit in column headings. Do not divide by 1,000 and then also apply a scaling custom format: that would scale the displayed result a second time.

When a Formula Adds the K Suffix

A formula can create text that includes K, but this has a trade-off. With the original amount in A2, enter =TEXT(A2/1000,"0.0")&"K" in B2. If A2 contains 12500, the result is the text 12.5K. The formula first scales the value, then TEXT formats it to one decimal place, and the ampersand joins the result to the letter K.

Unlike a custom number format, that formula result is text, not a numeric 12.5. It may not sort or calculate like a number, and other formulas that expect numeric values may need to refer to the original amount instead. Use this approach when the displayed string itself is needed—for example, for a text label or a specific export—not simply to make a worksheet cell look compact. In the formula, the ampersand must be written as & in HTML, but in Excel’s formula bar it is the ordinary ampersand character.

Use K Formatting in Charts and Reports

Compact number formats can make charts easier to read when values are large. In an Excel chart, axis labels and data labels can often be formatted through their Format Axis or Format Data Labels pane. Select the relevant axis or labels, open the formatting pane, expand Number, and enter a custom format such as #,##0,"K". The exact pane options depend on the chart element selected. If a chart does not show the suffix, confirm that you formatted the axis or data labels rather than only the worksheet cells.

Another option is to scale the chart’s source data using a helper column. That can be useful if the chart should show values such as 12.5 rather than 12.5K and its axis title says “Thousands.” Remember that this changes the plotted scale, so label the axis or chart title clearly. Avoid labeling data in thousands and also applying a K-scaling format to the same values, which can make the displayed units misleading.

For a PivotTable, apply the number format through the value field settings when possible, rather than formatting individual visible cells. A PivotTable may rebuild its cells when refreshed, and direct cell formatting can be lost or applied inconsistently. Look for Number Format in the value field settings, select Custom, and enter the desired format. The precise dialog wording can vary slightly by Excel build.

Common Problems and How to Fix Them

  • The value still shows all its digits: Check that the custom format includes a comma after the last digit placeholder and before the suffix, as in #,##0,"K". A format that merely adds a quoted K will append the letter without scaling.
  • The result looks too rounded: Add a decimal placeholder. Use #,##0.0,"K" for one decimal place rather than changing the original value.
  • A formula will not total correctly: If you used TEXT to attach K, the result is text. Sum the original numeric cells or use a custom number format instead.
  • The K appears twice or the number is far too small: Check whether the data was divided by 1,000 and then also given a scaling format. Choose one scaling method, not both.
  • The format seems to have disappeared: Pasting values, importing data, or refreshing a PivotTable can change formatting. Reapply the format or set it in the source or field settings where appropriate.

Excel for the web may not offer the same ability to create or edit custom formats as the Windows desktop app. If you cannot find the Custom type box, open the workbook in desktop Excel, apply the format there, and save it. Existing custom formatting may display in the web version, but editing options can be more limited. On a Mac, Format Cells is also available with Command+1; the ribbon and dialog layout may look different from Windows.

Frequently Asked Questions

Can Excel show 1,000 as 1K without changing the value?
Yes. Apply the custom format #,##0,"K". The cell displays 1K while its stored value remains 1,000.

How do I show one decimal place in K?
Use #,##0.0,"K" in Format Cells under Number > Custom. For example, 12,500 displays as 12.5K.

Does a custom K format affect SUM or other calculations?
No. It changes the display only. Excel continues to calculate with the full underlying numbers, not the rounded figures shown on screen.

How do I show millions instead of thousands?
Use two scaling commas, as in #,##0.0,,"M", to display a number in millions with one decimal place. For example, 1,250,000 displays as 1.3M. The stored value remains unchanged.

Why does my formula result ending in K not calculate as a number?
If the formula uses TEXT or joins the suffix to the result, it returns text. Use a custom number format for numeric cells, or keep a separate numeric helper column for calculations.

Can I use K formatting in Excel for the web?
Excel for the web can display many formats already saved in a workbook, but creating or editing custom formats may be limited. Use the desktop app if the Custom format controls are unavailable.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.