When working with Excel, especially during data imports, errors and inconsistencies are common. Some fields may contain numbers stored as text, missing values, or errors caused by formulas. To clean and validate your data, Excel provides ISNUMBER, ISTEXT, and ISERROR functions. These functions are simple yet powerful tools to strengthen your Excel validation skills and ensure your datasets are reliable.

In this guide, we’ll cover:

  • What ISNUMBER, ISTEXT, and ISERROR do.
  • Step-by-step instructions on how to use them.
  • A sample dataset to practice.
  • 20 real-world problems arranged from Basic → Expert level.
  • Solutions with exact formulas.
Excel_Validation_Skills_Practice_ISNUMBER_ISTEXT_ISERROR.xlsx

Why ISNUMBER, ISTEXT, and ISERROR Improve Your Excel Skills

  • ISNUMBER(value): Returns TRUE if the value is a number, otherwise FALSE.
  • ISTEXT(value): Returns TRUE if the value is text, otherwise FALSE.
  • ISERROR(value): Returns TRUE if the value is an error (like #DIV/0!, #N/A, or #VALUE!), otherwise FALSE.

Why Useful?
When importing data from CSV files, databases, or external systems, not all values come in clean. These formulas let you quickly validate and clean your data by checking whether a value is the expected type.

Step-by-Step Guide to Strengthen Your Excel Skills

  1. Enter data in a column (e.g., numbers, text, blank cells, and error values).
  2. In the next column, apply formulas:
    • =ISNUMBER(A2) → Checks if A2 contains a number.
    • =ISTEXT(A2) → Checks if A2 contains text.
    • =ISERROR(A2) → Checks if A2 is an error.
  3. Copy formulas down the column to validate all rows.
  4. Use filters or conditional formatting to highlight inconsistent data.

Practice Dataset for Excel Skills Training

Here’s a small dataset you can copy into Excel to practice:

A (Imported Data)
123
456
ABC
#DIV/0!
789
XYZ
#N/A

Now try:

  • =ISNUMBER(A2)
  • =ISTEXT(A2)
  • =ISERROR(A2)

20 Real-World Excel Skills Practice Problems

We’ll break these into 4 groups – Basic, Intermediate, Advanced, Expert. Each problem builds your Excel skills with real-life practice.

Basic Practice Problems (Level 1)

  1. Check if cell B2 contains a number.
    • Formula: =ISNUMBER(B2)
    • Solution: Returns TRUE if B2 has a number, otherwise FALSE.
  2. Check if C5 contains text.
    • Formula: =ISTEXT(C5)
    • Solution: If C5 = “Hello”, result is TRUE.
  3. Find out if D3 has an error.
    • Formula: =ISERROR(D3)
    • Solution: If D3 = #DIV/0!, result is TRUE.
  4. Highlight all numeric cells in column A.
    • Conditional Formatting → Formula: =ISNUMBER(A1)
    • Solution: Excel highlights only the number values.
  5. Count how many cells in column A are numbers.
    • Formula: =SUMPRODUCT(--ISNUMBER(A1:A10))
    • Solution: If column A has 5 numbers, result = 5.

Intermediate Practice Problems (Level 2)

  1. Check if imported IDs (Column A) are all numbers.
    • Formula: =AND(ISNUMBER(A2:A10)) (Array formula in older Excel).
    • Solution: Returns TRUE only if every row is numeric.
  2. Validate if column B contains only text (names).
    • Formula for each row: =ISTEXT(B2)
    • Solution: TRUE if B2 is text, FALSE otherwise.
  3. Identify rows with errors in column C.
    • Formula: =ISERROR(C2)
    • Solution: Returns TRUE for cells like #N/A or #DIV/0!.
  4. Count how many rows have errors in column C.
    • Formula: =SUMPRODUCT(--ISERROR(C2:C20))
    • Solution: If there are 3 errors, result = 3.
  5. Flag rows where numbers are incorrectly stored as text.
  • Formula: =AND(ISTEXT(A2),ISNUMBER(VALUE(A2)))
  • Solution: TRUE for entries like "123" stored as text.

Advanced Practice Problems (Level 3)

  1. Combine ISNUMBER with IF to mark valid phone numbers.
  • Formula: =IF(ISNUMBER(A2),"Valid","Invalid")
  • Solution: If A2 = 9876543210, output = Valid.
  1. Check for blank cells vs. text vs. numbers in column B.
  • Formula: =IF(A2="","Blank",IF(ISNUMBER(A2),"Number","Text"))
  • Solution: Returns “Blank” / “Number” / “Text”.
  1. Remove errors from column C using IF+ISERROR.
  • Formula: =IF(ISERROR(C2),"",C2)
  • Solution: Replaces error with a blank cell.
  1. Find all rows with non-text values in column D.
  • Formula: =NOT(ISTEXT(D2))
  • Solution: TRUE if not text (number or error).
  1. Validate product codes that should always be text (e.g., ABC123).
  • Formula: =ISTEXT(A2)
  • Solution: Ensures imported product codes aren’t numbers.

Expert Practice Problems (Level 4)

  1. Create a clean import column that replaces errors with “Check Data.”
  • Formula: =IF(ISERROR(A2),"Check Data",A2)
  • Solution: Replaces #N/A with “Check Data”.
  1. Ensure numeric IDs are not mistakenly imported as text.
  • Formula: =IF(AND(ISTEXT(A2),ISNUMBER(VALUE(A2))),"Fix","OK")
  • Solution: Flags text-stored numbers for correction.
  1. Count valid numeric entries excluding text and errors.
  • Formula: =SUMPRODUCT(--ISNUMBER(A2:A20))
  • Solution: Gives exact count of true numeric values.
  1. Find rows where both number and text appear (mixed values like “123ABC”).
  • Formula (conceptual): Use MID, ROW, and ISNUMBER inside an array formula.
  • Solution: Identifies mixed entries for cleanup.
  1. Build a validation column that shows: “Number / Text / Error / Blank.”
  • Formula: =IF(A2="","Blank",IF(ISERROR(A2),"Error",IF(ISNUMBER(A2),"Number","Text")))
  • Solution: Gives a clear label for each cell type.
Excel_Validation_Skills_Practice_ISNUMBER_ISTEXT_ISERROR.xlsx

Next Steps to Grow Your Excel Skills Beyond Validation

By practicing these problems, you’ve strengthened your Excel skills in data validation using ISNUMBER, ISTEXT, and ISERROR. These formulas are especially handy when importing messy data from different systems.

👉 Next Step: Continue practicing with larger datasets. Once you’re confident, explore automation with tools like PivotXL, which streamline Excel validation and reporting even further.