Plotting daily values and weekly averages in the same chart

Question

I have a simple data set of weight values per day, the DAY data set:

Date  Weight
1/11  61.3
2/11  61.9
3/11  62.2
4/11  61.5
[...]

I want to plot this on a line chart, together with a second line for weekly averages, the WEEK data set. I've entered these averages as weights entered for every 7th day, in the middle of a week:

Date  Weight
3/11  61.5
10/11 61.7
17/11 61.9

I'm a beginner with Excel graphs, and I haven't yet figured out if this can be done in the same line chart. Plotting them in separate charts gives me correct graphs, but in the same chart the WEEK data will only plot as points, not connected points. I'm guessing this has to do with the WEEK data missing correspondents for each DAY axis label?

I've tried searching either for a solution, or to better understand the issue, but without any luck. I may be missing the right search keywords.

Answer 1

As mentioned in the comments, in a Line Chart Excel treats the horizontal axis as categorical values. This means that regardless of what value you assign to each horizontal interval, Excel is just going to count them and use your values as labels. So your data, as a line chart, looks something like this:

So, to get Excel to recognize the values you want for the horizontal axis, you need to use an XY/Scatter Chart. Excel actually treats your horizontal axis values as values, not categories, so you can control exactly where points lie along the axis. So, convert your chart to an XY/Scatter Chart (with Lines) and the same data is plotted like this: