This guide will go over some methods you can try to fix the Convert to Number error in Excel.
There are some cases where the values in your spreadsheets are stored as text, even though they hold numerical values.
Let’s take a look at a quick example where you might find a Convert to Number error in Excel.
Numbers formatted and stored in cells as text can sometimes cause problems with calculations.
They may also produce confusing sort orders. For example, if we were to sort the values ‘1’, ‘10’, and ‘2’ in ascending order, we would get the result ‘10’, ‘1’, ‘2’ instead of ‘1’, ‘2’, and ‘10’.
Numbers formatted as text are easily spotted since they are left-aligned by default rather than right-aligned. However, this is not the most accurate way to spot text-formatted numbers since cell formatting could force numerical data to align to the left of the cell.
The most reliable way to spot and fix text-formatted numbers is through the Error Checking feature. Cells may have a small green triangle in the upper-left corner, indicating that the value is a text-formatted number.
If you want to convert a specific range into numbers, you may also use the Paste Special option. Excel has a unique feature that allows you to use text-formatted numbers in operations.
We could use Paste Special to multiply every value in a range by 1. Doing so should convert each value to a numerical value.
Now that we know what to do to fix text-formatted numbers in Excel, let’s take a look at what the error looks like on an actual spreadsheet.
Real Examples of the Convert to Number Error in Excel
In the following section, we will provide a few examples where numbers are stored as text rather than a numerical value. We’ll also provide an in-depth explanation of why these errors occur.
In the table below, we have a table of different products. Each product has a corresponding quantity and unit price. We can use these two values to compute the total price of everything in stock.
When we try to sort the table by quantity, we end up with an incorrect sort. Instead of the product with only two stocks coming up first, it’s the product with eleven items in stock.
We are encountering this issue because the values in column C are not numerical. To get the right sort, we’ll have to convert these values into true numbers.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you need to fix the Convert to Number error in Excel, head over to the next section to read our detailed breakdown on how to do it!
How to Fix Convert to Number Error in Excel
This section will guide you through each step needed to convert your value into valid numerical data. The methods you’ll learn here include using the Error Checking tool, using the VALUE function, or performing a mathematical function.
Follow these steps to convert your value into a number:
- First, let’s try to use the Error Checking feature to fix our numerical data. Numerical data stored as strings may be marked with a green triangle that appears on the top left corner of the cell. Select all cells that contain this marker.
- After selecting, click on the yellow ‘!’ icon to reveal a dropdown menu. Click the option labeled ‘Convert to Number’.
- Your range should now be stored as numerical data.
- We can also use the Paste Special feature to fix the Convert to Number error. First, we must place the number 1 on a random cell. Once you’ve typed 1 into the cell, copy the contents of that cell using the shortcut Ctrl + C.
- Next, select the values you want to convert into numbers. In this case, we’ll select the range C3:C8.
- Right-click on the selection and select the Paste Special option.
- In the Past Special dialog box, select Multiply as the operation to perform. Click on OK.
- Your range should now be converted automatically to a numerical value. This works because we’ve multiplied each of these values by 1. Since multiplying any number by 1 retains its value, we used multiplication to convert the text-formatted value to a number.
These are some of the most common methods to fix the Convert to Number error in Excel.
This step-by-step guide should be all you need to fix issues with the Convert to Number error yourself in Excel. Our guide shows how we can handle text-formatted numbers by using the Error Checking tool and the Paste Special option.
The Convert to Number error is just one example of a mathematical error you may encounter in Excel. If you are having difficulty working with Excel formulas and not updating in general, we have our own step-by-step guide that may help with that.
Our website covers plenty of other Excel methods and functions that you can apply to your own spreadsheets. With so many other Excel functions available, you can surely find one that suits your use case.
Do you want to learn more about what Excel can do? Subscribe to our newsletter to look into the latest Excel guides and tutorials from us.