Formatting Issues

Display Converted Units with a Custom Excel Number Format

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

The user wants to display a value in converted units using an Excel number format or formula.

Display Converted Units with a Custom Excel Number Format
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.
Before you start

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.

Solution 1Recommended

Scale the Display with a Custom Number Format

Use this method to display a value divided by 1,000 without changing the stored number.

1
Select the values

In Excel, select the cell or range containing values such as 1000 meters.

2
Open Format Cells

Right-click the selection, choose Format Cells, and open the Number tab.

3
Apply a scaled format

Select Custom, enter 0, in the Type box, and click OK. A stored value of 1000 will display as 1.

4
Add a unit label

To show the kilometer label as well, return to Format Cells > Custom and enter 0, "km" in the Type box.

Scale the Display with a Custom Number Format
The stored value stays the same: The trailing comma scales the displayed value by 1,000. Calculations still use the original value, and the format rounds the display to a whole number.
WPS Spreadsheets

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. 1. Open your spreadsheet: Open WPS Spreadsheets and select the workbook containing the source values.
  2. 2. Select the cells: Select the cells you want to display in converted units, then right-click and choose Format Cells.
  3. 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. 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.
Display values in kilometers while retaining the original numbersUse formulas to calculate converted valuesOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.