In this lesson, I’ll demonstrate 4 effective approaches to fix Microsoft Excel not recognizing numbers in cells. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Issues with Excel Not Recognizing Numbers in Cells
We should know when Excel does not recognize Numbers. In any Excel worksheet if we use values from other sources by directly copying we may face this problem. This is because Excel recognizes values as they are in the source. So if the source assigns the number in text format our worksheet will store the Number also as text. But it will let us know that the numbers are in text format by aligning them to the left side as texts are aligned to the left.
Learn to Fix If Excel is Not Recognizing Numbers in Cells with These 4 Possible Solutions
Here in this article, we will learn how to set a minimum and maximum value in Excel using different approaches. To be exact, I’ve provided a total of 4 methods down below.
1. Use VALUE Function
Here, we will learn how to fix Excel not recognizing numbers in cells using the VALUE function. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- Here we are using a function, so our results will be delivered in a different cell. So we have to select a cell where we will apply the following formula.
=VALUE(B6)
- After that, hit Enter and copy down the formula along the column to get all the values.
2. Try Paste Special Feature
In this section, we will be using the Paste Special feature of Excel. This tool is very handy and has a versatile field of application. Follow the instructions to format cells that are not recognized using the Paste Special feature.
⬇️⬇️ STEPS ⬇️⬇️
- First, select a blank cell and press Ctrl+C to copy.
- After that, select all the cells which have errors or do not recognize numbers, right click on them and select Paste Special option.
- Select the All except borders option from the Paste Then select Add option from the Operation section, and hit OK.
- Here is the result. As all the values are right aligned, it means we changed the format to a number.
3. Format to General or Number
This is the easiest way to format cells according to your wish. Using the basic tools with the help of a few clicks. Formatting from the toolbar you can have different options that can meet your demand. Remember this method is not applicable if the data has caution signs. In this case you have to solve that error first.
⬇️⬇️ STEPS ⬇️⬇️
- First, select all the data that are not recognized as Numbers.
- After that, go to the Home tab from the ribbon. You will see an option saying Text in the Number Click the Dropdown button for options.
- Now you can either select General or Number.
- You can also select the More Number Formats for tons of customization features to format cells.
- The Results are shown below. We chose Number. You can see all the values are right aligned which means they are in Number format.
4. Use the Error Sign to Convert
This is the most convenient and shortest way if you have one or two errors but a lengthy process if you’re dealing with a lot of scattered data having error signs. Follow the instructions to learn. The error occurred because our data started with parentheses in the source from where we copied the data.
⬇️⬇️ STEPS ⬇️⬇️
- Select all the cells that show errors.
- You’ll see a Caution sign on the upper right side of the selected range.
- Click on the sign and you’ll see options popping up.
- Just select the Convert to Number option and you just solved the problem.
- The Results are in the picture below.
What to Do If Excel Is Not Adding Numbers?
Let’s say we want to add multiple numbers from a dataset and the numbers are not adding up. Because the cells have value but those are not in number format. This is closely related to the main problem of the article. Here I will show you how to add up numbers that are not in the right format.
⬇️⬇️ STEPS ⬇️⬇️
- First, select all the data that are not recognized as Numbers.
- After that, go to the Home tab from the ribbon. You will see an option saying Text in the Number Click the Dropdown button for options.
- Now you can either select General or Number.
- You can also select the More Number Formats for tons of customization features to format cells.
- The Results are in the picture below. We chose Number. You can see all the values are right aligned which means they are in Number format.
- Now go to the Data tab from the ribbon and select Text to Columns.
- Don’t change anything just select Finish.
- Now select the whole dataset and click on the AutoSum option from the Home tab. The results are as follows.
📄 Important Notes
🖊️
Use shortcuts to reduce the time for instance F4 for absolute value
🖊️
While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.
🖊️
Be careful while defining criteria to be accurate as the dataset.
🖊️
Never delete a sheet before saving.
📝 Takeaways from This Article
📌
Use of VALUE functions.
📌
How to deal with dataset that have caution sign.
📌
Setting up copied dataset as usable.
📌
Formatting cells as per demand.
📌
A different use of Paste Special feature.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this Excel not recognizing numbers in cells lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.
Related Articles
- Convert Degrees Minutes Seconds to Decimal Degrees in Excel
- 6 Ways to Convert Percentage to Decimal in Excel
- Convert Degrees Decimal Minutes to Decimal Degrees in Excel