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.

  • One way: expand your weekly data to repeat the value each day in the week (7 daily entries of each week's average). Use a scatter (XY) chart to plot the daily and weekly values as two series (it would look better using just lines, no point markers). The weekly values will display as a line that's horizontal for the week, running through the daily values. – fixer1234 Nov 12 '16 at 17:27
  • Use an XY/Scatter chart and you can specify where every point is plotted. LIne charts force the horizontal axis to categorical values. – dav Nov 14 '16 at 15:22
  • Thank you both. @fixer1234 it's a very good alternative if I can't find exactly what I'm looking for. – Dirk101 Nov 25 '16 at 15:27
  • @dav the scatter chart really does do what I'm looking for, minus the trend line – Dirk101 Nov 25 '16 at 15:27
  • 1
    Looking through scatter chart types, I've found the "scatter with straight lines" type, which is exactly what I needed. If this is what you were referring to, feel free to add it as an answer and I'll tick it as the solution. – Dirk101 Nov 25 '16 at 19:18

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:

line chart

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:

xy chart

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.