Formatting Issues

How to Display Excel Numbers in Thousands Using Custom Formatting

Rana Garcia Oct 19, 2026 870 views

Question details

The user wants to format large numbers in Excel to display in thousands without altering the actual cell values, and also needs to show a dash for values falling below 500.

How to Display Excel Numbers in Thousands Using Custom Formatting

Product: Excel
Device & OS: not provided
Scenario: Creating clean, easy-to-read financial reports or spreadsheets where exact large unit numbers clutter the view.
Observed behavior: By default, exact numbers display fully. The goal state is for the displayed value to scale down by 1,000 while keeping the underlying data intact for accurate calculations.

Before you start

Ensure you have selected the specific cells, ranges, or columns containing the numbers you wish to format before applying the custom number code.

Solution 1Recommended

Apply Custom Number Format to Display in Thousands

Use a trailing comma in the custom format code to automatically scale the displayed number by a factor of 1,000.

By applying a custom format with a trailing comma, Excel visually rounds and displays the number in thousands. For example, 1,113,680 will appear as 1,114. This works flawlessly for manually entered numbers, formula results, and linked cells.

Select the target cells

Highlight the cells or range containing the numbers you want to format in thousands.

Open the Format Cells dialog

Right-click the selected cells and choose 'Format Cells' from the context menu, or press the keyboard shortcut Ctrl+1.

Navigate to Custom formatting

Go to the 'Number' tab in the dialog box, and select 'Custom' from the Category list on the left.

Enter the custom format code

In the 'Type' text box, enter the code `$#,##0,` and click OK to apply the formatting.

Apply Custom Number Format to Display in Thousands

Underlying Value Remains Unchanged: The actual cell value remains the exact original number. The formatting only changes how it is visually presented, ensuring that any downstream formulas relying on these cells will still calculate correctly.

Solution 2

Display a Dash for Values Below 500

Modify your custom format to include a condition that displays a dash if the value is under 500, preventing it from showing as $0.

Advanced Spreadsheet Formatting

Format Numbers Seamlessly in WPS Spreadsheet

WPS Office provides a powerful spreadsheet application that fully supports advanced custom formatting codes, allowing you to create professional financial reports effortlessly.

1. Open your spreadsheet: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
2. Select your data and open Format Cells: Highlight your data range, right-click, and select 'Format Cells' or press Ctrl+1.
3. Apply the custom format: Click on 'Custom' under the Number tab, type `$#,##0,` in the Type field, and press OK.

Fully compatible with Microsoft Excel custom number formats.Familiar interface makes formatting and data analysis intuitive.Lightweight, fast, and completely free to use.

microsoft office alternative - wps office

Frequently Asked Questions

How do I display numbers in millions instead of thousands?

To display numbers in millions, add two trailing commas to your custom format code (e.g., `$#,##0,,`). Each trailing comma scales the displayed number down by a factor of 1,000.

Will custom formatting change my formula calculations?

No, custom formatting only changes the visual representation of the data on your screen. The underlying cell value remains unchanged and will be used accurately in all background formula calculations.

How can I add a 'K' to the end of my thousand-formatted numbers?

In the Custom format Type box, simply add the letter K enclosed in quotation marks right after the trailing comma, like this: `$#,##0,"K"`. For example, 50,000 will then display as $50K.

Document & File Issues