As already noted, a lot of problems that people come across in Excel are related to formatting. One of the most common issues is when Excel keeps your numbers stored as text, or when you download data and it simply will not convert to numbers. This tends to be a large issue, as you cannot type formulas with this format.
In the example below, we will show how to resolve this issue.
Fix Numbers Stored as Text by Converting Range
If we happen to copy some data in our sheet, or we have our numbers formatted as text for any other reason, Excel will usually tell us so by displaying an error message in a triangle. These cells will also have a little green triangle in a top left corner of a cell, as shown in our example below:
When we hover over the triangle, we will see a couple of options that Excel provides us with:
There are many options, such as Ignore Error, Edit in Formula Bar, etc. but to change the formatting of the first cell, we would have to click simply on the Convert to Number option. However, that would only apply the changes to one cell. This would make the process of changing the entire range pretty painful.
What we need to do to change the entire scope is to select it, and then click on this option on any triangle at our disposal:
Once we click on this option, we should have our cells formatted to numbers:
Fix Numbers Stored as Text with Multiplier
Sometimes, however, we will not have the triangles (nor the green one nor the yellow one) that could be seen in the first example as an indicator that we have an error.
If we would go on and click any of these numbers, we could easily inspect that they are formatted as Text. What we need to do now is input number 1 in any blank cell (make sure that that cell is not formatted as text!). We will store ours in cell H2 and then right-click on it, and copy this cell. Once we do, we will select the entire range that we want to format, right-click and then go to Paste Special option as in the picture below:
On the window that appears, we will choose the Multiply under Operations:
We will click OK and our range will be formatted:
When we use a multiplicator, that makes the contents of our cells transform into their numerical values.
Fix Numbers Stored as Text with Text to Columns
The options that have been presented above are good and practical when dealing with smaller ranges. The problem that might happen with dealing with big data is that Excel can take longer to process, and it will, in some instances, crash. Luckily, there is a simple solution to this problem as well.
Suppose that we have the following range of numbers:
You will notice that our range is formatted as General, not Text but we still somehow have the same issue.
To resolve the issue, all you need to do is select the range, then go to Data >> Text to Columns:
When we click on it, we will choose Next for the first two options, and then simply click Finish. We do not need to change anything in our steps.
The result is as follows: