cell formatmicrosoft excel
NOTE. Not to be confused with Conditional Formatting
When using the Custom Numbers in Excel I often use RED font for a negative number.
See image below.
What other Colours can be used in this way ?
I know Green & Blue work, but is there a full list available ?
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.
Use this Custom format:
General;"!"General;0
It will change the negative to !
Best Answer
In addition to the 8 named colors you can also use [ColorX], where X is a number between 1 and 56. The named colors are identical to [Color1] to [Color8].
A quick way to see all the colors provided is to run the following in the immediate window in VBA (preferably in an empty workbook):