# Thread: Formatting Numbers so 1,600,000 Displayed as 1.6

1. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513

## Unanswered: Formatting Numbers so 1,600,000 Displayed as 1.6

I would like to know if there is a way to format cells, which have numbers exceeding 1 million, so that, for example, 1,600,000 is displayed in a cell as 1.6. The numbers will be used in a graph and I do not want to change their values by dividing by 1 million to get the display effect I am looking for.
Thanks.
Jerry

2. Registered User
Join Date
Dec 2003
Location
Posts
72
Hi

Why not divide by 1,000,000 within your formula with format number and one decimal?

3. Registered User
Join Date
Oct 2003
Posts
1,091
Howdy, Jerry. You can set this up systematically, following Pierre's lead.

Say your data is in E10:Q15, then leave that data alone, and in E20:Q25, put the formula in E20:

=E10*Scale

and copy to the entire range, E20:Q25. Where Scale is a named range (cell) (say in cell B2).

Then build your graph based on E20:Q25.

With this set up, you only need change the value in that cell for all formulas to automatically adjust. And for further refinement, set up a lookup table on the Control worksheet, and you can have the Scale cell a formula based on the MAX value of the data in E10:Q15. That way you can have it adjust according to each worksheet that you have to do, and you don't even have to change he value in the Scale, since it is a formula that automatically changes.

4. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
I thank you both for your responses to the Excel formatting question. I was not aware of this before, but this custom format will accomplish shortening the display of large numbers that I am working with, in the million+ range, without changing the value:
#,##0.0,,_);[Red](#,##0.0,,)
Jerry

#### Posting Permissions

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