I had a certain block of data in a spreadsheet I was trying to create a graph (chart) from. I spent over an hour trying to figure out what was going wrong with my chart settings. I tried cutting and pasting the data to new cells... no luck. Eventually out of frustration I manually re-typed the data into new cells. It charts fine! You can even re-type it in the CURRENT cells and it charts fine! Incredibly frustrating. I have gone over cell properties with a fine toothed comb, etc... can find no differences. Please see linked file and see if you can figure this mystery out. It will be obvious once you open the file, I have two tiny test tables which appear identical, but one will plot a chart, and one will not.
The issue is very simple. In the "won't graph" table, the numbers are stored as text. To fix this, just retype the numbers, click on the green prompt to the side of the box, or paste it to and from another application (excel should autodetect it as a number).
This is what reliably works for me. Although I cannot swear I don't apply Number format first. Number Format alone definitely does not work most of the time. Sometimes Text-to-Columns causes some weirdness. This works, but you have to select the range in question and do a little VBA:
Dim c As Range
For Each c In Selection
c.Value2 = c.Value2
I had a stacked column chart with four data points monthly over 60 months. One morning the first two years' data disappeared! The axis labels were still there but no data. I checked all the formulas, formatting, etc. I checked that the data hadn't been erased. Then I read here about numbers formatted as text. So I checked some data: they were formulas and seen by Excel as values. (I could multiply it in another cell.)
This is what worked. I changed a few of the data points from formulas to values. (Copy. Paste Special Values.) I checked the chart. The entire two years were back! Yay--so far. I then hit Undo to restore my formulas. The two years' data stayed on the chart. Success!
I'm calling this a bug in Excel. I thought it might be part of the switch-over from 2013 to 2016 version, but I checked earlier correct versions and they were done on 2016 as well. There had been no activity on the data cells. Bug. Or, as the title of this thread reads: Excel "Quirk."