Excel 365 MSO XY chart errors when a longer X data column ends with empty cell and adjacent to other data

Question

Excel 365 MSO XY chart errors when a longer X data column ends with empty cell and adjacent to other data

Aaron Boyce 0

In an Excel spreadsheet, I have a table with multiple series of data for some XY scatter charts; some series have more data points than others. I noticed my series with the longest data set was getting its X-values replaced with ordinal values as a count of each data point. Answers online suggested a bad data type, but my cells were all general or number, both cases still caused the error.

To support plotting of multiple data sets, the chart data range extended beyond the last data point of the longest series. Note, the error only occurred when a table/chart had mixed length series data sets, and did not occur on the bottom table in my spreadsheet even with mixed lengths.

I copied the data from two tables to a simple new workbook/sheet, made a new chart and did not see the problem. That is when I realized the new chart series ranges stopped on row 18, which is the last data point on the longest series. I extended to row 19 and still did not see the problem.

(see the attached; this form won't allow .xlsx workbooks, try with the .pdf files:

failing case 2025-09-22_xychart_err.pdf

good case 2025-09-22_xychart_ok.pdf )

But, when I extended the chart data range to row 20 the problem came back; note row 20 is the last empty row before new data for the table below starting on row 21.

Even with the range going to row 20, I can get the chart to display correctly if I repeat the last X-value data in the longest series (column I) down to row 20.

Can you verify if there is a fix for this bug?

Version:

Microsoft® Excel® for Microsoft 365 MSO (Version 2508 Build 16.0.19127.20192) 64-bit

2025-09-22_xychart_err.pdf

getting: "We encountered an unexpected error." when trying to post...

Aaron Boyce 0 Reputation points

2025-09-23T22:06:07.81+00:00

Looks like the unexpected error was just from being a new account and not able to post. So now I was able to post. Here is the .pdf of the good case.

2025-09-22_xychart_ok.pdf

1 answer

Your answer

Aaron Boyce 0 Reputation points

2025-09-23T22:06:07.81+00:00

Looks like the unexpected error was just from being a new account and not able to post. So now I was able to post. Here is the .pdf of the good case.

2025-09-22_xychart_ok.pdf

Answer 1

Hi @Aaron Boyce,

Thank you for posting your question in the Microsoft Q&A forum.

Let us conclude this situation based on your sharing:

In your file, when the chart data range extended to row 20, Excel replaced X-values with ordinal values for the longest series.
When the range stopped at row 18 or 19, the chart behaved correctly.
You can repeat the last X-value in the longest series down to row 20 made Excel run properly.

This behavior is highly an expected behavior based on how Excel interprets the X-values range when it includes blank cells:

In XY Scatter charts, Excel expects a 1:1 mapping between X-values and Y-values for each series.
If the X-range is longer than the Y-range, or contains blanks, Excel may ignore the X-range entirely and fall back to ordinal numbers (1, 2, 3, …).

That's why it happened in your situation. When you extended the X-range to row 20, Excel saw extra blank cells in the X-range of the longest series. Since the Y-range only had values up to row 18, Excel couldn’t match X and Y values properly. When you repeated the last X-value down to row 20, Excel saw valid X-values for all Y-values even if some Y-values were blank or missing, so it stopped falling back to ordinal mode.

However, we want to thank you for raising this concern. I’ve shared your feedback internally, and I’d also recommend submitting it directly through the Microsoft Feedback Portal so it can be reviewed and prioritized by the product team.

User's image

As moderators in the community, please be aware and accept that we are not responsible for the design of the product and that we have limited resources for detailed information about Microsoft products. We help guide users to the appropriate channels but do not influence product design decisions.

Thank you again for your patience and for bringing this to our attention. While we cannot change the product’s design directly, we’re here to support you and will continue to assist however we can. Please let us know if the steps above help resolve the issue or if further assistance is needed.

Wishing you a great day ahead.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Aaron Boyce 0 Reputation points

2025-09-24T18:17:06.6133333+00:00

Note: Whenever I change the cell range for the X-values, I made the same change in the Y-value range, so there was always a 1:1 match of cells. So whenever the X-cell was empty, the y-cell was also empty and should be ignored; interestingly this is handled correctly for the other series. Also for simplicity I was only changing the series that had the problem; but the behavior is the same if all series have the same number of rows in their ranges.

Finally, the most telling behavior (which was not explained) is that when the range stops at row 19 (one empty row inside the range or is followed by an empty cell), it behaves correctly. But when the range stops at row 20 (2 empty rows inside the range or the next cell beyond the range has a value), that is when it mis-behaves. FYI, I just tried deleting the content of row 21 and it did not fix it, so I don't understand the pattern/behavior; my initial observation in the large spreadsheet was that the bottom table worked OK.

It would seem that even though you are not part of the design department, you could forward test cases like this on to them, rather than expecting the customer to report it again to a different department. Is Microsoft really taking responsibility to own the finding and fixing of abnormal behavior?

Share via

Excel 365 MSO XY chart errors when a longer X data column ends with empty cell and adjacent to other data

1 answer

Your answer