How to Make Negative Numbers Red in Excel: A Simple Guide

Excel is great for organizing and analyzing data. But sometimes, the default formatting isn't enough. Highlighting negative numbers in red can help spot financial losses or errors easily.

In this blog, we'll show you how to make negative numbers red in Excel. We'll cover conditional formatting and VBA code for custom scenarios.

Method 1: Using Conditional Formatting to Color Negative Numbers Red

The easiest way to highlight negative numbers is through Conditional Formatting. Here's how to do it:

Step-by-Step Process:

1. Select the Range:
   • First, pick the cells with the numbers you want to format. Click on the column letter at the top for the whole column.
2. Open Conditional Formatting:
   • Go to the Home tab on the Excel ribbon.
   • In the Styles group, click on Conditional Formatting.
3. Create a New Rule:
   • Click on New Rule from the dropdown.
   • In the "New Formatting Rule" dialog box, choose Format only cells that contain.
4. Set the Condition:
   • Under "Format cells with," pick Cell Value from the first dropdown.
   • Set the second dropdown to less than and type 0 in the value field.
5. Choose a Format:
   • Click the Format button to pick the color and style for negative numbers.
   • In the Font tab, choose Red (or any other color you like) for the text color.
   • Click OK to close the Format Cells window, then click OK again to apply the rule.

Now, any negative numbers in your selected range will show up in red.

Method 2: Using VBA Code to Color Negative Numbers Red

For advanced users or those wanting a custom solution, VBA can color negative numbers red. VBA offers more flexibility, letting you create scripts for various scenarios.

Example 1: Simple VBA Code for One Range

This example uses VBA to make negative numbers in a specific range red. Here's the code:

Sub FormatNegativeNumbersMultipleRanges()
    Dim cell As Range
    ' Define multiple ranges
    Dim ranges As Variant
    ranges = Array("A1:A10", "B1:B10", "C1:C10") ' List the ranges you want to format
    
    ' Loop through each range
    For Each rangeAddress In ranges
        For Each cell In Range(rangeAddress)
            If IsNumeric(cell.Value) And cell.Value < 0 Then
                cell.Font.Color = RGB(255, 0, 0) ' Red color for negative numbers
            End If
        Next cell
    Next rangeAddress
End Sub

How to Use This Code:

1. Open the VBA Editor:
   • Press Alt + F11 to open the Visual Basic for Applications editor.
2. Insert a New Module:
   • In the editor, click Insert > Module to add a new module.
3. Paste the Code:
   • Copy the code above and paste it into the new module.
4. Run the Code:
   • Close the VBA editor and return to Excel.
   • Press Alt + F8, select FormatNegativeNumbersRed, and click Run.

This script will check all values in cells A1:A10. If the value is negative, it will turn red; if not, it stays black.

Example 2: Format Entire Column with VBA

To format an entire column, change the range like this:

Sub FormatNegativeNumbersRedEntireColumn()
    Dim cell As Range
    ' Loop through all cells in column A
    For Each cell In Range("A:A")
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Font.Color = RGB(255, 0, 0) ' Red color
        End If
    Next cell
End Sub

This script will check column A and turn any negative values red.

Example 3: VBA Code for Multiple Ranges

For more flexibility, you can format multiple ranges or entire sheets. Here’s a more advanced script:

Sub FormatNegativeNumbersMultipleRanges()
    Dim cell As Range
    ' Define multiple ranges
    Dim ranges As Variant
    ranges = Array("A1:A10", "B1:B10", "C1:C10") ' List the ranges you want to format
    
    ' Loop through each range
    For Each rangeAddress In ranges
        For Each cell In Range(rangeAddress)
            If IsNumeric(cell.Value) And cell.Value < 0 Then
                cell.Font.Color = RGB(255, 0, 0) ' Red color
            End If
        Next cell
    Next rangeAddress
End Sub

This script lets you specify multiple ranges. It will format negative values in red in each range.

Method 3: Using Excel Formulas for Dynamic Conditional Formatting

Another way is using Excel formulas in conditional formatting. This is good for complex conditions, like formatting based on multiple criteria.

1. Select the Range:
   • Select the cells where you want to apply the formatting.
2. Create a New Rule:
   • Go to Home > Conditional Formatting > New Rule.
   • Select Use a formula to determine which cells to format.
3. Enter the Formula:
   • • In the formula box, enter this formula:

   =A1<0

   • Change A1 to the top-left cell of your range.
4. Set the Format:
   • Click Format, choose red for the font, and then click OK.

Conclusion

Making negative numbers stand out is easy in Excel. You can use Conditional Formatting for simplicity or VBA for more flexibility. Both methods help you see negative numbers in red.

Choose the method that fits your needs. Whether it's for a small data set or across many sheets. Excel's tools and VBA code make data analysis easier. And they help highlight important values. Happy formatting!