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

Hello,

double check that the values in the cells are numbers, not text that looks like numbers.

• try to format them with fewer or more decimal points. If they don't react, they are text
• in that case, enter a 0 into an empty cell, copy that cell. Select the text cells and use Paste Special > Add. This will add a 0 to the text and convert it to a number at the same time.

When you have established that the values are truly numeric, double check the definition of the XY series data source. Edit each series and check the range that the X values are pointing to.

If that does not help, please replace confidential data with dummy data and share the file. Use a file sharing site or your Onedrive public folder, get a link and post that here.

Answer 2

The data is numerical. 

I've found the issue has to do with defining the X axis data set to all 17 rows (instead of the 5 rows where there actually is data).  I want to set the X axis data set to the 17 rows to cover any data that will populate the sheet in the future (17 rows or less).  From this data set, a chart is automatically generated. 

If I set the X axis data is set to just the 5 rows of populated data, it works fine.  Is there anyway to get around the "empty" cells in the data set causing the "generic" X axis?  I would rather set the sheet up so the user doesn't have to manually configure the chart for the X axis data.

Thanks!

Answer 3

The problem could be having more columns that rows. In my diagram I selected all the data and made an XY chart - the one to the left. It had what you call generic x-value. If I right click the chart and open the Select Data dialog, I can switch columns and rows - the chart to the right. I have left all the legends so you can see the effect.

best wishes

Answer 4

If the cells are truly empty, then the chart will plot correctly.

If you use a formula to calculate the X axis value and that formula returns an empty string like "" or a blank like " " then the chart will indeed number the data points on the X axis since the data is no longer truly numeric.

There are several approaches to deal with this.

1.  Instead of the "" or " " use NA().  You can use conditional formatting to suppress the ugly #N/A values in the data source.
2. use dynamic range names that grow and shrink with the data and plug the dynamic range names into the chart.  What formula to use for the dynamic range names depends on how your data is structured. To help you with this, I'd at least need to see a screenshot, but a file would be better.

Answer 5

teylyn,

You are correct, i use a formula to pull the data into a chart. I'm using an if statement to check for blanks and errors, and return " " if they exist. can you please elaborate on suppressing the #N/A with conditional formatting, or the dynamic range names? Those sound like solutions to my problem, but I'm not familiar with how to execute them...

Thanks for your help!