Custom number formatting in Excel
Results 1 to 7 of 7

Thread: Custom number formatting in Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Custom number formatting in Excel

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

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,829
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Custom number formatting in Excel

    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"
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,745
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Custom number formatting in Excel

    How about this custom format

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

    Ignore this - I missed something !!
    Last edited by Yongle; Jul 17th, 2019 at 02:36 AM.

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,149
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Custom number formatting in Excel

    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
    To receive a better answer, put more work into asking the question.


  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,745
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Custom number formatting in Excel

    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 by Yongle; Jul 17th, 2019 at 03:56 AM.

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom number formatting in Excel

    Hi all,

    Thanks for your replies!

    Quote Originally Posted by mole999 View Post
    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"


    Quote Originally Posted by Yongle View Post
    How about this custom format

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

    Ignore this - I missed something !!
    Thanks anyway!


    Quote Originally Posted by Fazza View Post
    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).

    Quote Originally Posted by Yongle View Post
    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
    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!

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Custom number formatting in Excel

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •