Spot invalid imported data with the help of a custom Excel function

You’re getting some unexpected data type errors from your analysis of imported data. Examination of the first few cells in the worksheet shown in Figure A confirms that some data has been entered as text rather than numbers.

Figure A

Rather than manually examine each cell, you can create a function that will mark the invalid cells for you. Follow these steps:

1. Open the worksheet containing the imported data and press [Alt] + [F11].
2. Go to Insert | Module.
3. At the prompt, type the following code:

Function ISNUM(range) As Boolean
If range.Value <> "" Then
    ISNUM = IsNumeric(range.Value)
    End If
End Function

1. Press [Alt] + Q (Figure B).

Figure B

1. Select the cells containing the imported data. (For this example, we selected A1:A6.)
2. Go to Format | Conditional Formatting, click the drop-down arrow, and select Formula Is. In Excel 2007, select the Home tab, click Conditional Formatting in the Styles group, click New Rule, and select Use A Formula To Determine Which Cells To Format.
3. Enter the following formula in the formula text box:

=ISNUM(A1)=True (Figure C).

Figure C

1. Click the Format button.
2. Click Green under Color.
3. Click OK twice.

Now the cells that contain numerical data are marked in a green font (Figure D). All others need to be reentered or deleted.

Figure D

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.