Excel ISREF Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 50 of 105 · Full Excel course

The Excel ISREF function returns TRUE when its argument is a cell or range reference and FALSE when it is a value, text, number or error. ISREF in Excel does not care what the referenced cell contains; it checks whether the thing you passed is a reference at all. Its main practical job is testing whether INDIRECT or a named range resolves to a valid location.

Table of Contents

ISREF syntax

=ISREF(value)

Arguments

Argument	Required	Meaning
value	Required	The item to test: a reference such as A1 or Sheet2!B2:C5, a named range, a function that returns a reference (INDIRECT, OFFSET, INDEX), or a constant.

Step-by-step example

=ISREF(A1)

Excel looks at the argument, which is the reference A1.
A1 is a reference regardless of whether it is empty, holds text or shows an error.
ISREF returns TRUE. Compare =ISREF("A1"), which passes the text string A1 and returns FALSE.

Practical use cases

1. Check a sheet exists before referencing it

=ISREF(INDIRECT("'"&B1&"'!A1"))

Type a sheet name in B1. TRUE means the sheet exists; FALSE means INDIRECT could not build the reference. Use it to avoid #REF! in consolidation formulas.

2. Validate a named range

=ISREF(SalesData)

Returns TRUE if the name is defined and points to a range. If the name was deleted, the formula shows #NAME? instead, which is itself a useful signal.

3. Test a text address entered by a user

=IF(ISREF(INDIRECT(C2)), INDIRECT(C2), "Invalid reference")

4. Distinguish references from values in a UDF or audit

=ISREF(OFFSET(A1, 2, 0))     -- TRUE
=ISREF(INDEX(A1:A10, 3))     -- TRUE (INDEX returns a reference)
=ISREF(SUM(A1:A10))          -- FALSE (SUM returns a number)

5. External workbook check

=ISREF(INDIRECT("'[Budget.xlsx]Summary'!A1"))

TRUE only while Budget.xlsx is open; INDIRECT cannot reach closed files.

Common mistakes and errors

Expecting ISREF to read a cell’s content – a cell containing the text B2 is still just a cell; ISREF(A1) is TRUE because A1 is a reference, not because of what it holds. Wrap the text in INDIRECT to test it as an address.
ISREF(#REF!) versus a cell showing #REF! – ISREF of a broken reference literal is FALSE, but ISREF(A1) where A1 displays #REF! is TRUE, because A1 itself is a valid reference.
Deleted names – produce #NAME?, which ISREF does not trap. Test the name inside INDIRECT if you need a clean TRUE/FALSE.
Empty cells – ISREF of an empty cell is TRUE. Use ISBLANK to test emptiness.
Closed workbooks – references built with INDIRECT to closed files return FALSE even if the file and sheet exist.

Tips and best practices

Use ISREF(INDIRECT()) as a sheet-exists test before building consolidation formulas.
Pair with a drop-down of sheet names so users pick valid targets and ISREF rarely returns FALSE.
Do not use ISREF to check content; ISBLANK, ISNUMBER and ISERROR do that.
Keep source workbooks open when validating external references; INDIRECT cannot see closed files.
Remember that deleted names give #NAME?, so wrap the name in INDIRECT(“Name”) for a clean TRUE/FALSE.