Chart with different magnitudes of numbers

[PaulW]

I am trying to make a single chart where the data values go from the hundreds to multi-thousands. The data is by week and I want to put the dates from row 1 into the x-axis legend.

What is the best way to normalize the data. The rows are non-consecutive starting with the dow-jones average to be charted against portfolio values against the S&P etc.

TIA

[HansV]

If the data vary that much in a single data series, you could set the value axis to use a logarithmic scale; this means the steps won't be 1000, 2000, 3000 etc. but 10, 100, 1000, etc.

x575.png

If you have series that differ greatly in magnitude between them, e.g. one series that goes from 200 to 700, and another that goes from 3000 to 6000, you can use a secondary value axis: select a series, and in its properties specify that it uses the secondary value axis:

x576.png

[PaulW]

Hi Hans,
Thanks for the reply. The magnitude of the differences in magnitude make the resulting chart almost a straight line using a logarhythmic scale and therefore not very helpful. I did try the secondary axis method you proposed, but that did not help much either. I am using my own homegrown average of values 2-21 divided into value 1 as a normalization function. Not cool, but seems to work.

I am using Excel 2010. How do I get the x-axis to be a data field (dates) and not numbers 1-20 or thereabouts.

Thanks again

[HansV]

For column, area and line charts, you can specify that the x-axis is a date axis:

x577.png

For scatter charts, this option is not available, but you can format the x-axis labels as dates in the Number section of the Format Axis dialog:

x578.png

[PaulW]

Hi Hans,

Thank you for the reply. Please bear with me. I did set the x-axis as a date field with the format I desired, but cannot figure out how to get the data points from the data. If I go to select data, I get only the date data in the chart. I wish to use the data only as x-axis labels. Is this possible in Excel 2010? I have a chart done in Excel 2000 that does what I want, but the coding does not seem to translate.

TIA

[HansV]

Select the chart, then click Select Data in the Design tab of the ribbon:

x579.png

You can specify the range for the x-axis label by clicking Edit... under Horizontal (Category Axis) Labels:

x580.png

[PaulW]

Hans,

As always, you come through! I thought I had done what you had suggested, but maybe it was not in the correct order. Sooooo, I tried it again and . . .