Excel: Custom number formatting

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?

Solution 1:

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

Solution 2:

Number formats can have at most 4 sections separated by ; (semicolon)

They are POSITIVE;NEGATIVE;ZERO;TEXT, respectively.

If the number is positive, first format is applied,  
else if negative the second,  
else if 0 the third,  
else it's considered as text and the last formatting is applied. 

You don't have to specify all four of them.
If only 1 format is specified it's used for every number.
If only 2 formats are specified, first part is used for positive and zeros, and second part is used for negatives.

If you want to skip some part(s) just use a semicolon for each and define the next part(s) following it(them).

Obviously, your formatting has all 4 sections.

_-* #.##0,00 TL_- for example this is for positives. 

Since your Excel uses Turkish formatting

. (dot) is used as thousands separator and 
, (comma) is used for percentages.  

So if someone wrote 10000 1/2 your formatting will show it as 10.000,50 TL i.e. Ten thousand Turkish Liras and 50 Kuruş.

Other format specifiers in your code have the following meanings:

# Digit placeholder, skips insignificant 0s  
0 Digit placeholder, adds extra insignificant 0s to fill the format  
? Digit placeholder, adds spaces for insignificant 0s to align decimals  
@ Text placeholder  
_ Skips the width of the next character, generally used to align positive numbers with the negatives when negative numbers use a - (minus sign) or parentheses. 
* Repeats the next character to fill the column width

Therefore POSITIVE _-* #.##0,00 TL_-; part of your code can be read as:

• _- skip the width of minus sign
• *(space) repeat blanks until column is filled
• #.##0,00 TL use . as thousands separator, and , as percentage separator, remove them if any 0z are written before the first digit. After the comma take only two digits, remove if more than two used, fill them with 0s if less than two digits used, finally add TL and once again skip the width of -.

Your code already displays - before the negative digits, so if you want to add + before the positive values just add + before the first # in positive part of your code.

Solution 3:

The official excel how-to article is actually quite good and contains several examples. The article has solutions for about a dozen good cases where you would want to use number formats.