Can't get Excel to plot to X axis data... It plots to generic 1, 2, 3 points

Question

I'm setting up a sheet to automatically plot data (2 data columns with a primary and secondary Y axis and common X axis data).  My selected data set has 17 rows, but usually only has data in the first 5-7 rows.  When the data is plotted the X axis goes to the generic 1, 2, 3, etc data points, instead of the actual data of 20.0, 70.0, 74.5, etc.  I'm using X-Y scatter chart type.  If I try to use Line chart type, it plots all the empty column point as zero, instead of ignoring them, like X-Y scatter does.

Any suggestions on how to get the chart to use the X axis data as the plot points?

Thanks,

Answer 1

OK, use the NA() function instead of the " " in your formula. Once you see your table filling up with the ugly #N/A values, select all cells in the table, then on the Home ribbon click Conditional Formatting > new Rule > use a formula to determine ... 

In the formula box enter

=isna(A1)

Replace A1 with the top left cell of your selection and make sure that there are no $ signs in the cell reference. Then click the Format button and set the font color equal to the cell fill color, e.g. if the cells are white, select a white font.  

That will make the #N/A values invisible to your users, but the cells are suitable for a XY chart.

Let me know how you get on.