Date in First Column, Time in First Row

[Guest]

I have data correlated to date and time. The dates go down the first column
of the sheet and the times (hours of each day) go across the first row of the
sheet, like so:

01:00 02:00 03:00 ...
01/01/05 1.3 2.2 3.2 ...
01/02/05 5.2 4.2 1.7 ...
01/03/05 2.3 4.1 6.4 ...
..
..
..

How do I make a chart with Date and Time on the x-axis and data on the
y-axis without doing lots of copying and pasting?

 

[Guest]

what kind of chart to you want?
if it is a line chart with contiguous day per hour on the x axis
A pivot table may give you the data format that you want

 

[Guest]

Yes, it's a line chart. I tried the pivot table but it doesn't work well
with data of this type. It sees the columns as the only fields. What I'm
looking for is a way to make a combination of a column and a row as the
field. Does that require special programming in Excel?

 

[Jon Peltier]

So many things are much easier in Excel if only the data could have been
effectively arranged from the start. Here's how to fix yours, in about
three minutes:

Starting data:

date 1:00 2:00 3:00
1/1/2005 1.3 2.2 3.2
1/2/2005 5.2 4.2 1.7
1/3/2005 2.3 4.1 6.4

Pivot table, date in row area, each time in data area:

date Data Total
1/1/2005 Sum of 1:00 1.3
Sum of 2:00 2.2
Sum of 3:00 3.2
1/2/2005 Sum of 1:00 5.2
Sum of 2:00 4.2
Sum of 3:00 1.7
1/3/2005 Sum of 1:00 2.3
Sum of 2:00 4.1
Sum of 3:00 6.4

Copy the table, Edit > Paste Special - Values. Replace "Sum of " with
"", and fill dates down into empty cells:

date time Total
1/1/2005 1:00 1.3
1/1/2005 2:00 2.2
1/1/2005 3:00 3.2
1/2/2005 1:00 5.2
1/2/2005 2:00 4.2
1/2/2005 3:00 1.7
1/3/2005 1:00 2.3
1/3/2005 2:00 4.1
1/3/2005 3:00 6.4

It might be more efficient to add the date and time to get a single
date-time value in an inserted column, before you continue.

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