Placing parentheses for negative numbers is a common exercise popularly used by accountants to increase readability. Nowadays, it is also displayed in red color. While working with Excel, you might need to accomplish this necessary task. In this instructive session, I’ll discuss 3 methods on how to put parentheses in Excel for negative numbers with proper explanation. Besides, I’ll show the process of displaying the red color with the parentheses for such types of numbers.
Download Practice Workbook
3 Methods to Put Parentheses for Negative Numbers in Excel
Let’s introduce the following dataset where Purchase Price and Selling Price are provided in USD for each Product. If you subtract the Selling Price from the Purchase Price (=E5-D5 for the F5 cell), you’ll get the positive value in the case of Profit. Surely, a negative value will be found if there is a Loss.
Now, you need to put parentheses for the negative values to express them with clarity in a standard way.
Let’s learn the methods.
1. Use the Format Cells to Add Parentheses Only
You’ll see in the beginning how to add parentheses only utilizing the Number Format from the widely used Format Cells option.
To execute the task, firstly you have to select the number values (e.g. F5:F12 cell range). Then you need to open the Format Cells option. Luckily, there are plenty of options for doing that.
- For example, you may use the keyboard shortcut to open the option (just press CTRL + 1).
- If you right-click, you’ll see options and choose the Format Cells option from the Context Menu.
- Besides, you may click on the arrow of the Number Format option from the Home tab to go to the option directly.
After doing that, you’ll get the following dialog box.
Now, choose the (1.234) format from the Negative numbers option under Number category.
If you press OK, you’ll get the following output where the negative numbers are displaying with the parentheses.
Note: If you’re a Microsoft 365 user, probably you’ll see the negative numbers with parenthesis by default as shown in the following image.
Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)
2. Set Parentheses with Negative Sign
But if you want to keep the negative or minus sign (–) as well as keep the parentheses also, you need to adopt this method.
Firstly, go to the Format Cells after selecting as shown in the previous method.
Then, choose the Custom option from the Category. Subsequently, pick the format code #,##0_);(#.##0) and insert the minus sign. So, the format code will be as follows-
#,##0_);(-#.##0)
Here, two format codes are combined mainly. The first one refers to positive values whereas the second one is for negative values with parentheses. When you include the minus sign, the second format code will add the sign as well as keep the parentheses for your negative values.
Shortly, you’ll get the following output.
Read More: Excel Custom Number Format Multiple Conditions
Similar Readings
- How to Format Number to Millions in Excel (6 Ways)
- [Fixed!] Excel Not Recognizing Numbers in Cells (3 Techniques)
- How to Change Number Format from Comma to Dot in Excel (5 Ways)
- How to Apply Number Format in Millions with Comma in Excel (5 Ways)
- Excel Number Stored As Text [4 Fixes]
3. Show Red Color with Parentheses for Negative Numbers
Lastly, you may display red color with parentheses for negative numbers as it is extensively used presently.
To highlight the negative number with the defined color, you need to pick the following format code from the Custom category.
#,##0_);[Red](#,##0)
Here, the [Red] displays the negative numbers in red font color.
So, the output will look as follows.
Note: In the examples of the first two methods, I specified the purple color manually. But in the above picture, it has been done using the above format code only.
However, if you want to show a negative sign along with the red color and parentheses, you need to insert the sign after [Red] as depicted in the following screenshot.
Eventually, you’ll get your desired output.
Thus you may easily put parentheses in Excel for negative numbers. Also, you can remove the parentheses if you want.
Read More: How to Format Number with VBA in Excel (3 Methods)
Negative Numbers Aren’t Showing with Parentheses in Excel
While placing the parentheses in Excel for negative numbers, you might get an error even if you tried all the methods. Most likely, it’s the problem with the operating system (OS) that you are using. If you are a macOS user, just update the OS. But go to the Control Panel and click on the Change date, time or number formats under the Clock and Region settings if you are a Windows OS user.
Then, click on the Additional settings from the Formats tab.
Immediately, you’ll get the following dialog box namely Customize Format. Next, click on the drop-down list (located on the right side of the Negative number format option). And choose (1.1) as the format.
If you turn on the format by clicking over the Apply option, I think your issue will be fixed and you’ll get your expected output.
Conclusion
That’s the end of today’s session. From the beginning to the end, I discussed the ways to put parentheses in Excel for negative numbers. I also mentioned the related issues with it. So, I hope this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section below.
This procedure for displaying negative numbers in parentheses did not work for me. I can use the ‘Format cell’ routine for individual spreadsheets with no problem but I could not get the ‘Control Panel’ procedure to change the environment globally: each new spreadsheet reverts to displaying the minus sign.
I’m using Excel 2013 under Windows 10 which I keep routinely updated.
Dear William Moloney,
As far as I understand, you are able to use “Format Cell” but can’t get the procedure of using “Control Panel” to fix negative number format, right?
You are using Windows 10 and you can do it easily in Windows 10.
Firstly, go to “Control Panel”.
Secondly, click on “Change date, time and number format” in the option “Clock and Region”.
Thirdly, a window named “Region” will appear. Go to “Formats” option of that window. Click on “Additional settings” at the right-bottom side of the window.
You will see a “Customize Format” window. Go to “Numbers” of this window. You’ll see many options available in this “Numbers” option.
Fourthly, in the “Negative Number Format” option, click on the value and you will see different options such as 1.1, -1.1, 1.1- etc. You need to select 1.1 here and then click OK. This is the most important step here to select 1.1. Windows 10 has default selection 0f -1.1, you need to just change it to 1.1.
Hope, your problem will be solved now. Thank you.
Regards,
Towhid
Excel & VBA Content Expert
ExcelDemy
Dear William Moloney,
As far as I understand, you are able to use “Format Cell” but can’t get the procedure of using “Control Panel” to fix negative number format, right?
You are using Windows 10 and you can do it easily in Windows 10.
Firstly, go to “Control Panel”.
Secondly, click on “Change date, time and number format” in the option “Clock and Region”.
Thirdly, a window named “Region” will appear. Go to “Formats” option of that window. Click on “Additional settings” at the right-bottom side of the window.
You will see a “Customize Format” window. Go to “Numbers” of this window. You’ll see many options available in this “Numbers” option.
Fourthly, in the “Negative Number Format” option, click on the value and you will see different options such as 1.1, -1.1, 1.1- etc. You need to select 1.1 here and then click OK. This is the most important step here to select 1.1. Windows 10 has default selection 0f -1.1, you need to just change it to 1.1.
Hope, your problem will be solved now. Thank you.
Regards,
Towhid
Excel & VBA Content Expert
ExcelDemy