Solved

# Excel - format number

Posted on 2011-10-21
Medium Priority
421 Views
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.
0
Question by:HNA071252
• 2
• 2
• 2

LVL 25

Expert Comment

ID: 37008790
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.
0

LVL 3

Expert Comment

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

Dave
0

LVL 25

Accepted Solution

Brian B earned 750 total points
ID: 37024445
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.

0.0,,

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.
0

Author Comment

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

How do I request attention to this question?
0

LVL 3

Assisted Solution

varontron earned 750 total points
ID: 37135691
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.,
``````0,,
``````
will turn 1234567 into '1', but
``````0,
``````
will display '1235',
``````0,0,
``````
will display '1,235' and
``````0,0
``````
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:  http://www.ozgrid.com/Excel/CustomFormats.htm

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?
0

Author Closing Comment

ID: 37210282
Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month16 days, 17 hours left to enroll