1

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

Improve this question
3
  • 2
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. Commented Apr 6, 2023 at 10:27
  • Maybe uploading some screencaps of what you are currently getting and what the desired result should be to imgur.com and linking to them would be useful here. Commented Apr 6, 2023 at 20:07
  • 1
    Added links to two images as suggested. Thanks. BTW I wasn't allowed to embed the images directly due to: "You need at least 10 reputation to post images". (This is my first StackExchange question.) Commented Apr 7, 2023 at 7:50

1 Answer 1

Reset to default
0

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

original chart

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

option 1

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 2

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.

Option 3

Improve this answer
1
  • 1
    Thanks for the informative answer. Knowing that I need to perform such extra steps assures me that I haven't overlooked an Excel function. I now know how to proceed. Commented Apr 7, 2023 at 16:52

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.