KajoData / Excel
Checking Excel data: ISNUMBER, AND and the limits of validation
I check numeric values, combine conditions and validate date ranges, while keeping a clear distinction between the right data type and trustworthy information.

The amount looks numeric, the date looks sensible, and the report still misses some orders. I start by checking the values stored in the cells. A neat display gives me too little information. Formatting can tidy the screen while the underlying column continues to mix numbers and text.
In this guide I build a small data check with ISNUMBER and AND. I then add questions about the meaning of an amount, the date range and the order identifier. The aim is a useful list of exceptions, together with a clear understanding of what the checks have actually established.
Define the columns before choosing a formula
I use a synthetic order table. Column A contains OrderID, B contains Sales and C contains OrderDate. Row one holds the headings and the data starts in row two. For this exercise, the identifier must be numeric, the amount must be a nonnegative number and the order date must belong to January 2026.
Those are assumptions for the example. Another system might use identifiers with letters and leading zeros. Returns might legitimately carry negative amounts, while a reporting field might include a time of day. I would not want to reject valid records simply because I copied a formula with different assumptions.
I also specify the level of the data: one row represents one order. If an export contains product lines, a repeated order identifier may be expected. Without that distinction, a duplicate check can encourage someone to remove useful information. A function can implement a definition, but it cannot decide what an order means in the business process.
ISNUMBER checks the stored type
The Microsoft documentation explains that ISNUMBER returns a logical result indicating whether a value is numeric. It does not convert a numeric string while checking it. These two formulas therefore produce different results:
=ISNUMBER(407)
=ISNUMBER("407")
The first gives TRUE and the second FALSE. The quotation marks make the second value text. When I reference B2 instead, the result depends on what that cell actually contains. Colour, alignment and the number of displayed decimal places do not answer the same question.
An empty cell, a text value, a logical value and an error value all fail the numeric check. That common outcome is useful for an initial inspection, although it does not explain the cause. A missing amount and a failed calculation usually need different action. For a larger source, I keep separate checks or a reason field so that the exceptions remain interpretable.
Make the example types explicit
The table describes storage types deliberately. A printed date in an article is not a promise about how a particular Excel installation will import it. To create a numeric string for the exercise, enter the amount with a leading apostrophe. The apostrophe tells Excel to treat the entry as text and is not part of the displayed amount.
| Worksheet row | OrderID | Sales | OrderDate | Deliberate type difference |
|---|---|---|---|---|
| 2 | 101 | 100 | 10 January 2026 | none, all values are numeric |
| 3 | 102 | 200 | 11 January 2026 | Sales is the text “200” |
| 4 | 103 | 50 | 12 January 2026 | OrderDate is text |
| 5 | 104 | 0 | 13 January 2026 | none, all values are numeric |
| 6 | 105 | -20 | 14 January 2026 | none, all values are numeric |
For the numeric dates, use DATE, such as =DATE(2026,1,10). This avoids relying on how your regional settings interpret a typed date string. For worksheet row four, deliberately retain text resembling a date. That row should fail the type check even though a person can understand what the text probably means.
Combine three checks with AND
In D2 I enter the combined type check and copy it down:
=AND(ISNUMBER(A2),ISNUMBER(B2),ISNUMBER(C2))
AND combines logical results. All three numeric checks must return TRUE for the combined result to be TRUE. A single FALSE is enough to make the combined condition false. Notice the separate calls to ISNUMBER rather than passing a raw range directly into AND.
For the specified table, D2:D6 contains TRUE, FALSE, FALSE, TRUE and TRUE. The negative amount passes because a negative number is still numeric. Zero passes as well. This column checks types, so these are the expected outcomes. I would name it TypesOK to reduce the chance that someone reads it as full approval of the order.
Match the formula language and references
In Polish Excel, the equivalent expression is =ORAZ(CZY.LICZBA(A2);CZY.LICZBA(B2);CZY.LICZBA(C2)). The English examples use English function names and commas. The Polish examples use Polish names and semicolons. Argument separators depend on regional settings, so a pasted formula may need a different separator without needing different logic.
If the range is an Excel table with the exact headings OrderID, Sales and OrderDate, I can use references to the current row:
=AND(ISNUMBER([@OrderID]),ISNUMBER([@Sales]),ISNUMBER([@OrderDate]))
Microsoft explains these references in its table documentation. The names must match the actual headings. Order Date with a space differs from OrderDate without one. Clicking the relevant cells while writing a formula helps Excel insert the correct reference. For a normal worksheet range, the A2, B2 and C2 version remains appropriate.
Add the amount rule separately
The exercise also requires nonnegative amounts. I write that requirement separately and handle any error produced while evaluating the expression:
=IFERROR(AND(ISNUMBER(B2),B2>=0),FALSE)
IFERROR returns the specified fallback when its expression produces an error. I do not depend on AND stopping evaluation after its first false argument. If the source cell contains an error, the outcome of this validation rule should still be FALSE.
The row containing -20 now fails. Before changing it to 20, however, someone needs to establish what the record represents. It might be a correction that belongs in a separate category. A rule identifies a departure from an agreed requirement. It does not give me permission to reverse the sign of a source amount simply to make an indicator turn green.

