5 Ways To Show Parentheses For Negative Numbers In Excel

Negative numbers are an essential part of various mathematical operations, and representing them in a clear and readable format is crucial, especially in spreadsheet applications like Microsoft Excel. One of the most common ways to indicate negative numbers is by using parentheses instead of the traditional minus sign (-). This format is widely used in financial and accounting contexts, as it can make financial statements and calculations easier to read and understand.

In this article, we will explore five different methods to display negative numbers with parentheses in Excel, along with step-by-step instructions, examples, and screenshots to help you master these techniques.

Method 1: Using the Format Cells Option

One of the simplest ways to show negative numbers with parentheses in Excel is by using the Format Cells option. Here's how:

1. Select the cells that contain the negative numbers you want to format.
2. Right-click on the selected cells and choose Format Cells from the context menu.
3. In the Format Cells dialog box, click on the Number tab.
4. Select the Category as "Number" and then click on the Custom option.
5. In the Type field, enter the following format code: #,##0.00_);(#,##0.00)
6. Click OK to apply the format.

This format code will display negative numbers with parentheses, and the resulting format will look like this: (123.45)

Method 2: Using the TEXT Function

Another way to display negative numbers with parentheses in Excel is by using the TEXT function. Here's how:

1. Enter the following formula in a new cell: =TEXT(A1,"#,##0.00_);(#,##0.00)")
2. Replace A1 with the cell reference that contains the negative number you want to format.
3. Press Enter to apply the formula.

This formula will display the negative number with parentheses, and the resulting format will look like this: (123.45)

Method 3: Using Conditional Formatting

You can also use Conditional Formatting to display negative numbers with parentheses in Excel. Here's how:

1. Select the cells that contain the negative numbers you want to format.
2. Go to the Home tab in the Excel ribbon.
3. Click on the Conditional Formatting button in the Styles group.
4. Choose New Rule from the drop-down menu.
5. Select the "Use a formula to determine which cells to format" option.
6. Enter the following formula: =A1<0
7. Replace A1 with the cell reference that contains the negative number you want to format.
8. Click on the Format button and select the Number tab.
9. Select the Category as "Number" and then click on the Custom option.
10. In the Type field, enter the following format code: #,##0.00_);(#,##0.00)
11. Click OK to apply the format.

This method will apply the format to all cells that contain negative numbers, and the resulting format will look like this: (123.45)

Method 4: Using VBA Macro

If you want to automate the process of displaying negative numbers with parentheses in Excel, you can use a VBA macro. Here's how:

1. Press Alt + F11 to open the Visual Basic Editor.
2. In the Editor, click on Insert > Module to insert a new module.
3. Paste the following code in the module: Sub FormatNegativeNumbers()
4. Range("A1:A10").NumberFormat = "#,##0.00_);(#,##0.00)"
5. End Sub
6. Replace A1:A10 with the range of cells that contain the negative numbers you want to format.
7. Press F5 to run the macro.

This macro will apply the format to all cells in the specified range that contain negative numbers, and the resulting format will look like this: (123.45)

Method 5: Using Power Query

Finally, you can use Power Query to display negative numbers with parentheses in Excel. Here's how:

1. Go to the Data tab in the Excel ribbon.
2. Click on the From Table/Range button in the Get & Transform Data group.
3. Select the table or range that contains the negative numbers you want to format.
4. Click on the Add Column tab in the Power Query Editor.
5. Click on the Custom Column button.
6. Enter the following formula: = if [Column1] < 0 then "(" & Text.From([Column1], "#,##0.00") & ")" else Text.From([Column1], "#,##0.00")
7. Replace Column1 with the column name that contains the negative numbers you want to format.
8. Click on the Close & Load button to load the data back into Excel.

This method will apply the format to all cells in the specified column that contain negative numbers, and the resulting format will look like this: (123.45)

Gallery of Negative Number Formats

Negative Number Formats Gallery

We hope this article has helped you learn five different ways to display negative numbers with parentheses in Excel. Whether you prefer to use the Format Cells option, the TEXT function, Conditional Formatting, VBA macro, or Power Query, you can now format your negative numbers in a clear and readable format. Do you have any favorite methods for formatting negative numbers in Excel? Share your tips and tricks in the comments below!