Geek Answers Handbook

Excel graph time frequency with continuous xaxis

I want to build frequency data of a time series similar to the ones below. I tried many types of charts, but they all set the time (i.e. 08:00) exactly under the data series column, I also tried to enter 8-9, 9-10, 10-11 ... but it looks awful just because That time series should be continuous. I want to have 8:00 and 18:00, as the start and end points and the data series must exceed the 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
+6
source share
2 answers

You can get good time series graphs in Excel however you want, but you need to work with a few quirks.

  • Be sure to select "Scatter Graph" (with the line option). This is necessary if you have uneven timestamps and scale the X axis accordingly.

  • In your data you need to add a midpoint column. Here is what I did with your sample data. (This trick ensures that the data will be displayed at the midpoint as you want.) enter image description here

  • You can format the parameters of the X axis using this menu. ( technology scheme> Design-> Layout ) enter image description here

  • Select Axes and go to Primary Horizontal Axis, and then select Advanced Horizontal Axis Options

  • Set the desired options. (Correct the start and end points.) enter image description here

  • And you will get a schedule like below. enter image description here

Then you can configure many parameters, mark axes, etc. better, but this should help you get started.

Hope this helps you move forward.

+7
source

I would like a compliment to Ram Narasimhan to answer some of the tips I found on the excel blog

Unevenly distributed data can be built in excel in

  • XY (scatter plots)
  • Line graphs with date axis
    • It does not take time, only days.
    • This method is rather cumbersome because it requires the conversion of your units of time to days, months or years .. then change the axis labels ... Not recommended

Just as Ram Narasimhan suggested, to center the points, you will need a midpoint, but you do not need to switch to a numerical format, you can stay in the time format.

1- Add a 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 axis options. The time values ​​are parts for 1 day. Therefore, if we want from 08:00 to 15:00, then we set the axis parameters:

  • Minimum: Fix: 0.33333
  • Maximum: Fix: 0.625
  • Base Unit: Fix: 0.041667

Line scatter plot

Alternative display:

Make dots turned into columns:

To be able to represent these points as columns instead of a simple point, we need to draw disjoint lines. The following is a way to get this type of chart.

1- You will need to add a few lines, where we will draw a line and split 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- Set up an XY (Scatter) chart with lines.

3 Now you can adjust the data series to a thicker row, without labels, etc., to get a column / column type chart with unevenly distributed data.

Bar-line scatter plot

+5
source

More articles:


All Articles