same month/day on x axis for multiple years

  • Thread starter Thread starter mjo73
  • Start date Start date
M

mjo73

If i have data sourced to me like this:

Date Price 1 Price 2 Price 3
1/1/2003 2.5 3.2 3.0
1/2/2003 2.6 2.8 2.9
1/1/2004 2.7 3.0 3.1
1/2/2004 2.0 2.8 3.2
1/1/2005 6.0 6.2 6.2
1/2/2005 6.5 6.3 6.4


If I want a line graph with the 3 series related to the Price columns,
how can I get the x axis to only reflect the 3 month/day combinations
not including the year? When I try this it gives me 6 x axis points
for each series.

Thanks, Mike.
 
You can do this with a pivot table. Select a cell in the data table, and
choose PivotTable and PivotChart Report from the Data menu. Drag the Date
field into the Rows area, and the three Price fields into the Data area.
Drag the Data field in the new pivot table to the Columns area (where it
just says "Total"). Then right click on one of the dates, and choose Group
and Show Detail, and choose Group in the pop up. In the Grouping dialog,
unselect Months, then select Days and Years. Finally drag the Years field to
the Columns area. You get a table that looks like this (it extends further
to the right):

Years Data

2003 2004
....

Date Sum of Price 1 Sum of Price 2 Sum of Price 3 Sum of Price 1 Sum
of Price 2 ...

1-Jan 2.5 3.2 3 2.7 3
....

2-Jan 2.6 2.8 2.9 2 2.8
....

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