Formatting Issues

Format Excel Negative Numbers in Red Parentheses and Zeros as Dashes

Kushani Nimanthika Sep 28, 2026 873 views

Question details

The user needs a custom number format in Excel to display positive numbers with a thousands separator, negative numbers in red text enclosed by parentheses, and zero values replaced by dashes.

How to Format Excel Negative Numbers in Red Parentheses and Zeros as Dashes

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting financial or accounting data to clearly distinguish between positive values, negative values, and empty or zero-value cells.
Observed behavior: By default, negative numbers are displayed with a standard minus sign and zeros are shown as a '0' digit. A custom formatting rule is required to achieve the exact stylistic presentation.

Before you start

Highlight the range of cells containing the data you want to format before applying the custom number rule to ensure your spreadsheet updates simultaneously.

Solution 1Recommended

Apply a Standard Custom Number Format

Use a standard custom format code to quickly change how negatives and zeros are displayed without complex alignment rules.

Excel custom number formats are separated into up to four sections by semicolons: positive numbers, negative numbers, zeros, and text.

By modifying the specific sections, you can enforce the exact display style without altering the underlying data.

Select the target cells

Highlight the cells or columns containing the numbers you wish to format.

Open the Format Cells dialog

Right-click the selected cells and choose 'Format Cells', or press 'Ctrl + 1' on your keyboard.

Navigate to the Custom category

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

Enter the format code

In the 'Type' input box, enter the following exact code: #,##0;[Red](#,##0);"-" and click OK.

Format Breakdown: The section '#,##0' handles positive numbers with thousands separators. The '[Red](#,##0)' changes negative numbers to red and adds parentheses. The '"-"' converts zeros into dashes.

Solution 2

Apply an Accounting-Style Aligned Format

Use an advanced format code with underscores to align positive and negative numbers perfectly in columns, preventing the right parenthesis from offsetting the digit alignment.

Format Cells Easily in WPS Office

Apply Custom Number Formats in WPS Spreadsheet

WPS Office provides full support for advanced custom cell formatting, including complex accounting formats, conditional coloring, and seamless compatibility with Microsoft Excel files.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you want to format.
2. Select the cells: Highlight the numeric values you want to modify.
3. Access cell formatting: Right-click the selection and choose 'Format Cells', then click on the 'Number' tab.
4. Apply the custom format: Select 'Custom', paste your desired code (such as #,##0;[Red](#,##0);"-") into the Type box, and click OK.

Free and lightweight office suiteFully compatible with Microsoft Excel (.xlsx) formatsSupports advanced custom number formatting rules nativelyFamiliar user interface makes formatting financial data quick and easy

microsoft office alternative - wps office

Frequently Asked Questions

What do the semicolons mean in an Excel custom number format?

Semicolons are delimiters that divide the custom format into specific conditional sections. A complete format has four parts: Positive format; Negative format; Zero format; Text format. By leaving parts blank or editing them, you control exactly how each data type displays.

Can I use colors other than red for negative numbers?

Yes, Excel and WPS Spreadsheet support several standard color names enclosed in square brackets. For example, you can replace [Red] with [Blue], [Green], [Magenta], [Yellow], [Cyan], or [White].

How do I align the parentheses to the far left of the cell?

To force a character to the edge of a cell, use the asterisk symbol (*) followed by a space. This tells the spreadsheet program to repeat the space character until the cell is filled. Adding this to your format code will push your symbols to opposite edges, common in formal accounting formats.

Document & File Issues