Microsoft Office Tutorials and References
In Depth Information
The problem with using number formats is that we can’t adjust them
dynamically. Therefore, we must adjust the data itself.
To adust the power of ten dynamically, set up your data in two
ranges. The first range contains your raw data. The second range
contains the modified data that you chart. Typically, the second
range is modified only with regard to the position of its decimal
To illustrate, suppose your raw data is in a range named RawData.
You could rely on a cell named Scale that has a formula that’s
something like this:
This formula returns .001 if the data should be displayed as
thousands, and .000001 if it should be displayed as millions.
(There’s nothing magic about 800,000; you can use any value you
Formulas in your range of modified data would use this scaling
value to adjust automatically to the scale of your data. For example,
if cell C5 contains raw data, a cell with the adjusted data would
contain a formula like this:
Typically you format the modified data range using the custom
With this setup:
If your raw data is: 1234567.89
Your modified data is: 1.23456789
With formatting, your modified data becomes: 1.2