Having trouble forcing Excel to plot data values instead of index values

**Matt_M** · 10-17-2016, 01:15 PM

I have two columns of data, X and Y. Let's say they are both of length 100. Y contains 100 integer values, but X varies. Depending on certain conditions, only a portion of the X data is valid. When X is not valid, I am using IF statements to populate the cell as a blank entry (=""). X could be 10 numbers and 90 blank cells, or 100 numbers and 0 blank cells. My chart is plotting Y vs X and I have selected all 100 values. I would like my chart to plot only the series pairs with valid data.

The blank cells are an issue. If X contains 100 integers, all is well. The data plots as expected. However, if X contains even 1 blank cell, Excel resorts to plotting Y vs the index of X and not the value of X. I want to continue plotting against the value of X even with blank cells.

To illustrate my problem I've isolated it to an easily understandable comparison. Here's what happens when X contains all integers. However, when a single value is blank, note what happens to the X axis. Is there a way to prevent Excel from saying "Oh, a non-integer? I'm going to plot index values then". I want to keep plotting values, but for Excel to simply gloss over the pairs with a blank member.

I re-submitted this twice due to typos in the subject line. Unsure how to delete the original copies... Sorry for the spam. I assumed I would be able to.

**MrShorty** · 10-17-2016, 01:38 PM

This is a common problem whenever we try to simulate blank cells, since Excel does not contain a function that truly means "blank". Note that the return value of "" is a text string. Whenever an XY scatter chart encounters a text string in the X values range, it ignores the numeric values and plots index numbers. The key to solving this is to make your IF() function return the N/A error (using the NA() function) instead of a text string.

This essay is an fairly thorough discussion around how Excel chart's "blank" cells and how to work with formulas to avoid these kinds of problems: http://peltiertech.com/mind-the-gap-...g-empty-cells/