Unable to format negative values in excel using parentheses

Question

I recently lost the ability to format negative values in excel using parentheses. (format setting:  number, currency, etc)

After doing some research, it appeared that this might be due to the "region" settings of my computer, but I verified that it is set to US.

I recently upgraded to Yosemite and believe this is related to the issue.

anyone have any guidance to fix this issue?

thanks

Answer 1

May I ask what type of system screen shot was taken from? If you are on a Mac running OS X 10.10.x (Yosemite upgrade), your number format options would be as follows ... Note that the Accounting format does not place parentheses around the negative number. See "Sample" on the screen. 

I tried to replicate your example, but under "Custom ... ", parentheses around negative numbers were not offered as an option, as pictured in your response. To check whether this anomaly just affected my THREE Macs, I traveled to the Apple store and tried to format a negative number with the Accounting format. Sadly, the negative number was formatted with a minus sign out front, instead of surrounded by parentheses as it should have been.

I recently spent some time with a Microsoft Office for Mac team engineer, and at first he could not replicate the anomaly. When I inquired as to what OS he was using, he responded he was on a windows PC. Windows PC? Troubleshooting a Mac problem? I noted he was a member of the Office for Mac team, and shouldn't he be working on a Mac? So he found a Mac, and sure enough he suffered the same problem.

Microsoft issued an update to Microsoft Office for Mac 2011, which I installed, shortly after the Yosemite OS was released, to insure the Office for Mac programs operated as expected under Yosemite. Unfortunately, they Accounting format was lost in the transition. It has been a known problem since October 2014. Microsoft has yet to remedy the problem.

I can only conclude Microsoft is not interested in correcting this, which is unfortunate since most Excel users use the program in the financial area where the Accounting format is necessary. I cannot believe it is a difficult correction to implement. It shows a callous disregard for its users by Microsoft. I will not be upgrading the Microsoft product beyond what I have now.

Answer 2

Hi

Wow - there's a lot in your answer! I'll try to get through it all.

When I wrote my response I was using a developer build of Mac OS X 10.11 aka El Capitan. At the moment I am using version 10.10.5. Trying the same steps in 10.10.5 I am able to make a custom number format that displays parentheses. This time I made it display two decimal points:

To make a Custom Number Format choose Custom on the left. Use the Type input box above the list of custom formats. Simply type the characters as shown here, then press the OK button. Once you have added a custom type, it is always available in the workbook. 

So Microsoft actually had a Mac for support people to use? Amazing! I congratulate you on getting a Microsoft support person to actually try out their own advice on Office for Mac. 

As for custom number formats, the parentheses problem is a low priority bug. In fact, fixing anything that's screwed up in Office 2011 is pretty much off the table. Web apps, phone apps and a little bit of attention to Office 2016 is about all we can reasonably expect. Since there is an easy work-around, I doubt this will ever be addressed in a fix for Excel 2011.

Here's the actual Accounting format modified to show parentheses for negative dollar values. I didn't fiddle with Euro or other variations. 

Answer 3

Thank you, Jim Gordon Mac MVP.

BUT, of course, the Excel user can always enter their own custom number format manually through the Custom format option, but it is time-consuming and each time you want to apply it to another cell, you have to enter the Custom option on the Number format dialog box and find and select it, or use the Format copy-paste tool. These are just work-arounds.

What I want to be able to do is to quickly apply the Accounting or Numerical (with parentheses) formats right from the ribbon menu, which Microsoft used to provide. The menu items still reflect those options, but they are not functioning.

Finally, you miss my point ... I don't require that Office for Mac 2011 be repaired. But I certainly expected the functionality to be restored in Office for Mac 2016 ... and it's NOT.

Answer 4

You can open a new, blank workbook, add the custom format, then save the workbook as the default workbook so that the format is available to all new workbooks you make.

Answer 5

One of the first things I tried to do ...