Why do Excel values in parentheses become negative values?

Profile picture for Bongo
Bongo

A colleague and I encountered a behavior in Excel which isn't clear to us.

Background: We have a tool which converts an Excel sheet into a table format. The tool calculates the formulas which are in excel and replaces variables inside it with specific values. The excel tool is used by one of our customers who use values like (8) or (247). These Value are automatically translated by excel to -8 or -247.

Question: I saw that many people want to display negative numbers in parentheses. But why would Excel change values in parentheses to a negative number?

I know that I could simply change the cell config to text and this would solve the problem but I wonder if there is a reason for the behavior, since there seems to be no mathematical reason for this.

To answer the why, it's because accountants put negative numbers in brackets for readability

Unfortunately, this is one of the excel feature/bugs that helps some folks and frustrates others. When opening a file or pasting content, excel will immediately and always try to parse any values into formats it deems appropriate, which can mess up data like:

  • Zip Codes / Tel. # → Numeric: 05401 → 5401
  • Fractions → Dates: 11/20 → Nov, 20th YYYY
  • Std. Errors → Negative Numbers: (0.1) → -0.1

For some workarounds , see Stop Excel from automatically converting certain text values to dates

Once the file is open/pasted, the damage is already done. At that point, your best bet is:

  • Updating the field and displaying as text (appending with ') to prevent re-casting
  • Formatting the field if the operation wasn't lossy and is just presenting the info differently
  • Running a clean if/else to pad or other convert your data based on the identified errors

Specific to displaying values back in parens, if excel is converting them and treating them like negative numbers (which may or may not be the appropriate way to actually store the data), you can apply a different format to positive and negative numbers to wrap back in parens.

Format Cells