Excel XY Scatter Chart with automatic line breaks

Question

I have a series of time and number pairs that I am plotting with an Excel XY Scatter with Straight Lines chart. The time values increase by a variable amount, though typically between 30 seconds and 2 minutes. This works well, except occasionally there is a time gap between consecutive pairs of over 10 minutes. I would like to represent these gaps on the chart with a line not being drawn over that gap. Is that possible? For example, in the following series, I don't want a line drawn between 21:33:22 and 22:03:17 since there were no readings during that extended period.

21:29:21    90
21:30:21    80
21:31:19    59
21:32:22    55
21:33:22    111
22:03:17    54
22:05:19    55
22:06:22    54
22:07:22    58

This is the chart Excel is currently producing from the above series: Current chart

This is what I would like: required chart

Answer 1

As you've discovered, Excel only charts the data you provide it. So, to have a gap in the chart, you'll need a gap in the underlying data. There are a couple ways to accomplish this, but your solution will likely need to be based on the amount of manual intervention you'll want to provide it as the underlying data updates.

Original Chart

Option 1: Insert a blank line where you want your gap. Quick and easy, but extends your data for every break.

Option 2: Create a separate series for each line segment. Keeps data intact, but requires a manual adjustment to every gap and associated series.

Option 3: Convert your data to an Excel Table, then create a Table Column for each series. This semi-automates the process and keeps your underlying data series intact but requires helper columns and the manual addition of columns whenever a new gap is found.