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.
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.
- Select the target area in the worksheet. In this example, we will select range C3:C13.
- After that, right click the selected area.
- And then in the menu, choose the option “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
- Now in the “Format Cells” window, click the “Number” in the “Category”.
- And then choose a type for the negative number. There are four options available. Here we choose the second one.
- In this step, change the “Decimal places” if you need.
- After that, click the button “OK”. Thus, you can see the negative numbers are all in red.
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.
- Still in the “Format Cells” window, choose the option “Custom”.
- And then input this new type into the text box:
The positive number will remain the same, while the negative number will be marked red.
- Next click the button “OK”. Therefore, you will now get the new result in the worksheet.
In addition, if you don’t want to show the “-“ in cells, you can change the type into this:
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.
- Select the target area in the worksheet.
- Next click the button “Conditional Formatting” in the toolbar.
- And then move your cursor onto the option “Highlight Cells Rules”.
- Next, choose the option “Less Than” in the sub menu.
- In the “Less Than” dialog, input the number “0” into the text box.
- And click the small arrow in the format text box.
- In the drop-down list, choose the option “Red Text”.
Of course you can also choose other format or custom format for those negative numbers.
- When you finish the setting, click “OK” in the window.
The negative numbers will also be red in the range.
Except for using the predefined condition rules, you can also create new rules.
- Still select the target area.
- And then click the button “Conditional Formatting”.
- After that, choose the “New Rule” in the drop-down list.
- And now choose the last option in the new pop-up window.
- Next input this formula into the text box.
- After that, click the button “Format”.
- 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.
- Next click “OK”.
- And then continuing “OK” in the “New Formatting Rule” window.
Thus, you can also make negative numbers red according to the above steps.
Method 3: Use VBA Process
In using the last method, you should use VBA process in Excel.
- Press the shortcut keys “Alt +F11” on the keyboard. And now you have activated the Visual Basic editor.
- And then click the button “Insert” in the toolbar.
- After that, choose the option “Module” in the sub menu. Thus, you have inserted a new module into the worksheet.
- Now input the following VBA codes into the module:
Dim cel As Range
For Each cel In Range(“C2:C13”)
If cel.Value < 0 Then
cel.Font.Color = vbRed
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.
- 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.
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
After you run the sub, the minus sign will disappear in the worksheet.
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.
Change Number Format
Use VBA Process
|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.
|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.
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