By default, Excel will show negative numbers prefixed with a minus sign before the number.
While this is alright in most cases, there are times when you need to show negative numbers in parentheses/brackets.
This is especially true with accounting and financial data in Excel.
In this tutorial, I’m going to show you three simple ways to show negative numbers in parentheses.
I’ll also show you how you can make simple customizations she as making the negative number red in color or showing/hiding the minus sign along with the parenthesis.
Show Negative Numbers in Parenthesis Using the Format Cells Method
Excel has many built-in number formats that can be used based on the requirement.
There is also a formula that allows you to show negative numbers within parentheses.
All we need to do is activate that format.
Below I have the transaction data where some values are positive and some are negative, and I want to show the negative values within brackets/parenthesis:
Below are the steps to show negative numbers in parentheses by changing the format of the cells.
- Select the range of cells that contain the positive and negative numbers.
- Right-click and scroll down to “Format Cells…”
Protip: Alternatively, should you want a shortcut to Format Cells… you can press CTRL + 1 (CMD + 1 on Mac) to open the Format Cells menu directly.
- In the Format Cells dialog box that opens, click on the Number option.
You will then see a place to select the decimal places that you want to display if you want to use a comma separator for numbers in the thousands and finally the Negative numbers selector.
If you want to show the negative number in parenthesis, select the third option, if you want the negative number in parenthesis in red you will select the fourth option.
Click OK to apply the format to the selected cells.
In the cells that you just formatted, you will now be able to enter negative numbers and since you have already created the format for them, they will appear in that format.
By formatting them ahead of time, you will save time by not having to format them one by one.
Note: When you specify the formatting for negative numbers it would not affect the positive numbers or text values for your data set, it will only impact how negative numbers are displayed. Positive numbers will still appear in the format that they are already displayed.
Also read: Accounting Format Shortcut in Excel
Show Negative Numbers in Brackets Using Custom Formatting Method
In the previous method, I showed you how to use a built-in format to show negative numbers in parentheses in Excel.
While that will suffice in most cases, there may be times in which you will need to implement more control over how the data is displayed.
With this method, you can create your own custom format.
With your own custom format, you can control how positive and negative numbers appear and assign values to these.
For example, you may want to show negative numbers in brown and positive numbers in green.
As with the previous method, we will be starting off with a generic data set that will show a beginning balance and types of debits and credits that may occur in the course of your business or personal life:
- From here you will want to highlight the cell(s) that you want to format to show that there is a negative number or that there is a positive number. Right-click and scroll down to “Format Cells…” or as indicated earlier you can press CTRL + 1 (or CMD + 1 on Mac) and bring up the Format Cells dialog box. Here you will want to click on Custom
- Once you click on Custom you will then enter #,##0;(#,##0) or if you want a color to appear you will enter #,##0,[Selected Color],(#,##0). Conversely, you can also color the positive number in your range of cells with [Selected Color]$#,##0.00;[Selected Color]-$#,##0.00;$0.00.
Explanation of the custom code: What this does is it selects from the four available formats for numbers in Excel (positive numbers, negative numbers, 0, and text).
Let’s break this code down.
- The first set is showing positive numbers. This is indicated by the first #,##0. This is showing that the number is to use comma formatting and not have any decimal places. If you need decimal places add them like this #,##0.00. This would give two decimal places.
- The second set is showing negative numbers. This is indicated by the (#,##0) after the semi-colon. This will cause the numbers to show inside the parentheses in your Excel document.
Formatting Negative Numbers in Parenthesis – Conditional Formatting Method
There is one more method that we would like to cover and that is the conditional formatting method of having your negative numbers appear in parenthesis in Excel.
As with the previous methods, you will start with a data set.
- Your first step is to locate conditional formatting. This is located on the home tab in the styles section.
- Once you have located this, highlight, and select the cell or range of cells that you want to format.
- Click on Conditional Formatting, Highlight Cell Rules, and then Less Than
- You will then get the Less Than Menu. It will select your cell (or range of cells) in the left box and in the right box after the word with it will be set to “Light Red Fill with Dark Red Text” by default. You will want to click on the down arrow and scroll down to “Custom Format…”
- You will then be presented with the Format Cells where you will be given the opportunity to select the format that you want for either number, currency or custom as we covered previously.
Other articles you may also like: