How to Make Negative Numbers Red in Excel? (6 Easy Methods)

Sharing is caring!

Working with numbers in Excel often involves identifying positive and negative values quickly. By default, Excel shows all numbers in the same color, but you can change the formatting so that negative numbers appear red. This makes your worksheets easier to read and helps you analyze financial data, budgets, and reports more effectively.

In this guide, you’ll learn different methods to format negative values in red using number formatting, conditional formatting, and custom Excel functions.

Why Highlight Negative Numbers in Excel?

Highlighting negative numbers in red is common in accounting, finance, and data analysis. It allows users to:

• Spot losses or expenses faster.
• Separate profit from loss at a glance.
• Avoid confusion when working with large datasets.
• Create professional spreadsheets that follow business reporting standards.

For example, financial statements often use red to show deficits, costs, or losses, while black numbers represent positive income or profit.

Method 1: Using Built-in Number Formatting

Excel provides built-in number formatting options that allow you to display negative numbers in red without changing the actual values.

Steps to Apply Red Formatting to Negative Numbers

1. Select the cells containing numbers.
2. Right-click and choose Format Cells.
3. Go to the Number tab.
4. Select Number from the category list.
5. In the “Negative numbers” section, choose the format with red text.
6. Click OK.

Now, all negative values in the selected range will automatically turn red.

Example:

This method is quick and works well for standard datasets. However, if you need more flexibility, you should use custom number formatting.

Method 2: Custom Number Formatting

Custom number formatting gives you full control over how positive, negative, and zero values appear.

Format Structure

A custom number format uses this structure:

Positive format; Negative format; Zero format; Text format

For example:

#,##0;[Red]-#,##0;0

• #,##0 – Positive numbers
• [Red]-#,##0 – Negative numbers in red
• 0 – Zero values
• (Optional fourth part for text)

Steps to Apply Custom Format

1. Select your cells.
2. Press Ctrl + 1 (or right-click → Format Cells).
3. Go to the Custom category.
4. Enter the format code: #,##0;[Red]-#,##0;0
5. Click OK.

Now, negative numbers will display in red while positive numbers remain black.

Method 3: Conditional Formatting for More Flexibility

Conditional formatting is useful when you want more than just red text. You can apply background colors, bold styles, or icons to make negative numbers more noticeable.

Steps to Apply Conditional Formatting

1. Select the range of numbers.
2. Go to the Home tab.
3. Click Conditional Formatting → New Rule.
4. Choose Format only cells that contain.
5. Set the rule to: Cell Value < 0.
6. Click Format and select Red Font (and optional background color).
7. Click OK.

Now, every negative number in your range will automatically turn red.

Example

This method is more dynamic than built-in number formatting because you can add multiple rules (e.g., green for positive, red for negative, gray for zero).

Method 4: Using Accounting Number Format

The Accounting format is often used in finance and accounting spreadsheets. It automatically aligns currency symbols and displays negative values in red parentheses.

Steps to Apply Accounting Format

1. Select your data.
2. Go to the Home tab.
3. In the Number group, select the drop-down and choose Accounting.
4. Excel will show negative values like this: (200) in red.

Example

This style is widely used in balance sheets, income statements, and financial reports.

Method 5: Using Excel Formulas with Conditional Formatting

Sometimes, you might want advanced formatting where only specific conditions apply. For example, you may want negative numbers red only if they are less than -100.

You can use formulas in conditional formatting to achieve this.

1. Select your range.
2. Go to Conditional Formatting → New Rule → Use a formula.
3. Enter a formula such as: =A1<-100
4. Click Format and choose red text.
5. Apply and confirm.

Now, only numbers less than -100 will appear in red.

Method 6: Using VBA Macros for Automation

If you frequently need to format negative numbers across multiple worksheets, you can automate the process with a VBA macro.

Example VBA Code

Sub FormatNegativeRed()
    Dim rng As Range
    Set rng = Selection
    rng.NumberFormat = "#,##0;[Red]-#,##0;0"
End Sub

Steps

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module.
3. Copy and paste the code above.
4. Run the macro.

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

Comparison of Methods to Show Negative Numbers as Red in Excel

Here’s a summary of all methods:

Best Practices for Formatting Negative Numbers

• Always use consistent formatting across your workbook.
• Avoid using too many colors; keep it simple for readability.
• Use parentheses for financial statements, as they are industry standard.
• Combine conditional formatting with custom rules for advanced scenarios.
• When sharing reports, check that formatting works correctly on different versions of Excel.

Practical Use Cases

Highlighting negative values in red is useful in many real-life applications:

• Budget tracking: Show overspending instantly.
• Sales reports: Identify losses in revenue.
• Expense management: Highlight cost overruns.
• Investment tracking: Spot negative returns quickly.
• Accounting reports: Standardize financial statements.

By applying these methods, you can make your Excel spreadsheets more readable, professional, and useful for decision-making.

Final Thoughts

Knowing how to make negative numbers red in Excel is an essential skill for anyone working with data. Whether you use built-in formatting, conditional formatting, or custom rules, highlighting negative values makes spreadsheets clearer and easier to interpret.

If you’re working in finance, accounting, or data analysis, adopting these techniques ensures that important information stands out clearly. Choose the method that fits your needs: quick formatting for simple tasks or conditional rules and macros for advanced automation.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.