Part of the free Module 5: Excel Formulas and Functions · Function 45 of 102 · Full Excel course
The Excel ISLOGICAL function returns TRUE when a value is a logical value (TRUE or FALSE) and FALSE for anything else, including numbers, text and the words “TRUE” or “FALSE” stored as text. ISLOGICAL in Excel is used to validate checkbox columns, audit imported data and make sure a formula is receiving a real Boolean before acting on it.
ISLOGICAL syntax
=ISLOGICAL(value)Arguments
| Argument | Required | Meaning |
|---|---|---|
| value | Required | The cell, expression or formula result to test. |
Only the Boolean constants TRUE and FALSE, or formulas that return them, count as logical. The number 1, the number 0 and the text “TRUE” are not logical values.
Step-by-step example
=ISLOGICAL(A1)- Excel reads A1. Suppose it contains TRUE (right-aligned, upper-case, as Excel displays Booleans).
- The value is a logical constant.
- ISLOGICAL returns TRUE. If A1 held 10, or the left-aligned text TRUE, the result would be FALSE.
Practical use cases
1. Validate a Yes/No flag column
=IF(ISLOGICAL(D4), "Logical value", "Non-logical value")2. Count real Booleans in a range
=SUMPRODUCT(--ISLOGICAL(B2:B100))3. Detect text TRUE/FALSE after a CSV import
=AND(NOT(ISLOGICAL(A2)), OR(A2="TRUE", A2="FALSE"))Returns TRUE for cells that need converting. Fix them with =A2=”TRUE” in a helper column, or Paste Special > Multiply by 1.
4. Data validation that accepts only TRUE or FALSE
=ISLOGICAL(B2)Set as a custom validation rule so users cannot type “yes” or 1.
5. Safe conversion to 1/0
=IF(ISLOGICAL(C2), --C2, "Invalid")6. Check a formula returns a Boolean before using it in IF
=IF(ISLOGICAL(E2>50), "Test is valid", "Not a comparison")Common mistakes and errors
- Expecting 1 and 0 to be logical – they are numbers. Excel treats them as TRUE/FALSE inside IF, but ISLOGICAL(1) returns FALSE.
- Text Booleans – imported “TRUE” strings look identical but fail the test. Check alignment: real Booleans are right-aligned.
- Testing an error – ISLOGICAL(#N/A) returns FALSE; it does not propagate the error.
- Range in a single cell (legacy Excel) – tests only the same-row cell unless wrapped in SUMPRODUCT.
- Blank cells – ISLOGICAL of an empty cell is FALSE, even though IF treats a blank as FALSE.
Tips and best practices
- Use ISLOGICAL in data validation for flag columns so users cannot type yes, Y or 1.
- Check imports immediately with SUMPRODUCT(–ISLOGICAL(range)) against the expected row count.
- Convert text Booleans in one step with =A2=”TRUE” filled down, then paste values over the original.
- Prefer real checkboxes (Excel 365 Insert > Checkbox, or Form Controls) which store genuine logical values.
- Combine with TYPE (returns 4 for logical) when auditing mixed columns.
Related functions
- TRUE and FALSE – the two logical constants.
- ISNUMBER and ISTEXT – sibling type tests.
- TYPE – returns 4 for logical values.
- IF – the function that consumes logical values.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Does ISLOGICAL recognise the text “TRUE” as a logical value?
No. Only the Boolean constants TRUE and FALSE qualify. Text that spells the same word returns FALSE, which is exactly how you detect badly imported flag columns.
Can ISLOGICAL test many cells at once?
Yes, inside SUMPRODUCT: SUMPRODUCT(–ISLOGICAL(B2:B100)) counts the logical cells. Excel 365 spills ISLOGICAL(range) as an array.
Is 1 or 0 a logical value in Excel?
No. They are numbers, so ISLOGICAL returns FALSE. IF and other logical functions accept them, but they are not Booleans.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.