Microsoft Excel displays negative numbers with a leading minus sign by default. It is good practice to make negative numbers easy to identify and, if you are not satisfied with this default, Excel provides a few different options for formatting negative numbers.
Excel provides a couple of built-in ways to display negative numbers and you can also configure a custom format. Let's dive.
Change to a different embedded negative number option
One thing to pay attention here is that Excel will show different built-in options depending on the region and language settings in your operating system.
For those in the USA. UU., Excel provides the following built-in options to display negative numbers:
- In black, preceded by a minus sign
- In red
- In brackets (you can select red or black)
In the UK and many other European countries, you will regularly be able to determine negative numbers to be displayed in black or red and with or without a minus sign (in both colors), but has no option for parentheses. You can learn more about these locales at Microsoft website.
Despite this, no matter where you are, you can add additional options by customizing the number format, that we will cover in the next section.
To switch to a different embedded format, right click on a cell (or a range of selected cells) and then click on the command “Cell format”. You can also press Ctrl + 1.
In the Format Cells window, switch to tab “Number”. On the left, choose the category “Number”. On the right, choose an option from the list “Negative numbers” and then press “To accept”.
Please note that the next image shows the alternatives you will see in the US.. UU. We will talk about creating your own custom formats in the next section., so no problem if what you want is not shown.
Here, we have chosen to show negative values in red in parentheses.
This screen is much more identifiable than Excel's default.
Create a custom negative number format
You can also create your own number formats in Excel. This gives you maximum control over how the data is displayed.
Start by right clicking on a cell (or range of selected cells) and then click on the command “Cell format”. You can also press Ctrl + 1.
In the tab “Number”, select category “Personalized” on the left.
You will see a list of different custom formats on the right. This may seem a bit confusing at first, but there is nothing to fear.
Each custom format is divided into up to four sections, with each section separated by a semicolon.
The first section is for positive values, the second for negatives, the third for zero values and the last section for text. It is not necessary to have all sections in one format.
As an example, Let's create a negative number format that includes all of the following.
- In blue
- In brackets
- No decimals
In the Type box, enter the code below.
#,##0;[Blue](#,##0)
Each symbol has a meaning and, in this format, the # represents the display of a significant digit and the 0 is the display of an insignificant digit. This negative number is in parentheses and is also shown in blue. There are 57 different colors that you can specify by name or number in a custom number format rule. Remember that the semicolon separates the display of positive and negative numbers.
And here is our result:
Custom formatting is a useful Excel skill. You can take the formatting beyond the standard settings provided in Excel which may not be sufficient for your needs.. Formatting negative numbers is one of the most common uses of this tool.
