A very common problem with Excel is where numbers are seen by Excel as text. So if you try and add them up they refuse or the numbers sort in a strange way.
Table of contents
Consistent ‘format’ issues with numbers
A common cause is that another program has exported it as text. These are generally easier to fix because the ‘error’ is consistent. Once you find the problem, it will sort out all the issues (look at our Converting Text to Numbers course ).
Inconsistent number/ text issue?
A more difficult problem is where the reason is inconsistent and this is often because of human input. See below. On my computer (note that this issue is heavily dependent on what the regional settings are on the particular computer being used) it uses spaces to separate the thousands and periods (.) to show decimals. So any number that doesn’t use this convention will be treated as text.
Note however how hard it is to spot which are and aren’t numbers. If you need to do a manual inspection, then make changes to the number format. Remember, only the numbers that Excel sees as numbers will be formatted, so anything that doesn’t change won’t be formatted.
Add 10 decimals to format
So below we have put a crazy number of decimals as our number format. Anything that doesn’t have that many decimals needs to be fixed.
Convert number format to a currency you never use
Another option is to format it as a currency that you will never find in your set of numbers. If you can find one that shows the symbol far left even better. So below anything that doesn’t have the Chinese (PRC) symbol is not a number.
Unfortunately for this type of input, with no consistent error, you may need to work through each of the problem cells and determine what it should be.
Want to learn more about Microsoft Excel and using it to quickly finish your work? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Green triangle in MS Excel cell- understand, control, remove