Share via

Excel line chart won't skip missing data point, connect directly to the next data point. It return to zero.

Anonymous
2024-09-03T13:28:13+00:00

I have tried "Select Data" "Connect line..." It didn't work

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Rory Archibald 18,875 Reputation points Volunteer Moderator
    2024-09-04T12:28:23+00:00

    If you change the formulas in the work file to return NA() rather than "" then the line chart will treat them as empty. You can use conditional formatting if necessary to hide the #N/A errors in the cells.

    1 person found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rory Archibald 18,875 Reputation points Volunteer Moderator
    2024-09-04T08:25:08+00:00

    Are those blank cells actually empty, or do they have formulas in them?

    0 comments
  2. Anonymous
    2024-09-04T12:19:28+00:00

    It has formula in the work file. I also tried to copy the fake data and pasted into a new sheet using paste special -> value only to draw a new chart. the blank cell was blank to me. The result was the same.

    I further tested the cells with Len and isBlank function. Strangely enough, the Len function always returned 0, the isBlank function returned "No". If I highlighted the cell and click delete (not the backspace), then isBlank returned "Yes". What I can do to delete the hidden "ghost".

    0 comments
  3. Anonymous
    2024-09-04T12:22:03+00:00

    Rory, thanks to your question. That's mind opening

    0 comments
  4. Anonymous
    2024-09-04T13:11:40+00:00

    Rory, That addressed my chart issue. Great solution, Really appreciate it!

    0 comments