Excel plot time series frequency with continuous xaxis

Question

I want to plot a time series frequency data similar to the below  I tried many types of charts but they all put the time  ie 08 00  exactly under data series column  I also tried to type 8-9   9-10  10-11    but that looks awful just because time series needs to be continuous  I want to have 8 00 and 18 00 as the starting and ending points and data series should come above intervals  between 8-9-10-11 etc   Any suggestions   08 00 00 - 09 00 00     12 09 00 00 - 10 00 00     11 10 00 00 - 11 00 00     13 11 00 00 - 12 00 00     12

User · Accepted Answer

You can get good Time Series graphs in Excel, the way you want, but you have to work with a few quirks.

Be sure to select "Scatter Graph" (with a line option). This is needed if you have non-uniform time stamps, and will scale the X-axis accordingly.
In your data, you need to add a column with the mid-point. Here's what I did with your sample data. (This trick ensures that the data gets plotted at the mid-point, like you desire.)
You can format the x-axis options with this menu. (Chart->Design->Layout)
Select "Axes" and go to Primary Horizontal Axis, and then select "More Primary Horizontal Axis Options"
Set up the options you wish. (Fix the starting and ending points.)
And you will get a graph such as the one below.

You can then tweak many of the options, label the axes better etc, but this should get you started.

Hope this helps you move forward.

User · Answer

I would like to compliment Ram Narasimhans answer with some tips I found on an Excel blog  Non-uniformly distributed data can be plotted in excel in    X Y  Scatter Plots  Linear plots with Date axis   These don t take time into account  only days  This method is quite cumbersome as it requires translating your time units to days  months  or years   then change the axis labels    Not Recommended    Just like Ram Narasimhan suggested  to have the points centered you will want the mid point but you don t need to move to a numeric format  you can stay in the time format   1- Add the center point to your data series   --------------- ------- ------       Time         Time    Freq    --------------- ------- ------    08 00 - 09 00   08 30    12      09 00 - 10 00   09 30    13      10 00 - 11 00   10 30    10      13 00 - 14 00   13 30     5      14 00 - 15 00   14 30    14     --------------- ------- ------    2- Create a Scatter Plot  3- Excel allows you to specify time values for the axis options   Time values are a parts per 1 of a 24-hour day  Therefore if we want to 08 00 to 15 00  then we Set the Axis options to    Minimum   Fix   0 33333 Maximum   Fix   0 625 Major unit   Fix   0 041667       Alternative Display    Make the points turn into columns   To be able to represent these points as bars instead of just point we need to draw disjoint lines  Here is a way to go about getting this type of chart   1- You re going to need to add several rows where we draw the line and disjoint the data   ------- ------    Time    Freq    ------- ------    08 30     0      08 30    12                       09 30     0      09 30    13                       10 30     0      10 30    10                       13 30     0      13 30     5                       14 30     0      14 30    14     ------- ------    2- Plot an X Y  Scatter  Chart with Lines   3- Now you can tweak the data series to have a fatter line  no markers  etc   to get a bar column type chart with non-uniformly distributed data

[excel] Excel plot time series frequency with continuous xaxis

Examples related to excel

Examples related to plot

Examples related to time-series