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.


  • 1
    Link seems to be dead. – pnuts Apr 11 '13 at 21:05
up vote 6 down vote accepted

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).

Some other methods

  • adding some of the information in the link will make this a even better answer for future readers – datatoo Jul 10 '12 at 13:46
  • Thanks, of course you are correct. The most frustrating part is that "Technique 3" from the link you posted did not work in this case. In fact I had been trying that method several times before making this post. "Formatting" the cells as numbers does not fix the issue. As far as the error checking boxes, I disabled that a long time ago as I got sick of seeing green triangles everywhere, which I realize makes me look stupid right about now. So in the future I know I can turn that on in a situation like this to enable the "convert" option since it seems not to be available anywhere else? – The111 Jul 10 '12 at 16:44
  • @Johnson, what do you mean? You want the green triangles back? – soandos Jul 10 '12 at 17:33
  • No, I know how to get them back (turn error checking on). What I mean is that simply changing the cell "format" to number does not fix the problem as "technique 3" seems to imply. The only real option is #1, which requires the "convert" button, which only seems available through the error checking menu (which I previously had disabled). Which seems kind of lame. The worst part is if you change format to number, you can do math on those cells, so obviously excel is aware of their numerical values. Yet STILL refuses to plot them (actually, you can do math even with "general" format!). – The111 Jul 10 '12 at 18:08

The 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.

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: 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.

  • The original question did not mention any hidden cells. – Vojtěch Dohnal Jun 10 '16 at 13:16

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.