Excel Information Functions: Simplifying Data Analysis - Train Excel

Excel’s information functions are like a Swiss Army knife for your spreadsheets – they come in handy more often than you’d think. When I’m sifting through data or setting up my worksheets, these tools provide valuable insights about a cell’s contents, formatting, location, and more. Take the ISOMITTED function, for example; it’s super useful for checking if a function argument is omitted in newer Excel versions. It’s fascinating how each version of Excel builds on this foundation with functions like SHEET and SHEETS, introduced in Excel 2013, which respectively return the sheet number of a reference and the count of sheets in a reference.

Dealing with functions is one thing, but figuring out their temperaments is another. I’ve noticed that functions like INDIRECT, which returns the reference specified by a text string, are volatile. This means they recalculate whenever a change is made in the worksheet. This brings us to the concept of recalculation mode – Excel’s way of determining when to recalculate formulas. By understanding which functions are volatile, I can optimize the performance of my active worksheets, especially with complex workbooks where the current recalculation mode can significantly impact computing resources. If you’re curious to see these functions in action, there are videos and sample files out there that can showcase just how these Excel features tick.

1. CELL Function

Syntax

CELL(info_type, [reference])

Arguments

info_type: The info I need about the cell, like its format, contents, or other attributes.
[reference]: The specific cell that I’m checking out.

Notes

Remember, if I’m tweaking the cell’s format and need to see that reflected in the CELL function, I gotta hit recalculate on the worksheet.

Example

So I tried using the CELL function to grab details about a cell, right? It’s pretty sweet – I can mix it with other functions to get the lowdown on cell characteristics. Here’s a picture to show you what I mean:

2. INFO Function

Syntax

INFO(type_text)

Arguments

type_text: The specific information you want to retrieve about your system.

Notes

The INFO function supports 7 parameters to glean details such as system directory, operating system version, and more.

Example

I once utilized INFO with each parameter to gain insights into my operating system. Here’s how you might do it:

3. ISBLANK Function

Syntax

=ISBLANK(value)

Arguments

value: The reference to the cell you’re checking.

Notes

I should mention that the ISBLANK function won’t be tricked by spaces or formatting. It’s looking for absolutely anything in a cell, and if there’s something there, even a space character, ISBLANK lets you know by returning a FALSE.

Example

When I’m organizing data, I like to use ISBLANK to quickly identify any cells I’ve missed. For example, to check cell A1, I use:

=ISBLANK(A1)

If I want to get fancy, combining it with an IF statement like so:

=IF(ISBLANK(F1), "Hey there, you missed me!", "Good job, you filled me in!")

And here’s a visual to see it in action:

4. ISERR Function

Syntax

ISERR(value)

Arguments

value: This is what I’m checking for errors. It could be a piece of direct data or a result spit out from another function.

Notes

When I use ISERR, what I’m looking for is pretty specific. It tells me whether there’s an error like #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, or #VALUE! in the value I’m scrutinizing. It gives me the cold shoulder for #N/A errors though—that’s another function’s territory (ISNA or ISERROR).

Example

I’ve played around with ISERR in different scenarios:

When combining ISERR with IF, I can get a custom message popping up if a cell’s got errors. This trick lets me flag problematic data quickly without breaking a sweat.
I also like using ISERR to check a bunch of cells all at once. By tossing it into an array formula, I can sift through a range and catch every little hiccup without flipping through cells one by one.

5. ISERROR Function

Syntax

ISERROR(value)

Arguments

value: The cell reference or expression to check for an error.

Notes

I can use ISERROR to detect any type of error value in Excel: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Works with both direct cell references and formulas that might result in an error.

Example

Here’s a situation where I combine ISERROR with IF to output custom text when an error is found in a cell:

And in this instance, I’m applying ISERROR across an array to pinpoint errors within a range:

6. ISEVEN Function

Syntax

ISEVEN(number)

Arguments

number: The value I check to determine if it’s even.

Notes

If I put in text instead of a number, I’ll get an error: #VALUE!
Decimals get dropped, so just the integer part counts.

Example

When I use different arguments, here’s what happens:

A value like 2 returns TRUE since it’s even.
Negative numbers, like -4, work just like positives.
Zero? Yep, that’s considered even, too.
For something like 2.5, the decimal is ignored—only the 2 is checked.
And dates are just numbers to Excel, so I can check them in the same way.

7. ISFORMULA Function

Syntax

ISFORMULA(reference)

Arguments

reference: The cell I want to check for a formula.

Notes

