Formatting Issues

How to Display Small Excel Values as a Dash with Custom Formatting

Huma Ashraf Ch Oct 22, 2026 870 views

Question details

The user needs to format a numeric column so that values below a specific threshold display as a dash, while larger values are displayed in thousands.

How to Display Small Excel Values as a Dash with Custom Formatting

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Formatting financial or numeric reports where small, insignificant values should be visually replaced with a dash to improve readability.
Observed behavior: Numbers are currently displaying their exact values, and the goal is to conditionally show a dash for values under a selected threshold while formatting the rest in thousands.

Before you start

Determine the exact threshold for your data (e.g., values under 1,000) and decide how negative values should be handled before applying the custom format.

Solution 1Recommended

Apply Conditional Custom Number Formatting

Use a custom format code with conditional brackets to display a dash for numbers below your threshold and format larger numbers in thousands.

Spreadsheet software allows you to use conditional statements within custom number formats by using square brackets. A format code like [<1000]"-";#,###, tells the program to output a dash for positive numbers under 1,000, and format everything else in thousands.

Select the target cells

Highlight the numeric column or specific cells you want to apply the formatting to.

Open the Format Cells dialog

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

Navigate to Custom formatting

In the Format Cells window, go to the 'Number' tab and select 'Custom' from the Category list on the left side.

Enter the custom format code

In the 'Type' text box, enter your conditional format code. For example, to hide values under 1,000 and format larger numbers in thousands, enter: [<1000]"-";#,###,

Apply and test

Click 'OK' to apply the formatting. Test boundary values (positive, negative, and zero) to ensure the formatting behaves exactly as expected.

Apply Conditional Custom Number Formatting

Handling Compound Conditions for Negative Values: If you need to handle ranges including negative values (e.g., showing a dash for values between -1,000 and 1,000), you must extend the condition. Use a format like [>=1000]#,###,;[<=-1000]-#,###,;"-" to properly format large positives, large negatives, and replace the middle range with a dash.

Professional Data Formatting

Format Complex Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced custom number formatting, including conditional formatting rules. It allows you to clean up your financial reports and dashboards effortlessly while maintaining complete compatibility with Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the data you wish to format.
2. Access formatting tools: Select the target cells and press Ctrl + 1 to open the Format Cells dialog box.
3. Apply custom format: Choose the Custom category, input your conditional threshold code in the Type field, and click OK to instantly update your view.

Fully compatible with Microsoft Excel custom number formats (.xlsx).Intuitive Format Cells dialog for quick adjustments.Free and lightweight office suite for seamless data management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use custom formatting to hide zero values completely?

Yes. To hide only zeros and leave other numbers normal, use a custom format like 0;-0;;@ where the third section (representing zero values) is intentionally left blank.

Why isn't my custom format applying to negative numbers correctly?

Standard custom formats follow the structure Positive;Negative;Zero;Text. When you use conditional brackets like [<1000], it overrides this standard structure. You must define specific conditions for negative numbers, such as [>=1000]#,###,;[<=-1000]-#,###,;"-".

Will changing the format alter my underlying data?

No, custom number formatting only changes how the data is displayed on the screen and in print. The underlying value remains the exact number, so formulas and calculations will not be affected.

Document & File Issues