Suppose I have cell value 0.465 and I want to display it in the spreadsheet in green as +46.5 if it is positive and -46.5 if it is negative. I have applied the following format


but MS Excel changes this format to [Green]+#.000%;[Red]-#.000% and displays the value as +046%. I have tried other formats without success. Any ideas?

  • MS Office Professional 2010 (Excel v14.0.7128)
  • Libre/OpenOffice is not an option.

I have tested this on Excel 2013:


NOTE: actually my excel is in another language with different decimal separator, so it looks like this:


but I assume yours use the dot as decimal.

| improve this answer | |
  • Thanks! You reply actually spotted the problem: in my environment the decimal separator is also , but I was totally misled by pre-cooked build-in custom formats. And yes, quoting signs originally didn't help – I've tried that as well. – dma_k Jan 9 '15 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.