To show numbers as thousands with a “k” in Excel, select the cells, press Ctrl+1, choose Number > Custom, and enter #,##0.0,"k". This displays 12,500 as 12.5k without changing the stored value; use a formula such as =A2/1000 instead if you need a separate value that is actually divided by 1,000.
The quickest way: display thousands with a custom format
A custom number format is usually the best choice when the goal is simply to make a worksheet easier to read. It changes how a number looks, not the number Excel stores. That means a cell containing 12,500 can appear as 12.5k while calculations still use 12,500.
- Select the cells you want to change.
- Press Ctrl+1 in Excel for Microsoft 365 on Windows to open Format Cells. On a Mac, use Command+1.
- Choose the Number tab, select Custom, and enter
#,##0.0,"k"in the Type box. - Select OK and check the displayed values.
The format includes one decimal place and a comma that scales the displayed number down by 1,000. The quoted letter is a literal suffix. For example, 12,500 displays as 12.5k, 8,000 as 8.0k, and 950 as 1.0k. The last result may not be desirable if you want values below 1,000 to remain in full units, so check the range of your data before applying the format broadly.
What “k” means in a spreadsheet
In labels and reports, k commonly stands for thousand: 1k means 1,000, and 25k means 25,000. Excel does not treat the letter k as a special unit by itself. It is just text. The scaling behavior comes from the comma in the custom number format, while the quoted “k” is added to the displayed result.
This distinction matters because there are two different tasks people describe as “converting numbers to k.” You might want to shorten a display while keeping the original values for sums, averages, and lookups, or you might need a new set of values expressed in thousands. Formatting handles the first task. A division formula handles the second. Choose based on what later calculations and exports need, rather than on appearance alone.
How the custom format works
Excel custom number formats can contain sections for positive numbers, negative numbers, zero, and text, separated by semicolons. A comma at the end of the numeric portion scales the displayed number by 1,000; two scaling commas divide the display by 1,000,000. A comma used within a pattern such as #,##0 instead adds thousands separators. Position is important: the final comma before the suffix is the scaling instruction.
Use #,##0,"k" to display whole thousands, or #,##0.0,"k" to show one decimal place. The first pattern makes 12,500 appear as 13k because the display is rounded to a whole thousand. The second shows 12.5k. These patterns round only what appears on screen; they do not round or replace the underlying value.
For a more controlled display of positive values, negatives, and zero, use #,##0.0,"k";-#,##0.0,"k";0. The first section applies to positive numbers, the second to negative numbers, and the third to zero. With this pattern, -12,500 appears as -12.5k and zero appears as 0. If you would rather show zero as 0.0k, replace the third section with 0.0,"k". Custom formats can be adjusted to match a report’s conventions, but keep the suffix consistent so readers know every displayed figure is in thousands.
Use a formula when you need new values
If another system or calculation needs actual values in thousands, put a formula in a separate column. Assuming the original number is in A2, enter this in B2:
With the original value in A2, enter this formula in B2 to calculate thousands: =A2/1000. If A2 contains 12,500, B2 returns 12.5. To display that numeric result with a suffix, apply the custom format 0.0"k" to B2. Unlike the earlier scaling format, this pattern has no scaling comma: the formula has already divided the value.
Keeping the result numeric is useful when you want to add or average the converted values. You can copy the formula down the column by dragging the fill handle or double-clicking it. Excel adjusts the relative reference as the formula is filled, so the next row refers to A3, then A4, and so on. Keep the original column if users may need to see or verify the full figures.
You can also create a text label directly, but text is less suitable for later arithmetic. With the original value in A2, this formula returns the text 12.5k when A2 contains 12,500: =TEXT(A2/1000,"0.0")&"k". The TEXT function controls the number of decimal places, and the ampersand joins the result to the letter. Because the result is text, formulas that expect numeric values may not treat it as a number. Use this approach for labels or presentation-only output, not as a replacement for numeric data.
Rounding, decimal places, and small values
A display format rounds to the number of decimal places it shows. With one decimal place, 1,499 becomes 1.5k because 1,499 divided by 1,000 is 1.499. With no decimal places, the same value appears as 1k. This is a visual rounding decision, not a change to the stored number. If precise values matter, show more decimal places or keep the full number visible in a nearby column.
A single scaling format applies to every selected value. It will show 500 as 0.5k, which is mathematically correct but may be awkward in a table. It will also show 999,999 as 1,000.0k with one decimal place; it will not automatically switch to 1.0m. If the data spans several orders of magnitude, consider separate presentation rules, a separate unit column, or a formula designed for the specific ranges. Avoid mixing thousands and millions without clearly labeling which unit each figure uses.
Negative values are also scaled. The simple one-section pattern normally retains Excel’s negative sign, while an explicit negative section lets you choose how the sign and decimal places appear. Check zero and negative examples after applying a format, especially in financial or operational reports where the sign carries meaning.
Show thousands on chart axes and data labels
Charts often become easier to scan when an axis displays thousands rather than long strings of digits. If the chart is based on worksheet values, format the axis independently: select the axis, open the Format Axis pane, expand Number, and enter a custom format such as #,##0.0,"k" in Format Code, then apply it. The axis labels can then show a value such as 12.5k while the source cells and chart data remain numeric.
Data labels can be formatted separately from axis labels. Select a data label, open Format Data Labels, and use its Number options to set a custom format. Check the chart after changing it: a label may have a format linked to its source cells, and changing source formatting can affect what the chart displays. If a chart will be copied into a presentation or shared with people who may not know the scale, add a clear axis title or note such as “Values in thousands.” The suffix is helpful, but an explicit unit description removes ambiguity.
Common problems and how to fix them
- The number did not change: Check that you entered a custom format in Format Cells, not into the worksheet cell itself. Also confirm the cell contains a number rather than text that looks like a number.
- The result is off by a factor of 1,000: Do not combine the scaling comma with a formula that already divides by 1,000. Use either original values plus a scaling format, or divided values plus a suffix-only format such as
0.0"k". - The letter k is missing or behaves unexpectedly: Enter it as a literal in quotation marks in the custom format, for example
0.0,"k". The comma before it is what scales the display. - The value appears rounded too much: Add a decimal place, such as changing
#,##0,"k"to#,##0.0,"k". More displayed decimals make the unit easier to interpret but take more space. - Calculations seem unchanged: That is expected with a display-only format. A cell showing 12.5k still contains 12,500. Use a division formula if the underlying result should be 12.5.
One frequent source of confusion is the difference between a displayed result and a stored result. Selecting a formatted cell and looking at the formula bar shows the original value, not the shortened label. This is a useful check when you need to confirm that the format has not altered your data.
Which method should you choose?
Use a custom number format when the numbers should remain in their original units for calculations, sorting, and data analysis, but should appear compactly in a report. This is generally the safest option for existing datasets because it does not change values or create a second column.
Use a division formula when the values themselves need to be expressed in thousands—for example, when another worksheet calculation or a downstream process expects 12.5 rather than 12,500. Preserve the original figures if traceability matters. Use a text formula only when the output is meant to be a finished label and will not need numeric operations. Before sharing the workbook, make the unit clear in a heading or note and verify a large, small, zero, and negative value if those cases occur in the data.
Frequently Asked Questions
How do I convert 1,000 to 1k in Excel?
Select the cell, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and use the custom format #,##0,"k". The value displays as 1k but remains 1,000 for calculations.
Does formatting a number as k change its value?
No. A custom number format changes only the display. To create a numeric result that is one-thousandth of the original, divide the original value by 1,000 in a formula.
How can I show one decimal place, such as 12.5k?
For a cell containing the original value, use #,##0.0,"k". For a cell whose formula has already divided the value by 1,000, use the suffix-only format 0.0"k".
Can Excel show k for thousands and M for millions automatically?
A basic custom format does not automatically switch between k and M based on a value’s size. A single scaling format always uses the same scale. For mixed ranges, use separate display rules or create a formula specifically for the desired thresholds, and label the units clearly.
Why does my formula result show 12.5 instead of 12.5k?
The division formula returns a number, not a label. Apply a suffix-only custom format such as 0.0"k", or use a text formula if the result is only for presentation. Do not add a scaling comma after dividing, because that would scale the display a second time.
Related
- Can You Track Changes in Excel? How Each Option Works
- Can You Shuffle Rows in Excel? Safe Ways to Randomize
- Excel Spreadsheet Sharing: How to Send or Collaborate
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Vending Machine Inventory Spreadsheet: Setup and FormulasA vending machine inventory spreadsheet tracks what is loaded, what remains, what sold, and what needs restocking. Start…
- Excel INDEX Array Formula: How It Works and When to Use ItIn Excel for Microsoft 365, INDEX can return a value from a range or return an array of…
- Excel Split Cell String: Formulas, Tools, and FixesTo split a cell string in Excel for Microsoft 365, enter =TEXTSPLIT(A2," ") in an empty cell to…
- HLOOKUP Examples for Practice: Formulas and Fixes in ExcelFor an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in…
- Switch Excel Formulas to Values: Methods and TipsTo switch formulas to fixed values in Excel for Microsoft 365 on Windows, select the formula cells, press…
- Excel SUM Times Formula: Multiply Values, Then Add ThemIf quantities are in B2:B5 and unit prices are in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5) to multiply each quantity by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.