A numeric date still needs boundaries
Excel supports the 1900 and 1904 date systems. Dates have numeric representations and formatting determines how they appear. ISNUMBER alone cannot distinguish a meaningful order date from an arbitrary number given a date format. It also cannot confirm that the value belongs to the reporting period.
For January I use an inclusive lower boundary and an exclusive upper boundary:
=IFERROR(AND(ISNUMBER(C2),C2>=DATE(2026,1,1),C2<DATE(2026,2,1)),FALSE)
This interval also includes times during the last day of January. If the source stores an order timestamp, using midnight at the beginning of 31 January as an inclusive upper boundary could exclude the rest of that day. If the requirement is dates without a time component, I make that a separate rule. I first establish what the source field represents and which reporting time zone applies.
Formatting does not establish successful conversion
Changing a cell to a numeric format is insufficient evidence that its text has been converted. After a repair, I check the type again and compare the value with the original. For amounts, I pay attention to decimal separators, grouping separators and spaces. A comma can mean different things under different regional conventions.
I also avoid claiming that every entry with a space must always become text. The outcome depends on how the value was entered or imported. In this example the types are specified explicitly, making the expected results unambiguous. In a real file I need to inspect the current state before choosing a transformation.
The same principle applies to a date stored as text. An unambiguous source format can support an agreed conversion rule. An ambiguous day and month order can instead produce a technically valid date with the wrong meaning. That situation deserves clarification at the source rather than a guess disguised as cleaning.
Filter exceptions while preserving the complete source
Once the checks exist, I filter for false results. I inspect whole records and record the reason: missing amount, text in a numeric field, date outside the interval or another failed requirement. This gives the source owner a more useful request than a message saying that Excel is somehow wrong.
I keep the original export and agree where the corrected version will be produced. An original amount should not silently disappear under an overwritten value. For a small task, a clear corrected-value column and a reason may be enough. A recurring process benefits from an agreed repair location and someone who confirms the interpretation.
At the same time, I compare record counts before and after handling exceptions. If five orders become three, the report needs to explain the remaining two. A green result inside an accepted subset says nothing about records removed earlier. It is remarkably easy to eliminate every warning by eliminating the rows that caused it.
Assess the effect on the report
Two problematic rows might account for a small or a substantial share of the report’s value. Alongside the exception count, I assess their effect on the measures. If an amount is text and I cannot safely interpret it, I do not silently replace it with zero. I state that the complete value remains unresolved.
In the synthetic table, the numeric amounts 100, 0 and -20 are individually understandable. Their sum should not automatically be labelled revenue from valid orders: the business rule rejects -20 and two other records still need type issues resolved. The sequence of decisions affects the meaning of the reported result.
When communicating with a report user, I describe the period, the included records and the kinds of exclusions. If the report is provisional, I say so directly. This lets the user judge whether a comparison with the previous month is appropriate or whether the missing information must be resolved first.
Distinguish detection from entry restrictions
A helper column evaluates data that already exists. Excel also has Data Validation, which supports allowed values, ranges and messages during entry. These approaches can work together, especially when a team manually completes a shared workbook.
I would configure allowed dates and amounts according to the agreed requirements. After receiving a file, I would still inspect the full current range. Entry restrictions help reduce mistakes at one stage, while reporting needs an assessment of the data after import and subsequent changes.
This separation clarifies responsibilities. The person entering a record receives a useful prompt, while the analyst sees exceptions in the current source. Neither needs to pretend that a single formula replaces an agreed definition. The right correction still depends on what the information means in the process.
Check identity and completeness beyond numeric types
A numeric OrderID may be repeated, refer to an unknown order or contain the wrong number. ISNUMBER only establishes the type. Under the one-row-per-order assumption, I also need a uniqueness check. When combining systems, I need to understand whether the identifiers match their expected counterparts.
If an identifier has leading zeros, I do not automatically convert it to a number. The code 0012 and the number 12 may seem equivalent to a reader, while the source system requires the complete code. The check must reflect that agreement. Revising an incorrect requirement can be more valuable than making a formula increasingly elaborate.
I also ask whether the export covers the complete period and all expected organisational units. A table with perfectly consistent types might omit an entire branch. Looking only at existing cells cannot reveal everything that should have been present. I need an expected list or another way to confirm source completeness.
Exercise: design the exceptions before the report
Create the five specified rows and add separate identifier, amount and date checks. First predict the outcomes on paper. Then replace the text amount with a number and identify which checks should change. The point is to understand the requirement before building a larger report around it.
Next, add an order dated 1 February. Its numeric type should pass while the January range rule should fail. Finally, change the requirement so that negative corrections are allowed. Decide whether to revise the amount rule or separate orders and corrections into categories. Explain the decision in one sentence, including how it affects interpretation.
To continue, read about merging queries in Power Query. I consider the check useful when I can explain what was inspected, what still needs investigation and how that unresolved state affects the report. A logical result becomes valuable when someone can act on it sensibly.
Key takeaways
Does ISNUMBER prove that an order date is correct?
It checks whether the stored value is numeric. A numeric date can still fall outside the reporting period or contain an unexpected time component.
What are the Polish names for ISNUMBER and AND?
They are CZY.LICZBA and ORAZ. English examples use commas and Polish examples use semicolons; argument separators depend on regional settings.
Should I delete every row that returns FALSE?
FALSE means that a stated rule was not satisfied. Investigate the cause and agree how to handle it, preserving the original data and the effect on reporting.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
