In daily work, we often mark positive number in green and negative numbers in red when calculating increasing values and decreasing values. Mark negative numbers in red color can make users lookup negative numbers immediately by one glance, so, it is useful of us to learn how to make negative numbers red in worksheet. Actually, there are some tricks to make negative numbers red in excel, this article will introduce you some of them step by step.
Precondition:
Prepare a table with increase and decrease values. Some of them are negative numbers.
Now we want to make all negative numbers in red.
Table of Contents
Method 1: Make All Negative Numbers Red by Format Cells Setting
Step 1: Select the list contains negative numbers, then right click to load menu. Click Format Cells on menu.
Step 2: On Format Cells, under Number tab, click Custom, then under Type enter 0;[Red]-0. Then click OK to confirm update.
Step 3: Verify that after above setting, all negative numbers are marked in red properly.
Comment:
If the number is displayed in percentage like -2.35%, you can enter 0.00%;[Red]-0.00% under Type. For other number types, you can just add [Red] before its type.
Method 2: Make All Negative Numbers Red by Conditional Format
Step 1: Select the list contains negative numbers.
Step 2: Click Home in ribbon, click Conditional Formatting in Styles group.
Step 3: Select New Rule in Conditional Formatting menu.
Step 4: In New Formatting Rule screen, select ‘Format only cells that contain”, then in Format only cells with section, select ‘Cell Value’, ‘Less Than’ in dropdown lists, and type ‘0’ in last textbox refer to below screenshot. Then click Format button in Preview section.
Step 5: In Format Cells screen, set negative number format per your demands. In this article, we only want to make them in red color, so in Color dropdown list, select red. Then click OK.
Step 6: In New Formatting Rule screen, you can see defined format in Preview. Then click OK.
Step 7: Then you find that all negative numbers are marked in red properly.