The first question is really – what are the Excel Information Functions?
They are functions, just like any other, however instead of working out a specific answer (to a maths question), the questions are about the sheet and the data that you are using.
So what is the point of them?
For most of us that use Excel, not that much.
But…
Depending on what you need to do, they offer options that you can use in other formulas and functions.
For example:
You’re expecting a number to be entered, and too many times you get text. This causes an error (urgh) – instead you can add an option in (information function in excel), to check that you’ve got a number to work on. If it’s a number, then you continue with the formula. Otherwise it stops.
Some of these Excel information functions make sense to me, and frankly some don’t.
Let’s take a look at them:
I’ve grouped some of them together as that made sense to me.
Cell Contents
This group of Information Functions all look at the contents of the cell. You can use them to check things before working on the contents.
For a lot of these functions, you’re not going to use them by themselves. You can, they are functions, but they aren’t useful alone.
In reality, they are best used with other functions – for example the IF Statement (Excel IF Statement: What do you need to know) as part of the logical test.
isblank(value)
Looks to see if the given value (i.e. cell address) is empty.
True is for a blank cell. False if there’s something in the cell.
isformula(reference)
Does the reference (cell address) have a formula in it?
Returns true (has a formula) or false (anything else).
iseven(number)
Given a number checks if it’s even.
The ‘number’ can of course be a cell reference.
True is that it’s even, false for odd numbers
isodd(number)
You didn’t think they’d miss out the opposite option… 😁
The same as iseven, but returns true if it’s odd, and false for even numbers.
iserr(value)
To err is human, to error is for computers.
It’s frustrating when the result of your hard work is an error message. Excel has it’s own share of these messages, and this function looks for a specific message “#N/A”.
However, it’s actually looking to be able to tell you that it isn’t this message.
False means that the error is #N/A, and true means that it’s any other error message.
iserror(value)
Unlike iserr, which looks to exclude a specific message, this version simply looks to see if the result is an error – of any type.
True means that it is an error, false means that it’s data.
islogical(value)
Based on the cell reference that you give this function, it looks to see if the cell contains True or False.
The result is True if it’s either of those options, all other types (numbers, text, formula) displays False.
Information
Surprisingly, there are a few functions that actually add information into your spreadsheet.
info(type_text)
How often have you needed some information related to the computer that you’re using in your spreadsheet?
Me too – all the time. .😂
OK, I don’t think I’ve needed it yet, but when (or if) that changes then the Info Function is here for us.
Select the option from the list (or type it in with the “double quotes”):
- “Directory” = current directory or folder that the file is saved into
- “Numfile” = the number of the active worksheets
- “Origin” = top and leftmost visible cell
- “OSVersion” = operating system version
- “Recalc” = Recalculation mode
- “Release” = Version of Microsoft Excel
- “System” = Operating Environment
cell(info_type, [reference])
When you need some information about the formatting, location or contents of a specific cell, there’s a lot to choose from with the Cell function.
- address = the current cell address (for this this cell if reference is blank, or the selected cell)
- col = the number for the column (col A = 1), for the selected cell
- color = negative numbers can have their own colour. If you’re using this option, then this function returns 1, otherwise 0
- contents = displays the contents of the cell, without formatting
- filename = displays the full path (location) and filename of this spreadsheet
- format
- parentheses = The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
- prefix = Text value (i.e. a symbol) corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else.*
- protect = is the cell protected with a lock? displays 1 if it’s locked, and 0 for anything else
- row = the row number of the selected cell
- type = displays “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else.
- width = creates an array with 2 items (a two column table grouped together). The 1st item is the column width of the cell, rounded off to a whole number. The 2nd item in the array is True if the column width is the default or False if the width it’s been set by the user.
Note: A number of these options aren’t available in Excel Online, Excel Mobile or Excel Starter.
* I’ve been known to use the apostrophe (‘) to force Excel to display information as text, and it’s this type of information that the prefix command is looking at.
Next Steps
For more info on all of these functions you can use the wizard, otherwise known as Insert Function. If you want a quick guide take a look at my video How to use the Insert Function button in Excel.
Take a second look at these information functions in Excel and see how you could make use of them.
Related Posts
- Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour - How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics!
excel information functions, information function in excel, excel file information function