There are countless of Stack posts about how to create a datetime X-axis in Excel (change from Number to Text in the axis formatting) but this only solves the display of the text. I would like to have a timeline where the data points are proportionally distanced from each other based on the time difference between when the data points where taken.

Let's say that I want to have one tick on the X-axis represent one hour of time. I want to then, on that timeline, add certain data points to this axis. If I just plot them with the standard Text formatting on the X-axis it becomes something like this:

enter image description here

As you can see, the data points weren't taken at the same time every day, sometimes you have several data points for one day, and some days none were taken at all. What I want to have is something along the lines of the following:

enter image description here

To make this work I created a series with empty entries for every hour except for the ones that I had values for but this is a pretty crappy workaround. Is there any way to get a proportionally spaced X-axis in Excel without creating dummy zero-values like this?

  • 1
    What kind of chart are you using? An XY scatter should have the data points spaced proportionally. – Ron Rosenfeld Oct 21 '19 at 0:40

There are two basic approaches. Either (1) you use an X-Y chart to plot the points you want spaced properly on the X-axis as well as the Y-axis, or (2) you define a conventional category axis with a resolution of 1 hour and then synthesize the hourly data you need by interpolating between the data points you actually have (set a fill colour and switch of borders for data series)

Usually using an X-Y chart is the best approach but there are thing you can do with e.g. area charts you couldn't do with X-Y ones (and vice-versa). Though if you want to do something fancy you may well be better off finding an add-in that does it for you.

For example, with an X-Y chart you can plot arbitrary things, like this:

Arbitrary drawing with X-Y chart

You could just paste the shapes in a pictures, but they are not then precisely aligned to other chart items whereas this way the lines change to reflect scale changes.

Alternatively, as you can't fill in an X-Y chart, you can use a area chart, but to make it work you have to create a series with suffient categories to represent the detail (say 100 in this case) and interpolate the datapoints to give the intermediate values between your actual data points:

So here is the normal X-Y chart (which you can't fill in):

Conventional X-Y chart (can't fill it with a pattern)

But if you simulate it using an area chart (which requires the calculation of a large number of intermediate points) you can create something very similar:

Simulation of an X-Y chart using an area chart

One key limitation of this vs the X-Y chart proper is that you can't go 'backwards' on the X-axis. The X-values have to be strictly increasing.

Still, these give you some additional options if you find them useful.

  • The first paragraph here is fine. I don't know what the second paragraph is getting at. – Jon Peltier Oct 24 '19 at 3:00
  • I'll extend the answer to explain... – Duke Bouvier Oct 24 '19 at 9:44
  • Okay, I see what you were saying. Quite a bit more than the user seemed to need, which is why I wondered. Since the horizontal axis is numeric in your example, with a suitable scale, you don't need to do all the interpolation. Just make the axis a date axis, and Excel properly spaces the points. See Line-Area Pseudo-XY Chart for a brief illustration. You could use this approach for the original data, but it requires some manipulation to get non-fractional axis increments (dates not times). – Jon Peltier Oct 25 '19 at 15:12

if you select the x-axis and choose 'axis options' in the right sidebar that pops up, you can change the axis type from 'text axis' to 'date axis', and in the latter you can set the day intervals you want the chart to use, etc. Much simpler than the above.

  • 1
    The problem is that I want the ticks to be by the hour since I'm using datetime. I can have multiple entries on the same day, and a date axis would just ignore those/group them into a single point. The goal is to have a timeline with proportional distances between data points, where the data points can be anything from days to minutes apart. – Tanaka Saito May 14 '20 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.