- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.
){kind=link}
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:
- 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.
- Click the warning symbol in the first cell.
- 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:
- Enter a 1 in a blank cell and copy it.
- Select the cells containing the numbers, right-click and select Paste Special, Paste Special.
- 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.
TIP
You can also copy a blank cell and use the Add option instead of Multiply.
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:
- Highlight the range of text to be converted.
- Go to Data, Text to Columns.
- Click Finish. The numbers are no longer considered numbers stored as text.
NOTE
For more information on the Text to Columns wizard, refer to the section “Working with Delimited Text” earlier in this chapter.