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

Any time you have to manually review worksheet data looking for errors, something is going to slip through. Here's one handy safety net: a user-defined function that will flag one type of invalid data for you.

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.

Editor's Picks