date -and- time on graph axis

CarlB · May 13th, 2002, 02:56 PM

Using Excel '97, when I had a worksheet with columns:

Date , Time , Value_A , Value_B , Value_C

when I created a graph, both time and date would be formatted as the x-axis. I recently had the misfortune of being forced to move to Excel 2000. Now, the same worksheet only uses the Date column, ignoring the time column. Is there a preference to correct this, or will it require a bug fix from Microsoft? If so, what patch is it in?

Asala42 · May 13th, 2002, 03:00 PM

G'day

I actually wasn't aware you could do that in '97.

Can you get away with adding a 3rd column (between time and your 1st data series) and adding adding the 2?

Adam

CarlB · May 13th, 2002, 03:06 PM

Wow ... you're quick with a response!
Actually, I should have pointed out that I even tried glomming the 2 columns into one, containing "dd/mm/yy HH:MM:SS" formatted data. It had the same result.

I liked the feature in Excel '97, and can't seem to get along without it.

Mark W. · May 13th, 2002, 03:08 PM

With the chart selected, choose the Chart | Source Data... menu command. Enter the cell range for your Date and Time values on the Series tab in the "Category (X) axis labels" field.

CarlB · May 13th, 2002, 03:37 PM

I guess I didn't phrase my description of the problem I experienced very well. The data points in the graph itself (whether bars, lines or areas) are all there. The problem is that it is no longer possible to format the axis. Instead, it is a smeared mess of all of the dates and times, jumbled on top of one another. If I try merging the date and time columns into one "date time" column, then the time sees to be ignored, resulting in only one graph point per date.

Asala42 · May 13th, 2002, 03:44 PM

Hey again,

Have you tried using a scatterplot instead of a line chart? That should give you some more freedom in scaling the x-axis.

Adam

Mark W. · May 13th, 2002, 03:46 PM

> The problem is that it is no longer
> possible to format the axis.

I don't follow. Why isn't it possible to format the x-axis? I can!

CarlB · May 13th, 2002, 04:05 PM

Mark and Adam,

Thanks for all your suggestions and guidance! The scatter plot seemed lik a good idea, but there isn't anything like a stacked scatterplot. I stack the data columns.

The problem with the formatting is that I can't set the scale in Excel 2000. It only allows me to modify the Y-axis cross location. The others, number of categories between tick marks and labels are greyed out. I found that if I set Excel 97 to the values that Excel 200 forces me to use, it is messed up just like it is in Excel 2000.

So ... if only Excel 2000 would allow me to set the X-axis scaling, I'd be in good shape!

Asala42 · May 13th, 2002, 04:15 PM

Hey again,

Can you get away with applying an autofilter on your data set, and then using a custom filter on the column that contains your x-axis data.

Adam

Mark W. · May 13th, 2002, 04:33 PM

CarlB, I believe you're missing a "fine" point about Excel chart x-axes. There are actually 3 varieties.

