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

Hi

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

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.

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