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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
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,365
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,365
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,679
Messages
5,488,226
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top