SOLVED

Need help making chart from data taken on non-contiguous dates

Occasional Contributor

I want to make a chart in Excel 365 for the weights of our two cats, Calvin and Hobbes. I am starting with data from our vet, who gave my wife a chart for each cat recently. Since we don't take both cats in to the vet each time, there are some dates that only have the weight for Calvin and some other dates that only have the weight for Hobbes. Please see attached Excel file. It is ok that there are interruptions in the lines but it seems the lines on the chart for each cat do not correspond to the correct dates for the data points for the respective cat. I would also like the dates on the X axis to be the actual dates vs a linear progression of dates corresponding to the vertical lines.

Thanks,

Don

7 Replies

@Snoopdon 

You might want to connect the points instead of leaving gaps.

And change the horizontal axis to a text axis to display only the dates from the source.

S1616.png

See the attached version.

best response confirmed by Snoopdon (Occasional Contributor)
Solution

@Snoopdon  I think what you want is an X-Y scatter plot.  See attached

I think this works. I changed it to the type of scatter plot that is connected with lines and now it is what I want. Thanks, Don
Ok, this one works also. Thanks, Don
Ok, this one works also. Thanks, Don

I'm curious, how did you connect the points?
Thanks,
Don

@Snoopdon 

In the 'Select Data' dialog for the chart, I clicked the button 'Hidden and Empty Cells'.

S1617.png

In the resulting dialog, I selected 'Connect data points with line'.

S1618.png

Finally, I clicked OK in both dialogs.

Excellent! I never knew about that function. I've been using Excel for maybe 25 years or more and am always learning new features. Thanks for pointing that one out to me. I wish there were a way for me to accept TWO answers as the best because your answer is equally as helpful as the one posted by the other person.
Don