If I enter an invalid cell reference, I’ll get a #VALUE! error.
Toggling formula display in the entire sheet is easy with Ctrl + `.

Example

I’ve got an example where I used ISFORMULA in different scenarios:

For a cell with a simple sum formula, ISFORMULA gives me a TRUE.
It even returns TRUE for a cell with a volatile function like TODAY().
It’s important to note ISFORMULA looks for formulas, not the results. So even if the formula output is an error or blank, I’ll still see TRUE.
If there’s just a value and no formula, ISFORMULA tells me FALSE.

8. ISLOGICAL Function

Syntax

The way you write the ISLOGICAL function is simple:

ISLOGICAL(value)

Arguments

value: This is what I’m checking to see if it’s TRUE or FALSE.

Notes

Just keep in mind, if I feed it a bad cell reference, it’ll spit out #VALUE!.

Example

Here’s what happens when I play around with different stuff in the cells:

First off, popped in a direct TRUE, came out TRUE.
Second, called TRUE as a function, also got TRUE.
Third and Fourth, whether I write FALSE or call it, I get FALSE either way.
Fifth and Sixth, used 1 and 0. Those might mean TRUE and FALSE in the numeric world, but ISLOGICAL isn’t buying it – says FALSE.
Seventh and Eighth, threw in some text. ISLOGICAL wasn’t impressed – FALSE.
Ninth, tried an empty cell. Guess what? Still FALSE.
And lastly, if there’s an error lurking in there, ISLOGICAL flags it with a #N/A.

Here’s a picture to show you all this in action:

9. ISNA Function

Syntax

ISNA(value)

Arguments

value: Reference or value to check for #N/A error.

Notes

The function detects #N/A errors specifically.
It discards other error types and returns FALSE.

Example

I checked various error types using ISNA. Here’s what I found:

=ISNA(#N/A) returned TRUE.
=ISNA(#VALUE!) returned FALSE.

Check out this example where I use ISNA with IF and VLOOKUP to provide clear info:

10. ISNONTEXT Function

Syntax

ISNONTEXT(value)

Arguments

value: The item I’m checking to see if it’s not text.

Notes

If a number is wrapped in quotes like “42”, I treat it like text, so ISNONTEXT tells me it’s False.

Example

So I put ISNONTEXT to work inside an IF statement. Let’s say I type a value into A2, and I want a clear message about what it is. The formula I might use:

=IF(ISNONTEXT(A2), "Hey, that's not text!", "This is text.")

And here’s an illustration for better clarity:

11. ISNUMBER Function

Syntax

=ISNUMBER(value)

Arguments

value: The value to test as a number.

Notes

Text in quotes like “123” is not counted as a numeric value.

Example

I might set up a cell to show “Please enter a number” when non-numeric input is detected. Here’s how I’d do it with the ISNUMBER function:

12. ISODD Function

Syntax

ISODD(value)

Arguments

value: The number I’m checking to see if it’s odd.

Notes

If I input something that’s not a number, I’ll get a #VALUE error.
Putting a number in quotes treats it like text, and I’ll get FALSE.

Example

I used ISODD combined with IF to create an alert message. If there’s a number in the cell that’s not odd, the message pops up. Here’s how it looks in Excel:

13. ISREF Function

Syntax

ISREF(value)

Arguments

value: I make sure this is the item I’m testing to see if it’s a valid reference.

Notes

I remember that if the reference address is in quotes, ISREF can’t verify it and returns FALSE—even if it’s actually valid.

Example

Let’s say I’ve got a named range ‘fruit’. Normally, ISREF can tell it’s a valid reference, but if I use quotes like “fruit”, ISREF treats it as text and says nope, not a valid reference.

14. ISTEXT Function

Syntax

ISTEXT(value)

Arguments

value: Can be a specific text, number, or a cell reference in Excel.

Notes

Text in quotes (e.g., “123”) is recognized as text by ISTEXT.
ISTEXT does not consider numbers, dates, or errors as text.

Example

Here’s an example where I use ISTEXT and IF together. Let’s say I want to make sure a user types in text.

Formula: =IF(ISTEXT(A1), "It's text!", "Please enter text.")
If A1 has the word “Name”, Excel will say “It’s text!”
If A1 has a number like 123, Excel will ask for text.

15. N Function

Syntax

N(value)

Arguments

value: The item that is being checked to see if it is TRUE or FALSE. When TRUE is passed, it returns 1; FALSE returns 2.

Notes

Generally, using N to directly convert Booleans is unnecessary since Excel handles the conversion automatically.

Example

Let’s say I want to add a comment in my formula without affecting the calculation. I can use the N function with a logical test that always returns FALSE. That way, I can tag a zero value, which doesn’t alter the result.

16. NA Function

Syntax

=NA()

Arguments

The NA function does not require any arguments.

Notes

I use the NA function to mark data that’s missing or not available.
It returns the error value #N/A, which Excel recognizes as “Not Available”.

Example

I placed the NA function in cell A1 and got #N/A as the result, signifying no data is present.

17. SHEET Function

Syntax

SHEET(value)

Arguments

[value]: Optionally, use the name of a sheet or a cell reference within that sheet.

Notes

The SHEET function counts all types of sheets, no matter if they’re chart sheets, worksheets, or macro sheets.
If I don’t specify a value, it tells me the sheet number where I entered the function.
In case of a non-existent sheet name, I’ll see a #N/A error.
A wrong sheet reference leads to a #REF! error message.

Example

When I experimented with different references using the SHEET function, here’s what happened:

Referring to a valid sheet, I got its respective sheet number.
With an invalid sheet name or reference, the function threw an error.

So that’s how the SHEET function responds to various situations in my workbook.

18. SHEETS Function

Syntax

SHEETS(reference)

Arguments

reference: The cell or range for which I want to determine the sheet count.

Notes

SHEETS includes every sheet type—Chart, Worksheet, and Macro Sheets.
The function counts visible, hidden, and very hidden sheets.
Leaving the reference argument empty returns the total count of sheets in the workbook.
An invalid reference will result in a #REF! error.

Example

In my example, I applied a 3D cell reference to sum A1 across five sheets. I used the SHEETS function with the same reference to count these sheets.

19. TYPE Function

Syntax

TYPE(value)

Arguments

value: The item I want to know the data type of.

Notes

If I use this function on a cell with a formula, it gives me the data type of the formula’s result, not the formula itself.
A blank cell will give me a 1, since emptiness is technically a number, oddly enough.
Dates are also considered numbers, so I’ll get a 1 if I test a cell with a date.

Example

Here’s a smart way I can use it:

=IF(TYPE(F3) <> 1, "Enter Valid Quantity", E3*F3)

In this nifty example, I first check the data type of what’s in F3. If it’s not a number (anything but 1), I tell the user to “Enter a Valid Quantity”. If it’s all good, I just do some simple math, multiplying the quantity by the price. Simple and efficient, that’s how I roll.