Top 10 ways to Clean your Data in Microsoft Excel

Fixing Numbers Stored as Text

Sometimes when you import data or receive data from another source, the numbers might be converted to text. When you try to sum them, nothing works. That is because Excel will not sum numbers stored as text.

When numbers in a sheet are being stored as text, Excel lets you know by placing a green triangle in the cell (if File, Options, Formulas, Error Checking, Enable Background Error Checking is selected). When you select the cell and click the warning sign that appears, Excel informs you that the number is being stored as text, as shown in Figure 3.19. It then gives you options for handling the number, such as Convert to Number or Ignore Error.

Figure 3.19. With Background Error Checking enabled, Excel informs you if a number is being stored as text.

If you have a worksheet with thousands of cells, it will take a long time to convert them all to numbers. Three options for doing a larger-scale conversion are covered in the next sections.

Using Convert to Number on a Range

One option for converting multiple cells into numbers is to use the information drop-down that Excel has provided:

1. Select the range consisting of all the cells you need to convert (making sure that the first cell in the range needs to be converted). The range can include text and other numerical values, as long as it doesn’t include cells you do not want to be converted to numbers.
2. Click the warning symbol in the first cell.
3. From the drop-down, select Convert to Number, and all cells in the selected range will be modified, turning the numbers to true numbers.

Using Paste Special to Force a Number

If you have the Background Error Checking disabled and don’t see the green warning triangle, try this method for converting cells to numbers:

1. Enter a 1 in a blank cell and copy it.
2. Select the cells containing the numbers, right-click and select Paste Special, Paste Special.
3. From the dialog box that opens, select Multiply, and click OK.The act of multiplying the values by 1 forces the contents of the cells to become their numerical values.

Using Text to Columns to Convert Text to Numbers

In step 3 of the Text to Columns wizard, you select the data type of a column. You can use this functionality to also correct numbers being stored as text. To convert a column of numbers stored as text to just numbers, follow these steps:

1. Highlight the range of text to be converted.
2. Go to Data, Text to Columns.

Click Finish. The numbers are no longer considered numbers stored as text.

Also read Customize Excel’s pivot table

Go To Main Page