First, there are categorical values which cannot be scaled. If you're creating a Column chart for {"Apples";"Bananas";"Grapes"} the x-axis is categorical. If you change the Chart type to Line it's obviously still categorical. If you subsequently replace {"Apples";"Bananas";"Grapes} with {1;2;3}... guess what... it's still categorical -- even though it looks like it can be scaled.

Second, there are scalar x-axis values like those on a X-Y Scatter chart. These can be {1;2;3} too, but they behave differently -- allowing you to set Minimum/Maximum values on Format Axis Scale tab.

Finally (and, the most recent addition), there's time-scale. Typically, these are used with the Line chart type. These can be scaled too. And, date formatted to suit your needs.

Okay, now here's "pay dirt" for this treatise... If your x-axis label is sourced from 2 or more columns you have a categorical axis. If your x-axis label is sourced for a single column containing date values you have time-scale. Otherwise, a x-axis sourced from single column will be categorical or scalar depending on the chart type and the data type of the values themselves.

[ This Message was edited by: Mark W. on 2002-05-13 16:03 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 02:56 PM	#1
CarlB New Member Join Date: May 2002 Posts: 5	Using Excel '97, when I had a worksheet with columns: Date , Time , Value_A , Value_B , Value_C when I created a graph, both time and date would be formatted as the x-axis. I recently had the misfortune of being forced to move to Excel 2000. Now, the same worksheet only uses the Date column, ignoring the time column. Is there a preference to correct this, or will it require a bug fix from Microsoft? If so, what patch is it in?

May 13th, 2002, 03:00 PM	#2
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	G'day I actually wasn't aware you could do that in '97. Can you get away with adding a 3rd column (between time and your 1st data series) and adding adding the 2? Adam

May 13th, 2002, 03:06 PM	#3
CarlB New Member Join Date: May 2002 Posts: 5	Wow ... you're quick with a response! Actually, I should have pointed out that I even tried glomming the 2 columns into one, containing "dd/mm/yy HH:MM:SS" formatted data. It had the same result. I liked the feature in Excel '97, and can't seem to get along without it.

May 13th, 2002, 03:08 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	With the chart selected, choose the Chart \| Source Data... menu command. Enter the cell range for your Date and Time values on the Series tab in the "Category (X) axis labels" field.

May 13th, 2002, 03:37 PM	#5
CarlB New Member Join Date: May 2002 Posts: 5	I guess I didn't phrase my description of the problem I experienced very well. The data points in the graph itself (whether bars, lines or areas) are all there. The problem is that it is no longer possible to format the axis. Instead, it is a smeared mess of all of the dates and times, jumbled on top of one another. If I try merging the date and time columns into one "date time" column, then the time sees to be ignored, resulting in only one graph point per date.

May 13th, 2002, 03:44 PM	#6
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey again, Have you tried using a scatterplot instead of a line chart? That should give you some more freedom in scaling the x-axis. Adam

May 13th, 2002, 03:46 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	> The problem is that it is no longer > possible to format the axis. I don't follow. Why isn't it possible to format the x-axis? I can!

May 13th, 2002, 04:05 PM	#8
CarlB New Member Join Date: May 2002 Posts: 5	Mark and Adam, Thanks for all your suggestions and guidance! The scatter plot seemed lik a good idea, but there isn't anything like a stacked scatterplot. I stack the data columns. The problem with the formatting is that I can't set the scale in Excel 2000. It only allows me to modify the Y-axis cross location. The others, number of categories between tick marks and labels are greyed out. I found that if I set Excel 97 to the values that Excel 200 forces me to use, it is messed up just like it is in Excel 2000. So ... if only Excel 2000 would allow me to set the X-axis scaling, I'd be in good shape!

May 13th, 2002, 04:15 PM	#9
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,027	Hey again, Can you get away with applying an autofilter on your data set, and then using a custom filter on the column that contains your x-axis data. Adam

May 13th, 2002, 04:33 PM	#10
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	CarlB, I believe you're missing a "fine" point about Excel chart x-axes. There are actually 3 varieties. First, there are categorical values which cannot be scaled. If you're creating a Column chart for {"Apples";"Bananas";"Grapes"} the x-axis is categorical. If you change the Chart type to Line it's obviously still categorical. If you subsequently replace {"Apples";"Bananas";"Grapes} with {1;2;3}... guess what... it's still categorical -- even though it looks like it can be scaled. Second, there are scalar x-axis values like those on a X-Y Scatter chart. These can be {1;2;3} too, but they behave differently -- allowing you to set Minimum/Maximum values on Format Axis Scale tab. Finally (and, the most recent addition), there's time-scale. Typically, these are used with the Line chart type. These can be scaled too. And, date formatted to suit your needs. Okay, now here's "pay dirt" for this treatise... If your x-axis label is sourced from 2 or more columns you have a categorical axis. If your x-axis label is sourced for a single column containing date values you have time-scale. Otherwise, a x-axis sourced from single column will be categorical or scalar depending on the chart type and the data type of the values themselves. [ This Message was edited by: Mark W. on 2002-05-13 16:03 ]