I have very large spreadsheets with complex formulae that I do not want to move or copy. Often the formulae result in empty cells-so a column of 500 rows may have 100 empty cells dispersed throughout. I use ("") in the formulae to designate an empty cell, so these empty cells actually have ("") in them, but display empty. I know no other way of a formula resulting in an empty cell and other formulae ignoring the result.
I need to make and print many different charts on these columns, changing variables in between. So the charts are always changing and which cells are empty is always changing.
I commonly use the XY scatter charts because I want to see how 2 variables relate to each other.
But, Excel keeps interpretting the empty cells as zeroes.
I have gone into Tools->Options->Chart (tab)->and highlighted the radio button Plot Empty Cells As; "Not Plotted" (leave gaps) This seems to have no effect on the way charts work. I have closed the chart and made a new one, i have closed the file and opened a new one, I have closed Excel and re-opened after making thes changes to Options, to no avail.
The only thing that works, is to copy the whole data set to another part of the spreadsheet, then sort to get rid of blanks. Then the chart plots correctly. Otherwise, it interprets the blanks as zeoes and lines up the data points (1-500) in sequence instead of relating the X and Y data.
I have attached a sample Excel file with 2 charts demonstrating the problem. I am hoping to get the data (with blank cells) to graph like Chart B, not Chart A (which simply sequences the data irrespective of X-values)
Start Free Trial
