Excel - format number

Posted on 2011-10-21
Last Modified: 2012-05-12
Do you know how to customize in Excel to format a number in millions.   For example, I want to format (not round) the number 1,234,567 to 1,200,000.  

I don't want the actual number to round or truncate.
Question by:HNA071252
    LVL 22

    Expert Comment

    by:Brian B (TBone2K)
    Unless there is a format that can mask the value, it can't be one value and display another. You may have to hide the column containing the true values and then create another column with the ROUND() function. In you example, it would be =round(a1,-5). There is also roundup and rounddown if you always want it to round up or down, respectively.
    LVL 3

    Expert Comment

    To do millions, you can use a custom format like this:   #,,",000,000"

    Your question asks millions, but your example displays 100,000s.  Not sure how to do that.

    LVL 22

    Accepted Solution

    Do you always want to round down? If so, this format will display the value as millions, although I'm not sure if this is useful.


    Will display 1234567 as 1.2.

    Like I said, maybe not the exact answer, but hopefully gets you started. You can use the request attnetion link if you want to get other expert's opinions.

    Author Comment

    I still not able to display 1,200,000 from 1,234,567

    How do I request attention to this question?
    LVL 3

    Assisted Solution

    I've scoured and tested for solutions to your question.

    I believe now it is not possible to do.  It seems the best you can do is limit display by sections  i.e.,

    Open in new window

     will turn 1234567 into '1', but

    Open in new window

     will display '1235',

    Open in new window

    will display '1,235' and

    Open in new window

    will display the whole number.  Any placeholder, 0,#,? will have the same effect.  Further, there is no pattern in formatting for changing significant digits to zeros.  For that, it seems, you must round.  (plenty of left-of-highest-digit and right-of-decimal padding options, though)

    Here is one of many links to formatting syntax:

    If you can explain your problem in terms of what your requirement is, (i.e., client provides x, wants to see y, can't change z) rather than asking about how to implement your chosen solution, perhaps we can figure out another way?

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now