multiple rules in format code

%3CLINGO-SUB%20id%3D%22lingo-sub-2982054%22%20slang%3D%22en-US%22%3Emultiple%20rules%20in%20format%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2982054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ELet's%20say%20I'm%20creating%20a%20scatter%20plot%20chart.%20So%2C%20I've%20entered%20in%20x%20and%20y%20values%20in%20the%20cells.%20I%20go%20to%20Insert%20chart%2C%20etc.%2C%20and%20get%20the%20chart%20displaying%20just%20as%20I%20want%20it.%20Then%2C%20I%20notice%20there's%20no%200%20displaying%20in%20either%20the%20x-axis%20or%20y-axis%20labels%20(it's%20just%20blank%20for%20that%20label).%20So%2C%20I%20go%20to%20-%26gt%3B%20Format%20axis%20-%26gt%3B%20Axis%20options%20-%26gt%3B%20Number%20-%26gt%3B%20Category%3A%20Custom%3B%20Format%20Code.%20I%20know%20which%20three%20rules%20I%20need%20in%20order%20to%20get%20the%20labels%20to%20display%20as%20I%20want%20them.%20The%20problem%20is%20it%20seems%20you%20can%20only%20create%20a%20code%20with%20a%20maximum%20of%20two%20rules.%20Do%20you%20know%20how%20to%20cram%20three%20rules%20(separated%20by%20semi-colons)%20into%20a%20single%20format%20code%3F%20The%20only%20remedy%20I%20found%20was%20to%20create%20a%20%22new%20rule%22%20in%20Home-%26gt%3BConditional%20Formatting%2C%20but%20that%20didn't%20work.%20If%20I%20try%20to%20create%20a%20format%20code%20with%20three%20rules%20and%20add%20it%2C%20it%20just%20chops%20off%20the%20third%20rule.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2982054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2982269%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20rules%20in%20format%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2982269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1223142%22%20target%3D%22_blank%22%3E%40calvinsaxon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20option%20is%20to%20use%200%2C%22K%22%3C%2FP%3E%0A%3CP%3EApart%20from%20that%2C%20I%20have%20no%20problem%20creating%20three%20sections%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0897.png%22%20style%3D%22width%3A%20240px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328066i892EA56637043A3F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0897.png%22%20alt%3D%22S0897.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOr%20am%20I%20missing%20the%20point%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2982390%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20rules%20in%20format%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2982390%22%20slang%3D%22en-US%22%3EYeah%2C%20if%20I%20enter%20that%20(three%20rules%20separated%20by%20two%20semicolons)%2C%20it%20will%20only%20add%20a%20code%20with%20the%20first%20two%20rules%2C%20so%20thanks%20for%20the%20reply%20but%2C%20as%20always%2C%20%22works%20for%20me%22%20isn't%20a%20huge%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2982448%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20rules%20in%20format%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2982448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1223142%22%20target%3D%22_blank%22%3E%40calvinsaxon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20understand%20that%20that%20isn't%20helpful%2C%20my%20apologies.%20What%20about%20my%20other%20suggestion%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20what%20do%20you%20see%20when%20you%20open%20the%20attached%20version%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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