You’re working on an MS Excel spreadsheet made by someone else, or it’s something you haven’t worked on for quite some time. But suddenly, things don’t seem to be adding up. One or two of your formulas don’t seem to be working and you find out that one or more of the cell values aren’t being recognized as a number.
You’re here now because you want to know how to fix the issue with Microsoft Excel not recognizing numbers in worksheet cells instead it recognizes them as texts. Read on to know why and how easy it is to fix.
Consider the simplistic example above. All the values in the Net Pay column look like numbers, and we have ten employees with a little over 10,000 each. So why is the total amount less than a hundred grand, even when using the simple SUM function? It means we have a number that doesn’t behave like one. Upon simple inspection, you’ll notice something odd in the pay for employee number three.
Reasons & Scenarios for Excel to Recognize & Identify Numbers as Text
There are a few reasons and varied scenarios why a value that looks like a number isn’t recognized as a number in MS Excel spreadsheet. Instead, it is identified as text format.Â
- As in the above example, the number was mistakenly formatted as text using a single quote before the number. (‘10150.01) It’s a simple way to represent a number as a text value. That green notch may not even be present as someone may have checked it out and chosen to dismiss the issue by choosing Ignore Error.
- Another example is when the sheet was made by someone who formatted the entire thing as text and forgot to format the numbers column as numbers.
- Or perhaps a text-to-spreadsheet import process has gone wrong.
- And lastly, the values were copied from a website and the user didn’t paste them the correct way.
How to Fix Microsoft Excel Not Recognizing Numbers in Cells & Formulas
- One quick solution is to check whether the values are indeed numbers or text just by going to a cell and looking at the formula bar. You may see that single quote (‘) before the number. Simply remove the quote and the problem is solved.
- What if there’s no quote on the number? This is the part where someone else may have chosen to ignore the error that the number represented is formatted as text. But if you notice that notification triangle at the left of the cell, you’ll see the true nature of the value.
- You will have to format the cell or the entire column from Text to Numeric. This is done by right-clicking on the cell (or highlighting the entire column, then right-clicking) and choosing Format Cells. You may choose between Number or Currency, then click on OK.
- If Converting to a Number doesn’t seem to work, then the number may be stored in Excel as Text. You will notice it by the persistent appearance of the notification triangle. It’s pleading for your attention. So please hover your mouse until the dropdown triangle appears. You’ll see that the number is indeed stored as Text. Solve the problem by choosing Convert to Number.
So, folks here’s hoping we’ve solved your problem on numbers recognized as texts in your Microsoft Excel spreadsheet documents. Feel free to look around for more answers to your issues or tricks to help make using Excel a lot easier.
