This workbook was sent to us by one of our customers:
He tried to create a chart from the values on the workbook, but the chart appeared completely blank.
The reason this happened is that the values in column B are being stored as Text instead of as Numbers.
If Excel charts are unfamiliar to you, you could benefit greatly from our Essential Skills Books and E-books, which explain Excel charts in great depth.
Excel can store numbers as text
When you type a number into a cell, Excel usually recognizes it as a number and internally stores it as one. Excel then knows that it is a number and can use it in charts and other mathematical calculations.
If the cell has been formatted as Text, Excel won’t do this. Instead it will store whatever is entered as a piece of text that can’t be used in calculations. A little green arrow is usually displayed in the corner of the cell in these cases, along with a warning that the number has been stored as text and an offer to convert it into a ‘real’ number.
This error message can be disabled so you are not guaranteed to see it in cases where numbers are stored as text.
Converting numbers stored as text back into numbers
Excel can be stubborn about numbers stored as text. Simply using the drop-down menu on the Ribbon to format the cells as General or Number won’t work – these only affect how the values are treated at the point they are entered. Copying and pasting the cells as values also won’t work.
One solution is to use the VALUE function in another column. The VALUE function’s entire purpose is to convert text into values.
Another solution is to use the Convert to Number drop-down menu shown above. This will work with multiple cells, but will only appear if you have Error Checking enabled. Error Checking and the VALUE function are covered in more depth in our Expert Skills Books and E-books.
Once the numbers have been converted into ‘real’ numbers, you should be able to create a chart from them with no problems.
You can download a sample workbook showing the VALUE function solution in action.
