When working with financial data or any dataset with positive and negative numbers, it’s often useful to highlight negative numbers in red. Excel 2013 offers several ways to do this — through number formatting or conditional formatting.
Method 1: Using Custom Number Formatting
Custom number formats allow you to automatically display negative numbers in red without affecting positive numbers.
Step-by-Step:
-
Select the Cells
Highlight the range of cells containing the numbers you want to format. -
Open Format Cells
-
Right-click the selected cells and choose Format Cells,
-
Or press Ctrl + 1 on your keyboard.
-
-
Go to Number Tab
In the Format Cells dialog box, click on the Number tab. -
Choose Custom
-
Select Custom from the list on the left.
-
In the Type field, enter the following format:
Explanation:
-
0→ format for positive numbers -
[Red]-0→ format for negative numbers (appears in red with a minus sign)
-
-
-
Click OK
Your negative numbers will now automatically display in red while positive numbers remain black.
✅ Tip: You can also use other variations, like #,##0;[Red]-#,##0 for numbers with commas.
Method 2: Using Conditional Formatting
Conditional formatting lets you dynamically change text color based on conditions. This is very useful if you want more flexibility, like coloring numbers red when they fall below 0.
Step-by-Step:
-
Select Your Cells
Highlight the numbers you want to format. -
Go to Conditional Formatting
-
Click the Home tab.
-
In the Styles group, click Conditional Formatting → Highlight Cells Rules → Less Than…
-
-
Set the Condition
-
Enter 0 in the value field.
-
Choose a formatting option.
-
You can select Light Red Fill with Dark Red Text or click Custom Format for full control.
-
-
-
Apply the Format
Click OK, and all negative numbers in your selected range will turn red automatically.
Bonus Tips
-
Dynamic Updates: Both custom formatting and conditional formatting will automatically apply to new negative numbers entered in the formatted range.
-
Combine with Currency: To show negative currency in red, use:
-
Remove Formatting: To remove custom formatting, select the cells → Clear Rules (for conditional formatting) or reset the Format Cells to General.
Summary Table
| Method | Steps | Pros | Cons |
|---|---|---|---|
| Custom Number Format | Format Cells → Custom → 0;[Red]-0 | Automatic, clean | Slightly less flexible than conditional formatting |
| Conditional Formatting | Home → Conditional Formatting → Highlight Cells → Less Than 0 | Flexible, works with rules | Slightly more clicks to set up |
