Excel Scatter chart plots data but not formula results

Jim 20 Reputation points
2026-07-12T02:42:12.8133333+00:00

Excel Scatter plot correctly plots X,Y data from two number formatted columns. But does not plot the correct x if the x cell(s) is a formula that results in the same values.

Instead, the x values look like a row or sequence number. E.g., the data point is x=15.50, and y=2.0. However, hovering over the point on the chart reveals ' Series "apples" Point "15.50" (10, 2.0) ' The x value is weight calculated from a formula with a blank "" operator in it so the field is blank if not used. I noted on some posts that this can cause the problem.

If I create a helper column next to the x formula column and paste the number values from the formula into it and change the chart range to point to the fixed values, it plots perfectly. Unfortunately, this method does not update if the equation's variables change. This has to be a dynamically updated worksheet.

Finally, I am not allowed to use Visual basic scripts in this worksheet (arrg).

Can anyone offer a suggestion what would allow accurate scatter plotting of the formula column, or dynamic updates for the helper column?

Microsoft 365 and Office | Excel | For home | Windows
0 comments
Answer accepted by question author
riny 21,520 Reputation points Volunteer Moderator
2026-07-12T06:17:19.4+00:00

@Jim

Could you please show us how your data, the formula for x and the chart look like?

In the meantime see if replacing "" in whatever your formula is with NA() . That will force the chart to ignore that data point. A blank "" will be interpreted as zero and plotted as such.

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim 20 Reputation points
    2026-07-12T13:30:05.4033333+00:00

    Thanks.

    Formula: =IF(E8>0,ROUND(CONVERT(E8,"mm","in"),$Z$4),IF(D8>0,ROUND(D8,$Z$4),""))

    If the red dots range is the formula column, it looks like this. The black and blue dots are fixed values from the helper column and are where they are supposed to be. The colors represent 3 different types of the same object. If it were correct, the center sequentially spaced red dots would be more randomly spaced on the x axis. I will try the formula without the "", but the main use of the equation is not this chart. It's a different scatter chart that works fine with this formula. That blows my mind. I can't tell the difference in the setup. Even the X and Y data are in the same left/right column sequence. I created a brandnew chart with the same results as below.

    with formula

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.