I have looked at problems with XY charts displaying as Line charts previously at:
When is an XY chart not an XY chart …? and
Plotting Charts With Gaps …

The charts shown below. and also in the next post, can be downloaded from:
XY chart bug.xlsb

This post summarises problems associated with section of the plot range, in response to an Eng-Tips question, regarding problems plotting an XY chart with two rows of data.

This is an example of an Excel bug that has been there for ever, and for some reason many people don’t even see it as a bug. You want to plot an XY graph (known to Excel as a scatter graph, even when displaying the data as lines), but if Excel sees any hint that the data is actually a “line graph” (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking. Also if the number of columns is equal to or greater than the number of rows it will treat the data series as being in rows rather than columns. The screenshot below illustrates these problems:

The top left graph was generated by selecting a “Line Chart” as the graph type, with range C4:D6 selected.

Top right had the same selected range, but I selected “Scatter (XY)” as the graph type. It has converted it into a Line Chart, but with the series names converted into sequence numbers (but with values displaying as decimals rather than integers!).

For lower middle I selected range C5:D6 (i.e. without the header row) and chose Scatter (XY). This has now plotted as an XY graph, but with the data in rows rather than columns.

For lower right  I selected C5:D7 and Scatter. The data is now plotted correctly, but it has decided that the text in cell D4 is the graph title.

For the lower left chart I selected C5:D6 and chose Scatter (XY), then reselected the data. If you don’t select the header rows when you generate the chart it leaves it as an XY graph with one data series, and you can re-select by drag and drop, or through the select data menu (if you prefer).