Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date in First Column, Time in First Row
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? |
#2
|
|||
|
|||
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 "Jaret Wessel" wrote: 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? |
#3
|
|||
|
|||
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? "bj" wrote: 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 "Jaret Wessel" wrote: 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? |
#4
|
|||
|
|||
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/ _______ Jaret Wessel wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
search for latest date | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Automatically enter date and time but only update once. | New Users to Excel |