Display Converted Units with a Custom Excel Number Format
Question details
The user wants to display a value in converted units using an Excel number format or formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters a value such as 1000 meters and wants to display it as 1 kilometer.
- Observed behavior
- A scaled custom format can display the value divided by 1,000 while retaining the original value; a formula can produce the converted value.
Check the units of the source values and decide whether you need to change only their appearance or calculate a converted value for use in other formulas.
Scale the Display with a Custom Number Format
Use this method to display a value divided by 1,000 without changing the stored number.
In Excel, select the cell or range containing values such as 1000 meters.
Right-click the selection, choose Format Cells, and open the Number tab.
Select Custom, enter 0, in the Type box, and click OK. A stored value of 1000 will display as 1.
To show the kilometer label as well, return to Format Cells > Custom and enter 0, "km" in the Type box.

Calculate and Format the Converted Value
Use a formula when you need the converted number for calculations or other references.
Display Converted Units in WPS Spreadsheets
Use a custom number format to show a value scaled by 1,000, or use a formula when you need the converted value in calculations.
- 1. Open your spreadsheet: Open WPS Spreadsheets and select the workbook containing the source values.
- 2. Select the cells: Select the cells you want to display in converted units, then right-click and choose Format Cells.
- 3. Set the custom format: Choose Custom under Number, enter 0, "km" in the format field, and apply it to display values divided by 1,000.
- 4. Calculate a converted value if needed: To use the converted value in calculations, enter =A1/1000 in another cell and format that cell as 0 "km" instead.

Frequently Asked Questions
Does the custom format change the value stored in Excel?
No. The format 0, changes the display only. A cell containing 1000 still stores 1000 and formulas use that original value.
How do I display the unit next to the scaled number?
In Format Cells > Number > Custom, enter 0, "km" to display a stored value of 1000 as 1 km.
Can a custom number format perform any unit conversion?
No. A trailing comma scales the display by 1,000, but arbitrary conversions require a formula. For example, enter =A1/1000 in another cell to calculate kilometers from meters.
Why does my converted value display as a whole number?
The format 0, displays no decimal places. In Format Cells > Custom, use 0.00, "km" to display two decimal places while keeping the stored value unchanged.




