Custom number formatting in Excel

jaqcues

New Member
Joined
Jul 16, 2019
Messages
3
Hi there,

Im trying to display some tricky % number formatting and have hit a dead end. Just wondering if it's possible to display % under the following criteria

Over 100%: +xxx%
0-100%: +xx.x%
-100%-0%: [red]-xx.x%
Under -100%:[red]-xxx%

The key is basically to retain the +/-, the black/red and the 3 significant figures throughout. I already have conditional formatting on the cell which uses green/red icons to highlight growth/decline.

Keen on any advice! Ideally this will be possible under number formatting alone; I can achieve the +/- and red easily, or alternatively the 3 sig figs with [=0]"N/A";[<1]##.0%;###%.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,878
not sure it works 100% of the time but in custom format after the plan try "b" and that might add b to the end of your display

"+/-" General "b"
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
How about this custom format

[=0]"N/A";[Red][<0]-##.0%;+###%.

Ignore this - I missed something !!
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
I don't trust my memory on this, but IIRC one is limited to three number ranges. So four as desired isn't possible.

What about just using some nested IF with TEXT to return a string result? So the result would be text & can't be added up. Can maybe handle this using two fields - one numeric & one text. If that makes sense :)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,239
This works

1 apply custom number format as in post#1
Range Number Custom Format:
[=0]"N/A";[<1]##.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format :
[<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font
\ and set Colour selection to: RED

Althouigh not visible in the extract below, my worksheet also shows the Red \ Green CF icons in each cell

Excel 2016 (Windows) 32 bit
E
2
+494%.​
3
-323.3%
4
-322.1%
5
+23.5%​
6
-93.6%
7
-310.5%
8
-257.0%
9
+45.7%​
10
-245.3%
11
+69.1%​
12
+334%.​
13
-196.8%
14
+198%.​
15
-242.0%
16
-160.7%
17
-60.9%
18
+155%.​
19
-56.4%
20
+243%.​
21
-18.4%
Sheet: Sheet1
 
Last edited:

jaqcues

New Member
Joined
Jul 16, 2019
Messages
3
Hi all,

Thanks for your replies!

not sure it works 100% of the time but in custom format after the plan try "b" and that might add b to the end of your display

"+/-" General "b"
:confused:

How about this custom format

[=0]"N/A";[Red][<0]-##.0%;+###%.

Ignore this - I missed something !!
Thanks anyway!


I don't trust my memory on this, but IIRC one is limited to three number ranges. So four as desired isn't possible.

What about just using some nested IF with TEXT to return a string result? So the result would be text & can't be added up. Can maybe handle this using two fields - one numeric & one text. If that makes sense :)
This strategy would work, the only issue is that I have basically made dummy slides which are refreshable each month and linked to Powerpoint, so there are space constraints and also the icon would be a bit off centre (if i were to use the value cell as an icon-only CF).

This works

1 apply custom number format as in post#1
Range Number Custom Format:
[=0]"N/A";[<1]##.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format :
[<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font
\ and set Colour selection to: RED

Althouigh not visible in the extract below, my worksheet also shows the Red \ Green CF icons in each cell

Excel 2016 (Windows) 32 bit
E
2
+494%.​
3
-323.3%
4
-322.1%
5
+23.5%​
6
-93.6%
7
-310.5%
8
-257.0%
9
+45.7%​
10
-245.3%
11
+69.1%​
12
+334%.​
13
-196.8%
14
+198%.​
15
-242.0%
16
-160.7%
17
-60.9%
18
+155%.​
19
-56.4%
20
+243%.​
21
-18.4%

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>
I was half way through typing that this was 99% there, then I tried 1 additional tweak and it is perfect (was just values under 1%, showing as ".x%" instead of "0.x%", mainly personal preference), the difference was in step 1.

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]#0.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font \ and set Colour selection to: RED


Amazing, thanks for your help!
 

jaqcues

New Member
Joined
Jul 16, 2019
Messages
3
Whoops, had to tweak 2nd and 3rd point

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]#0.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+#0.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Number Format \ custom format : [>-1]#0.0%;###%
Format \ Font \ and set Colour selection to: RED


Boomfa
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top