+ Reply to Thread
Results 1 to 4 of 4

Adaptive cell number format, thousands to trillions, K, M, B, T etc

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Adaptive cell number format, thousands to trillions, K, M, B, T etc

    Hello,

    I am trying to use custom formatting for larger numbers when they happen. I am able to get both $1,200 to show $1.2 K, and then if the cell equals $1,200,000 to show $1.2 M. But I can figure out how to add Billions and Trillions etc. Here is the format I thought would work:

    [>=1000000000] $#,##0.0,,," B";[>=1000000] $#,##0.0,," M";[>=1000] $#,##0.0," K";General


    It still properly shows Thousands and Millions, but when the cell is a billion + is shows for example $1,200,000,000 --> $1,200 M.

    Thanks!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Adaptive cell number format, thousands to trillions, K, M, B, T etc

    This will give you billions, millions and thousands:
    [>=1000000000]$#,##0.0,,," B";[>=1000000]$#0.0,," M";$#0.0," K"

    If you want trillions as well, I think you'll need to do some Conditional Formatting or VBA, as I don't think Excel will accept any more conditions in a custom number format.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Adaptive cell number format, thousands to trillions, K, M, B, T etc

    Ah. I was looking to have more than 2 (3 or 4) different break points. Thanks for replying!

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Adaptive cell number format, thousands to trillions, K, M, B, T etc

    Like I say, you can use Conditional Formatting to do it.

    Select your range, then click 'Conditional Formatting' then 'New Rule' then 'Format only cells that contain.' In the rule description, select 'Cell Value' 'greater than or equal to'. Enter 1000, click the 'Format' button and enter this as a custom format: #,##0.0," k"
    Repeat this with the following combinations:
    1000000 #,##0.0,," M"
    1000000000 #,##0.0,,," B"
    1000000000000 #,##0.0,,,," T"
    1000000000000000 #,##0.0,,,,," Qd"
    1000000000000000000 #,##0.0,,,,,," Qn"


    Note that the CF rules must be done in this order (smallest to largest), or you'll find them being applied in the wrong order. If you get them in the wrong order by accident, go to 'Manage Rules' and use the arrow buttons to the right of 'Delete Rule' to re-order them.

    I've attached a file showing this working.
    Edit - I forgot to put in the $ symbol, sorry, but you can just add that in front of the first # in each format.

    Hope that helps.
    Last edited by Aardigspook; 02-12-2017 at 03:29 PM. Reason: Add note

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 04-09-2018, 09:46 PM
  2. Replies: 1
    Last Post: 03-05-2015, 10:05 PM
  3. [SOLVED] Adaptive cell range
    By Trevasaurus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2013, 11:34 AM
  4. [SOLVED] Issue: Custom Number Format to Display (thousands or dashes)
    By Crawfinator1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-25-2013, 03:28 AM
  5. Replies: 1
    Last Post: 07-19-2006, 04:25 PM
  6. [SOLVED] Format to Thousands and x-foot the sum
    By Jeanne in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2005, 07:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1