3 Useful Methods to Make Negative Numbers Red in Your Excel Worksheet

Sometimes you need to highlight negative numbers. And in this article, we will demonstrate 3 useful methods to fulfill this task.

Suppose you are analyzing the sales volume of the last year. To make things easier, you need to make all the negative numbers red.An Example for Negative Number

If you do this by changing the font color one by one, you will certainly spend a lot of time. And in the coming article, you may learn about 3 effective methods.

Method 1: Change Number Format

In this method, you need to change the number format for certain cells.

  1. Select the target area in the worksheet. In this example, we will select range C3:C13.
  2. After that, right click the selected area.
  3. And then in the menu, choose the option “Format Cells”.Format Cells

Besides, you can also directly click the small arrow in the “Number” area in the toolbar. This is the same as the previous two steps

  1. Now in the “Format Cells” window, click the “Number” in the “Category”.
  2. And then choose a type for the negative number. There are four options available. Here we choose the second one.
  3. In this step, change the “Decimal places” if you need.Choose One Option
  4. After that, click the button “OK”. Thus, you can see the negative numbers are all in red.Red Number

But using this method, there will be decimal numbers in cells. And the minus sign “-“ will also disappear. To solve this problem, you can use a customized number format.

  1. Still in the “Format Cells” window, choose the option “Custom”.
  2. And then input this new type into the text box:

General;[Red]-General

The positive number will remain the same, while the negative number will be marked red.Custom Type

  1. Next click the button “OK”. Therefore, you will now get the new result in the worksheet.New Result

In addition, if you don’t want to show the “-“ in cells, you can change the type into this:

General;[Red]General

And you can also use other colors in the type. You can use 8 different colors in the custom format: Black, Blue, Cyan, Green, Magenta, Red, Yellow and White.

Method 2: Use Conditional Formatting

Instead of changing the number format, you can also use the conditional formatting feature in Excel.

  1. Select the target area in the worksheet.
  2. Next click the button “Conditional Formatting” in the toolbar.
  3. And then move your cursor onto the option “Highlight Cells Rules”.
  4. Next, choose the option “Less Than” in the sub menu.Less Than
  5. In the “Less Than” dialog, input the number “0” into the text box.
  6. And click the small arrow in the format text box.
  7. In the drop-down list, choose the option “Red Text”.Input 0

Of course you can also choose other format or custom format for those negative numbers.

  1. When you finish the setting, click “OK” in the window.Click OK

The negative numbers will also be red in the range.

Except for using the predefined condition rules, you can also create new rules.

  1. Still select the target area.
  2. And then click the button “Conditional Formatting”.
  3. After that, choose the “New Rule” in the drop-down list.New Rule
  4. And now choose the last option in the new pop-up window.
  5. Next input this formula into the text box.

=(C2<0)

  1. After that, click the button “Format”.New Formatting Rule
  2. In the “Format Cell” window, set the font color in the window. Here we only set the font color. You can also set other format according to your need.Set Format
  3. Next click “OK”.
  4. And then continuing “OK” in the “New Formatting Rule” window.Click OK Again

Thus, you can also make negative numbers red according to the above steps.

Result

Method 3: Use VBA Process

In using the last method, you should use VBA process in Excel.

  1. Press the shortcut keys “Alt +F11” on the keyboard. And now you have activated the Visual Basic editor.
  2. And then click the button “Insert” in the toolbar.
  3. After that, choose the option “Module” in the sub menu. Thus, you have inserted a new module into the worksheet.Insert a Module
  4. Now input the following VBA codes into the module:

Sub NegativeNumber()

    Dim cel As Range

    For Each cel In Range(“C2:C13”)

        If cel.Value < 0 Then

            cel.Font.Color = vbRed

        End If

    Next

End Sub

In this module, we will mark the cells red if it contains negative numbers. And you may also change the range according to the actual worksheet. Besides, if you also need to use a special format, you can also modify the VB codes according to your need.

  1. After that, press the button “F5” on the keyboard. You can also click the button “Run Sub” in the toolbar. And then you can get the result immediately.Result

On the other hand, if you need to hide the “-“in the worksheet, you can add the following codes into the “if-end if” statement into the codes:

If cel.Value < 0 Then

cel.Value = Abs(cel.Value)

cel.Font.Color = vbRed

End If

After you run the sub, the minus sign will disappear in the worksheet.No Minus Sign

From the above steps, you can see that using VBA process is also very easy.

Compare the 3 Methods

The next time if you need to make negative numbers red, you may wonder which method you should use. You can refer to the table below and have a more comprehensive understanding about them.

Comparison

Change Number Format

Conditional Formatting

Use VBA Process

Advantages

1.      Format cells can give you a lot of flexibility on the number format.

2.      This method is very easy for most of the users. When you are unfamiliar with VBA or conditional formatting, you can choose this method.

1.      You don’t need to change the cell format by the conditional formatting.

2.      You can either use the pre-defined rules or create a new rule by yourself.

1.      You can quickly get the result with just one click.

2.      Using VBA codes can help you fulfill more complicated tasks.

Disadvantages

1.      Only 8 different colors are available in this method.

2.      In this method, you need to change the cell format.

1.      You cannot hide the minus sign through this method.

2.      Compared with other methods, there are more steps in this method, especially when you need to use a formula in the conditional formatting.

1.      If you are not familiar with VBA codes, you will meet with errors when changing the codes.

2.      Using VBA codes will make things more complicated.

With all the points listed in the above table, you may have already made a choice. Therefore, you can certainly quicken your work pace with those methods.

Software including Excel Still has Shortcomings

Even though technology has developed very fast, there is still no perfect software in the world. Shortcomings still exist in many applications. And Excel is one of those applications. You may have already found many errors when using Excel. Some of them will have no effect in your files, while others may cause severe Excel damage. Therefore, you can prepare a magnificent Excel tool at hand. The next time you meet with Excel errors, you can use this tool immediately and repair xlsx file error quickly.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc document corruption and outlook repair software products. For more information visit www.datanumen.com