multiple rules in format code

New Contributor

Let's say I'm creating a scatter plot chart. So, I've entered in x and y values in the cells. I go to Insert chart, etc., and get the chart displaying just as I want it. Then, I notice there's no 0 displaying in either the x-axis or y-axis labels (it's just blank for that label). So, I go to -> Format axis -> Axis options -> Number -> Category: Custom; Format Code. I know which three rules I need in order to get the labels to display as I want them. The problem is it seems you can only create a code with a maximum of two rules. Do you know how to cram three rules (separated by semi-colons) into a single format code? The only remedy I found was to create a "new rule" in Home->Conditional Formatting, but that didn't work. If I try to create a format code with three rules and add it, it just chops off the third rule. 

5 Replies

@calvinsaxon 

One option is to use 0,"K"

Apart from that, I have no problem creating three sections:

S0897.png

Or am I missing the point?

Yeah, if I enter that (three rules separated by two semicolons), it will only add a code with the first two rules, so thanks for the reply but, as always, "works for me" isn't a huge help

@calvinsaxon 

I understand that that isn't helpful, my apologies. What about my other suggestion?

 

By the way, what do you see when you open the attached version?

@calvinsaxon 

Custom number format doesn't work with more than two conditions, that's by design I guess.

Actually, I take back what I said. Your post was in fact helpful. I wasn't able to put in more than two conditions (as the post below points out), but it turns out I didn't need more than two conditions. Your suggestion helped me figure out what I needed. Here's what I ended up with (I was trying to get just "0" for the zero and a K for the other values): [>999999]#.0,,"M";[>999]#,"K";0