I've been reading around and from what I can tell, what you ask is impossible. Conditional formatting is limited to two conditional statements (source,source).
That doesn't mean you can't have this display the way you want, but will take a more complicated route, which requires that you put the following in another column.
=CONCATENATE(IF(A1<0,"-$","$"),IF(ABS(A1)>=1000000,FIXED(ABS(A1)/1000000,1),IF(ABS(A1)>=1000,ABS(A1)/1000,ABS(A1))),IF(ABS(A1)>=1000000,"M",IF(ABS(A1)>=1000,"K","")))
More visually pleasing version (this is all strung together in a single cell, no carrige returns)
=CONCATENATE(IF(A1<0,"-$","$"),
IF(ABS(A1)>=1000000,FIXED(ABS(A1)/1000000,1),
IF(ABS(A1)>=1000,ABS(A1)/1000,ABS(A1))),
IF(ABS(A1)>=1000000,"M",IF(ABS(A1)>=1000,"K","")))
This blob of a formula does what it seems you want your conditional format to do. It assumes that the value you want to "format" is in cell A1. You can adjust the reference as needed to fit your spreadsheet.
I realize that this method is not solving the problem in the way you want. It also may not be usable depending on the reasons why your can't use conditional formatting. Without knowing the full details, I really can't know if this will work for you or not. If you are worried about duplicate data being viewed, you can always hide a column.
[EDIT]
Charts are different because you have to use macros to get it to do the really wild stuff. I ran across this page with some useful information on how to set stuff up. Looking over the "Arbitrary Axis Scale" page, it mentions that a third party has a free excel add-on that might do the trick. I haven't tested it, but it seems like it should allow you to change your axis scales to show the correct value, assuming you are using a line graph.
If you are using a bar, pie, or specialized graph. You'll probably have to turn the graph into a picture and then add the custom labels as needed. Not a path I would go down, but may be your only option.
[EDIT]
I hope this helps.
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
Best Answer
You want a custom number format:
This defines the format for positives
"("0.00")"
and another for negatives"("-0.00")"
You could optionally provide a third format for zero value but if you don't specify it then zero will be formatted using the positive format.