Excel Thousands Display: Every Way to Display Values in Thousands Easily
When working with numbers in Excel, there is one request you hear often: “Keep the numbers unchanged, but display them in thousands so they are easier to read.” For example, you may want to show 12,500 as 12.5K in a report. This practical guide covers displaying thousands in Excel for tables, charts, PivotTables, and Power Query.
Quick Fix: Display Values in Thousands in 3 Minutes
- Add only a comma thousands separator, such as 1,000
Select the range →Ctrl + 1(Format Cells) → Number tab → select “Use 1000 Separator (,).” - Abbreviate values with a unit such as 1K (custom format)
Select the range →Ctrl + 1→ Number format: Custom →0,"K"or0.0,"K" - Display chart axes in thousands
Click the chart axis →Ctrl + 1→ Axis Options → Display units: Thousands
“Change Only the Display” vs. “Actually Divide the Values”
- Change only the display (recommended): The original values remain unchanged, while only the display uses K or thousands. This keeps calculations and sorting safe.
- Actually divide the values: The value itself changes, as in
=A2/1000. Use this only when you need to store values in thousands.
Method 1) Use a Thousands Separator (1,000 Format)
Go to the Home tab → Number group → Comma Style (,) or press Ctrl + 1 → Number → select “Use 1000 Separator (,).” This displays 1000 as 1,000.
Method 2) Use a Custom Format to Abbreviate Values as 1K
Key rule: Adding a comma (,) at the end of a custom number format divides the displayed value by 1,000. Two commas display values in millions.
Display as K
0,"K"→ 1000 = 1K, 12500 = 13K (rounded)0.0,"K"→ 12500 = 12.5K
Display as “thousand”
0,"thousand"→ 1000 = 1 thousand0.0,"thousand"→ 12500 = 12.5 thousand
Handle Negatives, Zeros, and Decimals at Once
A custom number format can separate sections in the order positive;negative;zero;text.
0.0,"K";(0.0,"K");"-";@(negatives in parentheses and zeros as a dash)[>=1000]0.0,"K";0(values below 1,000 remain unchanged)
Method 3) Create Display Text with Functions (TEXT/ROUND/LET)
If you need text for use in a sentence or report, TEXT is quick (but the result is text).
=TEXT(A2,"0.0,")&"K"
=TEXT(A2,"0,")&"thousand"Keep source values as numbers when you need calculations, and create a separate display column.
Method 4) Display Chart Axes in Thousands (Display Units)
- Click the chart, then click the axis.
- Press
Ctrl + 1→ Axis Options. - Set Display Units to Thousands.
Method 5) Display Thousands in a PivotTable/PivotChart
- Select a number in the PivotTable Values area, then right-click.
- Select Value Field Settings → Number Format.
- Under Custom, apply
0.0,"K"or0,"thousand".
Method 6) Create a Thousands Column in Power Query
- Go to the Data tab → From Table/Range.
- Select Add Column → Custom Column.
=[Sales]/1000(new column: Sales (K))
This method actually divides the values. Use it only when you need to store values in thousands.
Method 7) Create a Thousands Measure in Power Pivot (DAX)
Sales (K) := DIVIDE([Sales], 1000)Practical Example: Recreate a “Monthly Sales (Thousands)” Report
| Month | Sales |
|---|---|
| January | 12500 |
| February | 980 |
| March | 250000 |
| April | 0 |
| May | -13500 |
Target output: 12.5K / 980 / 250.0K / – / (13.5K)
Example all-in-one format:
[>=1000]0.0,"K";[<=-1000](0.0,"K");"-";0
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Does not change to 1K | Custom format was not applied | Ctrl+1 → Custom → 0,"K" |
| Shows 13K instead of 12.5K | No decimal format | Change to 0.0,"K" |
| Totals are incorrect | Values were converted to text with TEXT | Keep the original number column and use a separate display column |
| PivotTable formatting resets after refresh | Only cell formatting was applied | Value Field Settings → Number Format |
| Chart axis remains too large | Axis display units were not set | Axis → Ctrl+1 → Thousands |
Checklist
- Does your source data remain numeric (avoid overusing =TEXT)?
- Did you avoid actually dividing by 1,000 when you only need a shorter display?
- For PivotTables, did you set the format through “Value Field Settings > Number Format”?
- Did you set chart axis display units to Thousands?
Related Articles (Internal Links)
- Remove Decimals in Excel: Rounding, Rounding Down, and Truncation
- Display Billions in Excel: Make Large Numbers Easier to Read
- How to Merge Multiple Excel Files at Once
- Practical Patterns for Using the Excel IF Function to Its Limits
- Top Excel Sites by Traffic Worldwide
External References (Official Documentation)
- Microsoft Support: Create a Custom Number Format
- Microsoft Support: Change the Display Units on a Chart Axis