Excel: Custom number formatting

cellsmicrosoft excelmicrosoft-excel-2007

I don't know much about the rules on custom number formatting in Excel, and I need to learn more. When I open the related window (Format Cells > Numbers > Custom), there are some predefined formats consisting of various symbols. For example:

_-* #.##0,00 TL_-;-* #.##0,00 TL_-;_-* "-"?? TL_-;_-@_-

What does each of these symbols mean?

There is also a specific issue I want to achieve. I want to put a plus sign in front of a number if it is positive, and minus sign if it is negative. How can I do that by custom number formatting?

Best Answer

the semicolons are delimiters for each specific criteria - the first is for postive numbers, the second for negative number, the third is for zeroes and the last is for text values.

I want to put a plus sign in front of a number if it is positive, and minus sign if it is negative. How can I do that by custom number formatting?

+?#,###,###,###.00;-?#,###,###,###.00

Should achieve what you're looking for, assuming thousands as group operator.

Each symbol tells Excel what to do

? - Placeholder - means to leave spaces for leading zeroes, but don't display them

0 - Placeholder - means explicitly display leading zeros to match the format

* - Repeat the character next to it

+ - display + sign

- - display the negative sign

You can even add conditions to the above, or add colourrs ( show negatives in red et al) - More documentation is available here

Related Question