How to Make Negative Numbers in Brackets in Excel: A Complete Guide

When working with financial data in Excel, it's common to show negative numbers in brackets. This makes them stand out, improving report clarity and look. We'll explore how to do this using Excel's features, custom formatting, and VBA code.

Method 1: Using Excel’s Built-in Number Formatting

Excel makes it easy to format negative numbers with brackets using its built-in options.

Steps:

1. Select the Range: Choose the cells where you want to format numbers.
2. Open Format Cells: Right-click and choose Format Cells, or press Ctrl + 1.
3. Choose Number Formatting:
   • Under the Number tab, pick Number.
   • In the Negative Numbers section, select the bracket option (e.g., (1234.10)).
   • Click OK to apply the changes.

Example:

Before:

-1234.10

After applying the format:

(1234.10)

Method 2: Using Custom Number Formatting

Custom number formatting gives you more control over negative numbers. You can specify how positive, negative, and zero values appear.

Steps:

1. Select the Range: Highlight the cells you want to format.
2. Open Format Cells: Right-click and select Format Cells, or use Ctrl + 1.
3. Custom Format: In the Format Cells dialog, go to the Number tab and select Custom.
4. Enter Custom Format: In the Type field, enter this custom format:lessCopy#,##0.00;[Red](#,##0.00);0.00
   • The first part (#,##0.00) shows how positive numbers should look.
   • The second part ([Red](#,##0.00)) shows negative numbers in brackets with red text.
   • The third part (0.00) shows how zero values should appear.

   You can change the decimal places and negative color by tweaking the format.

Example:

Before:

-1234.10

 

After applying the custom format:

(1,234.10) 

in red color.

Method 3: VBA Code to Automatically Format Negative Numbers

VBA code can automate formatting negative numbers in brackets. It's useful for multiple ranges across your workbook.

Sub FormatNegativeNumbersInBrackets()
    Dim rng As Range
    Dim cell As Range

    ' Define the range (modify to your needs)
    Set rng = Selection

    ' Loop through each cell in the selected range
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            ' If the value is negative, format it with brackets
            If cell.Value < 0 Then
                cell.NumberFormat = "[Red](#,##0.00)"
            End If
        End If
    Next cell
End Sub

Explanation:

• rng: This defines the range of cells you want to format. You can select the range manually or define it in the code (e.g., Set rng = Range("A1:A10")).
• IsNumeric(cell.Value): This ensures the code only applies to numeric values.
• cell.NumberFormat: The line that applies the custom format for negative numbers, displaying them in red and within brackets.

Steps to Run the VBA Code:

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module by going to Insert > Module.
3. Copy and paste the VBA code into the module.
4. Close the editor and go back to your Excel sheet.
5. Select the range of cells you want to format.
6. Press Alt + F8, choose FormatNegativeNumbersInBrackets, and click Run.

Example Output:

For the range A1:A5 containing the following numbers:

1234.10
-1234.10
456.50
-456.50
789.00

After running the VBA code, the negative values will be displayed as:

1234.10
(1,234.10)
456.50
(456.50)
789.00

Method 4: Formatting Negative Numbers in Brackets Dynamically Using Conditional Formatting

Excel's custom formatting is great, but for dynamic negative numbers, use Conditional Formatting.

Steps:

1. Select the Range: Highlight the cells where you want to apply the formatting.
2. Open Conditional Formatting: Go to Home > Conditional Formatting > New Rule.
3. Choose Format Cells Based on Their Value: Choose Format only cells that contain.
4. Set the Condition: In the rule description, select Cell Value less than 0.
5. Format with Custom Number Format: Click Format, go to the Number tab, select Custom, and enter the following format:scssCopy(0)This will display negative numbers in brackets.

Conclusion

Formatting negative numbers in brackets makes financial data in Excel easier to read. You can use built-in formatting, custom formats, or VBA code. VBA is best for large data sets or automation.

By using these methods, you can make Excel fit your reporting needs. This ensures negative numbers are clear and consistent.