ISREF Function

What is the ISREF Function?

The ISREF function in Excel evaluates whether the given value is based on a cell reference or not.

Building huge Excel models can be strenuous. Once you are halfway through, you start judging your objective of why you are building the model or what you are even doing.

This can easily lead you to make errors, such as interchanging hardcoded values with referenced values. Thus, knowing a tool that helps you identify whether a cell's value is a hardcoded value or a referenced value is essential.

The ISREF function allows you to evaluate such values and returns a boolean value to confirm the presence of a cell reference.

In this article, we will see the ISREF function and a couple of examples to understand it better.

Understanding the ISREF function

The ISREF is categorized as an Information function that tells whether the value is a cell reference or not.

The function returns the result as a boolean value, i.e., if it is a reference value, then the function evaluates to TRUE or else returns the result as FALSE.

Generally, one would assume that the function returns the result as TRUE if you reference a cell that contains a referenced value. In contrast, any cell with a hardcoded value will return the result as FALSE.

However, in reality, the function works quite differently.

If you reference any cell in the ISREF function, the function will return the result as TRUE, whereas if you input a hardcoded value, the function returns the result as FALSE.

ISREF function Formula

The syntax for the function is:

=ISREF(value)

Where,

value - (required) any hardcoded or referenced value that will be evaluated.

How to use the ISREF Function in Excel?

In the example, we will explore both the possibilities of the function returning the result as TRUE as well as FALSE.

a. When the function evaluates to FALSE

Perhaps this side of the horizon is far easier to understand while using the function. 

The entire theory of how the function works is based on its innate ability to ignore the cell value and judge whether it is a hardcoded value or a cell reference.

In this case, we will see two scenarios - when you reference a cell containing a hardcoded value and another where you directly hardcode a value in the ISREF function.

Suppose that you have the data as illustrated below:

In cell C4, we have the hardcoded value as 'Microsoft Inc'. By using the formula =ISREF(C4) in cell D4, surprisingly, the result is TRUE.

In this case, the function saw the cell reference made in the formula =ISREF(C4) and not the cell value in cell C4. This is why the function returns the result as TRUE.

However, if the formula is =ISREF("Microsoft Inc") in cell D5 then the function evaluates to FALSE since it is a hardcoded value in the ISREF function.

In reality, the function returning the result as FALSE in scenario two fulfills the true purpose of the ISREF function.

b. When the function evaluates to TRUE

If you have understood when the function returns the result as FALSE, it becomes easy to understand when it would return as TRUE.

Any references you make in the formula despite the value being hardcoded in the source cell will return as TRUE in the ISREF function.

Suppose that the data is as illustrated below:

In cell C4, we have a hardcoded value as '123123', whereas in cell C5 the value is a cell reference to cell C4, i.e., =C4.

Generally, anyone would think the function would evaluate to TRUE only for scenario TRUE. However, that is incorrect. As we have stated multiple times, the function does not check the cell value but rather how the value is represented in the function.

We will use the formula =ISREF(C4) in cell D4 and drag it down to cell D5, which gives the result.

Since both were cell references in the formula, the function evaluates to TRUE. If the formula were something like =ISREF("123123"), the function would only return the result as FALSE.

Alternatives to the ISREF function

Let's agree that the function doesn't do what it should - check whether the given cell value is a reference or a hardcoded value.

In this case, you can use the special Go To Tool that will help you easily find the hardcoded values and the referenced value in the entire sheet.

Yes, you read that right.

To use the tool, all you need to do is follow a few simple steps. They are

• Press the F5 key on the keyboard or even the Ctrl + G key combination
• This will open up the dialog box as illustrated below:

• Finally, click on the Special button that opens up the window below:

Now, selecting the radio button for the 'Constants' will select all the hardcoded values in the spreadsheet, whereas selecting the radio button for 'Formulas' will select all the cells where references are made.

But the only downside is that it will select the cells where hardcoded values are used in the function. So, for example, we saw that when we use formula =ISREF("123123"), we get the result as FALSE. 

This is contrary to what we wanted with the Go To Special Tool, but it is useful for finding the referenced and hard-coded cell values.

Researched and Authored by Akash Bagul | LinkedIn

Reviewed & Edited by Ankit Sinha | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AND Function
• PI Function
• SLN Function
• T Function
• XOR Function