Have you ever wondered how can we show the negative numbers into the bracket and in red color? You can show the negative numbers into the bracket with text in red color using the custom cell formatting option.
Before starting with this blog, let me explain what this technique would help you to achieve with the help of a small example.
Suppose you have a list of numbers in Excel (as shown in the screenshot below).
As you can see that some of the numbers are positive and others are negative (with a minus sign before it). Also, all the numbers (whether positive or negative) as in black color.
Now you want to change the formatting of the numbers such that the positive numbers should remain as it is (i.e. in black color), but the negative number should be in the bracket and the font color should change to red.
The below screenshot shows the sample numbers and its result (after changing the formatting).
So how can you achieve this?
Someone may think that this can be done using the Cell Number Formatting option of Excel. But it is pertinent to note that the Excel Cell Number Formatting can change the negative number into red color, but will not insert the numbers into brackets.
Refer to the screenshot below:
Let us now unlock the technique to show the negative numbers into a bracket and red color.
Show Negative Numbers in Bracket and in Red Color
Select the cells which contain that list of the numbers as shown in the screenshot below :
Go to the “Home” Tab. In the “Number” group, click on the “Format Cell” dialog box launcher. You can find the dialog box launcher as a small square (with an arrow inside it) on the bottom right of the “Number” group
Refer to the screenshot below
The “Format Cells” dialog box would open as shown in the picture below
In the “Category” section of this dialog box, click on the option “Custom”. You would notice that on the right side of this dialog box, the “Custom” formatting options are available as shown in the screenshot below
You would notice that there is one input box under the section “Type”. Enter the following text in the input box
And then press the “OK” button
Finally, you can see that the positive numbers remain as it is (in black color) and the negative numbers are in brackets with red color
Explanation of the functionality :
There are four parts of this text: #,###;[Red](#,###);;General. As you can see, Semicolon (;) separates each of these parts.
The first part denotes the formatting of a positive number in the cell. In the present case, we have given the formatting to a positive number as #,### (number with thousand separator).
The second section denotes the formatting of a negative number. In the present case, we have given formatting to a negative number as [Red](#,###) i.e. it should in the bracket and in red color.
The third part denotes the formatting of zero (0) in the cell
The fourth and the last section denotes the formatting of the text (Characters/words). We have given it “General”.
This brings us to the end of this blog.