Issue with line graph and missing values

I haven't used Excel since my college days and have now struck an issue with my daughter's science project that is driving me crazy.

What she's done is measuring the force required to separate a magnet from a steel bar with an increasing number of sheets of paper in between the magnet and the bar. Initially, she increased the sheets of papers by 1 but because the function is not linear, she later switched to increments of 2 sheets, then 5 sheets and finally 10 sheets. With 105 sheets of paper in between the magnet no longer held.

We entered the results into Excel and then tried to plot a line graph with the number of sheets of paper on the x-axis and the force required to pull the magnet off on the y-axis. The issue is that because she did not increase the number of sheets consistently in increments of 1, the scale of the x-axis is off. Excel plots a point for each data point available but does not account for the fact that initially the interval is 1, then 2, then 5 and then 10. Each of the data points is the same distance from the previous point. I hope that makes sense.

I then entered x-values for each of the missing points and left the y-values blank which fixed the scaling issue on the x-axis but now the line goes to zero for each of the missing values. I found an option somewhere in the graph menu "Hidden and empty cell settings" which, by default, has "zero" selected. There is an option "Connect data points with line" but that option is grayed out and cannot be selected.

How can I fix this?? Any pointers would be greatly appreciated. The spreadsheet is attached.



take measurements for each number of sheets

That was it, thank you!!

Scatter graphs to me had a different meaning so it never occurred to me to select that option. Problem solved though, so thanks again for the fast help.