# How can I horizontally space data points on a chart when the dates are arbitrary?

Say I have events that occur on sporadic dates, like this:

Date Qty
2022-01-01 100
2022-02-08 200
2022-06-17 150
2022-08-03 700
2023-01-27 300

I have rows for arbitrary days in Jan, Feb, June, Aug this year, and Jan next year. I could even have multiple entries in a single month.

I want to insert a chart with the dates on the horizontal axis, and quantities on the vertical axis. The horizontal gap between data points should be proportional to the number of days in between each row of data. Something like:

``````                            700
----
/           ----
---- 300
/
200 ------
/          ----- 150
100
---------------------------------------------------
Jan Feb             Jun     Aug                 Jan
``````

Is this possible, without having to insert empty rows into the table for every month where there is no data?

As far as I can tell, by default google sheets is spacing each data point equally across the X axis, regardless of how far apart the dates are. Here is a screenshot showing what I mean:

Note that the columns are evenly spaced. In the data though, columns 1 & 2 are ~one month apart, while columns 2 & 3 are ~four months apart. I want the columns to be spaced accordingly.

• Hi. Is this possible, without having to insert empty rows into the table for every month where there is no data? I converted your data to a period of 392 days (1 Jan 22 to 27 Jan 23) checked "Chart Style" = "Plot null values". There is no effect on the x axis. Commented Jan 12, 2022 at 4:21
• I'm not sure that the actual chart created by Google is a lot/at all different to your example. Would you please include the actual chart created by Google and overlay how/where you expect that your version would be different. Commented Jan 12, 2022 at 4:25
• Edit the chart, "Customize", "Grid lines and ticks", select "Horizontal Axis". Edit "Major count" = 10. This will insert a date on every major tick. I think this is proof that the "horizontal gap between data points should be proportional to the number of days in between each row of data." Commented Jan 12, 2022 at 4:32
• @Tedinoz I've added a screenshot showing the problem. The columns are all evenly spaced in the chart - I want them spaced out proportionally to the dates. I don't see an option for "Major count" when I customise the chart. Commented Jan 12, 2022 at 22:32
• _ don't see an option for "Major count" when I customise the chart._ That is because you chose a "Column chart" instead of a "Line chart" Commented Jan 13, 2022 at 0:58

You want the horizontal gap between data points to be proportional to the number of days in between each row of data.

This is the result of the standard chart layout.

5 data elements

This is the chart created using five data elements.

362 data elements

This is the chart created by including a value for every day between 1 Jan 2021 and 27 Jan 2023. Data

Chart Changes requires for this chart:

• Customize, Chart Style, check "Plot Null Values"
• Customize, Grid lines and ticks, select "Horizontal Axis", set "Major count" = 10.

• Yeah so basically what I want is the chart in your second screenshot, using the data from your first screenshot. From my original question: "Is this possible, without having to insert empty rows into the table for every month where there is no data?" Commented Jan 15, 2023 at 2:07
• My apologies. In general terms, the answer to your question is "no" BUT... I have just been reading the documentation regarding "Customizing Axes" and specifically Discrete vs Continuous. I think that a "Continuous" x-Axis might be the solution; problem is that the answer involves writing a script. There are some hidden traps (and workarounds) too- refer Hiding 0 value data labels in chart. Commented Jan 15, 2023 at 6:47
• No worries, even knowing that it's not possible (without scripting) is a helpful answer. Cheers. Commented Jan 16, 2023 at 23:24
• @CamJackson By using a combination of sheetaki.com/… to populate all the dates using a one line formula, and `IFNA(VLOOKUP(...` to reference to your original table you can create a new table with the in the necessary format with minimal effort. Commented Aug 3, 2023 at 22:47

I know it is a late response, but for those searching for the answer as I just did:

This is possible and automatically done if you choose Line Chart, Area Chart, and their "Stacked" variations. It doesn't work with Combo Charts and Bar Charts.

Thank you to Tedinoz for the link to Discrete vs Continuous.

Once I discovered this link, I solved the issue I'd had for several hours within a matter of minutes.

The link makes it clear that

In line, area, bar, column and candlestick charts (and combo charts containing only such series), you can control the type of the major axis:

For a discrete axis, set the data column type to string. For a continuous axis, set the data column type to one of: number, date, datetime or timeofday

Now realising that this was actually possible, I experimented and discovered that you need to UNCHECK the box that says "treat labels as text" at the bottom of the Chart Editor -> Setup tab. By unchecking this box, Google Sheets interprets your x-axis values as continuous, at which point the axis becomes uniformly distributed in time/date/number.

It seems that for the chart type that I had (Stacked Column Chart), the default behaviour is to treat labels as strings, which is understandable.