Excel plot time series frequency with continuous xaxis

Question

A time series of frequency data like the one below is what I want to plot. I tried a variety of charts, but they all placed the time (8:00) precisely within the data series column. I also tried typing 8-9, 9-10, and 10-11, but that just looks terrible because time series must be continuous. The data series should start and conclude at 8:00 and 18:00, respectively, and should fall within the intervals of 8-9-10-11, etc. Any recommendations?

Answer 1

Excel has a few oddities that must be worked with in order to produce good Time Series graphs in the way you like.

• Make certain to choose "Scatter Graph" (with a line option). This is required if your time stamps are not uniform, and it will scale the X-axis appropriately.
• You must include a column with the midpoint in your data. What I did with your sample data is shown here. (This method makes that the data is plotted at the desired midpoint.)
• 
• You can format the x-axis options with this menu. (Chart->Design->Layout) 
• Select "Axes" 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.