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.
08 Answers
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).
4The easiest way to do this is to type the number 1 in another cell, copy it and then "paste special" it by multiplying. All the cells will turn to numbers.
0I ran into this issue when referencing a cell on another sheet ("Confirmed") that is obtained via a query. This did not work:
=Confirmed!F228This worked:
=NUMBERVALUE(Confirmed!F228)I know this is an older post, but posting so that maybe the next person to have this doesn't spend over an hour trying to resolve it. I am using Excel version 2011 for Office365.
I had the exact same issue that "The111" asked about, but none of the solutions offered by "soandos" or others worked.
The solution I was told is that there is a character at the end of the "data" in the cell and therefore Excel treats it as text. If you select the cell, you will notice you can press backspace. Although I could not open the file "The111" attached to verify, I did see this empty character in my data set.
The solution is to use Find and Replace > copy that empty character from any cell you are having issues with > paste it into Replace and then select Replace All.
That should fix the issue. Note, note sure exactly what this empty character is, but as an experiment I tried using F&R and just pressing the spacebar once to give what I thought would be the same thing; however, when using the spacebar and then pressing Replace, it would give a message that it couldn't find anything to replace. Just a FYI.Image of solution process
1This 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: Sub walkselection2correctformatting() Dim c As Range For Each c In Selection c.Value2 = c.Value2 Next c End Sub
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."
Another solution which isn't mentioned here is don't hide your data. If you hide the columns that have your data in it, the data will not pull into the chart/graph and you have blanks. Once you "unhide" it all pops back into place.
1We could solve this problem by changing the system language to English in the system preferences. After restarting excel also appears in English and graphs appear again and can be created again.
1
