Completing a Chart

[Guest]

Hello,

Could anyone help me with this problem. I wanted to create chart with a
table that had the months across the top horizontally and the year vertical
on the right. The data was a bunch of price index data. I wanted the chart
to have the year and month on the bottom x-axis, the lowest numerical value
and the highest numerical value on the y-axis. When I would choose chart
type as Line it would ask me to select the data source. I could only get it
to read the horizontal axis label as the months and not the year. How would
I combine the 2? I had to retype all the data like this in order to get it
to work:

Jan-97 49.2
Feb-97 43.5
Mar-97 35.3
Apr-97 25.9
etc....

The data originally read as such:

Year Jan. Feb. Mar. Apr.
1997 49.2 43.5 35.3 25.9
1998 33.3 23.3 22.2 22.1
1999
etc....

Any help would be appreciated.

Thanks,

 

[Jon Peltier]

The table you want to start with has already been pivoted or cross-tabbed
from the original data, which is in the form you retyped the list as.
Starting with the list (a flat database structure), you can easily produce
the chart and using pivot tables or array formulas you can easily produce
the table you began with.

To convert your already cross-tabbed data into a list, select it, go to Data
menu > Pivot Table Report, step through the wizard selecting the defaults,
and Excel will draw an empty Pivot Table frame. Drag the Year field into the
Rows area, and each of the months into the Data area. You will end up with
this pivot table (I've turned off row and column totals):

Year Data Total
1997 Sum of Jan. 49.2
Sum of Feb. 43.5
Sum of Mar. 35.3
Sum of Apr. 25.9
1998 Sum of Jan. 33.3
Sum of Feb. 23.3
Sum of Mar. 22.2
Sum of Apr. 22.1

You could make a pivot chart from this data, or copy the data, paste special
as values elsewhere, delete the cells containing 'Year' and 'Data', select
the range including these empty cells, and make a regular chart.

- Jon

 

[Guest]

You're awesome! Thanks!!!
--
Mung Q

The table you want to start with has already been pivoted or cross-tabbed
from the original data, which is in the form you retyped the list as.
Starting with the list (a flat database structure), you can easily produce
the chart and using pivot tables or array formulas you can easily produce
the table you began with.

To convert your already cross-tabbed data into a list, select it, go to Data
menu > Pivot Table Report, step through the wizard selecting the defaults,
and Excel will draw an empty Pivot Table frame. Drag the Year field into the
Rows area, and each of the months into the Data area. You will end up with
this pivot table (I've turned off row and column totals):

Year Data Total
1997 Sum of Jan. 49.2
Sum of Feb. 43.5
Sum of Mar. 35.3
Sum of Apr. 25.9
1998 Sum of Jan. 33.3
Sum of Feb. 23.3
Sum of Mar. 22.2
Sum of Apr. 22.1

You could make a pivot chart from this data, or copy the data, paste special
as values elsewhere, delete the cells containing 'Year' and 'Data', select
the range including these empty cells, and make a regular chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______