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%;###%.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,367
Office Version
2019, 2016, 2013
Platform
Windows
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
6,348
Office Version
365
Platform
Windows
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,368
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
6,348
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,336
Messages
5,486,250
Members
407,538
Latest member
kbendelac

This Week's Hot Topics

Top