# How can I build a graph with a time axis based on minutes:seconds, milli seconds (EXCEL2010)

### Question

• I'm using Execl 2010 and I want to create a graph based on a time. The times solution has to be in ms

time [mm:ss,ms]             data

01:10,200                       27

01:10,225                       130

and so on.

How can I format the x-axis to show such grapgh?

michael

Wednesday, December 12, 2012 8:08 AM

• Create an XY scatter chart. This is the only chart type that supports a time scale on the x-axis.

Regards, Hans Vogelaar

Wednesday, December 12, 2012 8:58 AM
• Time-series charts do not support for fractions of a day. The normal approach for time units of less than a day is to use a Scatter chart and set the Axis to display units of the relevant fraction of a day, eg for hours 1/24 or 0.04166667, for minutes 1/3600.

However for your data, if(?) you have data for every 5 milli-seconds you could use an ordinary Line type. Split your times into two columns, h:mm in col-A (only one entry for each minute), milli-seconds in col-B, and finally the data in col-C. In X-Axis options select "Multi-level Category Labels" and go from there. When done, you should have two rows of ticklabels, milli-seconds in units of 5 over "centred" minutes.

Peter Thornton

Wednesday, December 12, 2012 10:17 AM
• An XY chart is a Scatter chart in which both XY values are regarded as "data" values. In most other chart types the X values are "category" values. The difference might seem mute in certain scenarios but XY points can be plotted anywhere along the X-axis according to the X-value. However as "category" values points are plotted in order one after another.

So for your purposes change the chart to a Line type as I suggested before, though that will only be appropriate if the time intervals are equally spaced and in logical order. Start like this -

-Split the time values into two columns as I described before
-Select the data values in the 3rd column and create a Line chart
-Select the Line, click Select Data
-In the right panel (Horizontal Category), Edit, select the two time columns, eg col-A & col-B
-Right click the X-Axis, Format Axis, Axis Options, check "Multi-level Category Values" (though it will probably have been selected by default).

Keep in mind you don't need to complete every cell in col-A, just one value for every significant change.

After all that I'm not sure I thought through your original question. You want to plot every 5ms, right?, so that means 200 points per Second, not sure if I what I'm suggesting is practical. If not give a clear idea of the data set you are working with.

Peter Thornton

Wednesday, December 12, 2012 1:21 PM

### All replies

• Create an XY scatter chart. This is the only chart type that supports a time scale on the x-axis.

Regards, Hans Vogelaar

Wednesday, December 12, 2012 8:58 AM
• Time-series charts do not support for fractions of a day. The normal approach for time units of less than a day is to use a Scatter chart and set the Axis to display units of the relevant fraction of a day, eg for hours 1/24 or 0.04166667, for minutes 1/3600.

However for your data, if(?) you have data for every 5 milli-seconds you could use an ordinary Line type. Split your times into two columns, h:mm in col-A (only one entry for each minute), milli-seconds in col-B, and finally the data in col-C. In X-Axis options select "Multi-level Category Labels" and go from there. When done, you should have two rows of ticklabels, milli-seconds in units of 5 over "centred" minutes.

Peter Thornton

Wednesday, December 12, 2012 10:17 AM
• Hello Peter,

I'm using the xy-chart, is that right?

where can i choose the mentioned "multi-level category label"?

thanks michael

Wednesday, December 12, 2012 11:03 AM
• An XY chart is a Scatter chart in which both XY values are regarded as "data" values. In most other chart types the X values are "category" values. The difference might seem mute in certain scenarios but XY points can be plotted anywhere along the X-axis according to the X-value. However as "category" values points are plotted in order one after another.

So for your purposes change the chart to a Line type as I suggested before, though that will only be appropriate if the time intervals are equally spaced and in logical order. Start like this -

-Split the time values into two columns as I described before
-Select the data values in the 3rd column and create a Line chart
-Select the Line, click Select Data
-In the right panel (Horizontal Category), Edit, select the two time columns, eg col-A & col-B
-Right click the X-Axis, Format Axis, Axis Options, check "Multi-level Category Values" (though it will probably have been selected by default).

Keep in mind you don't need to complete every cell in col-A, just one value for every significant change.

After all that I'm not sure I thought through your original question. You want to plot every 5ms, right?, so that means 200 points per Second, not sure if I what I'm suggesting is practical. If not give a clear idea of the data set you are working with.

Peter Thornton

Wednesday, December 12, 2012 1:21 PM