Microsoft Excel INFO Functions

In Microsoft Excel you have access to a few functions that are a little different than your standard mathematical based functions.

The functions this article will cover are Info Functions that can provide information about your workbook, cells, and other function results or errors. While these may not be functions you would use every day, they are just the type of great little nuggets of trickery that you can use to really round out what your worksheets or Excel Applications are able to do.

The functions in this article will show you how to return information about the type of formatting, location, or contents of a cell in a reference. Things like colors, row, width, format, even the filename can get returned.

You can also retrieve information about your current operating environment – how much memory you’re using or how much is available, or even the version of operating system you’re using. All in the same old Excel cells!

If you need to do some function testing, we’ll cover ways to test the type of value that gets returned from a function, and we’ll even do some “error handling” with standard excel functions.

To get started, we’re going to have a look at the CELL function.

The CELL function returns information about – you guessed it – a cell!

Figure 1

You can use many different arguments for the cell function:

    “address”
    “col”
    “color”
    “contents”
    “filename”
    “format”
    “prefix”
    “protect”
    “row”
    “type”
    “width”

For instance, the function, =CELL(“filename”) will give you the full path and filename of the current excel file you’re working in (note: this only works with saved files, if you try it in a workbook you haven’t saved yet, there’s obviously no filename!)

=CELL(“width”) likewise gives you the cell width. These are very handy functions for returning information about cells in order to test different pieces of your sheet or insert some interesting data.

The INFO function is similar, and returns information about the computer you’re working on. You can use these arguments:

    “directory”
    “memavail”
    “memused”
    “numfile”
    “origin”
    “osversion”
    “recalc”
    “release”
    “system”
    “totmem”

So, if you want to see how much memory Excel (and your other applications) happens to be sucking out of your system at this very moment, try the function, =INFO(“memused”). If you’d like to see what kind of operating system you’re on, try =INFO(“osversion”). Incidentally, it might just tell you you’re on NT 5 if you’re running Windows XP.

Another handy function is the “Type” function. You can use Type to adjust how a particular function works if it depends on the type of value in a different cell. If the value in the referenced cell is a number, Type returns 1; text, 2; logical value, 3; error value, 16; Array, 64.

The ERROR.TYPE function (Yes, that’s “Error” and “Type” separated by a period) returns a number value corresponding to an Excel error. For instance, look at this function:

=IF(ERROR.TYPE(A1)=2,"You can't divide by zero!")

This uses an IF formula to test the value of A1. If the value is “#DIV/0!” then instead of returning that error, it returns the phrase, “You can’t divide by zero!”. You can use the ERROR.TYPE function to give a friendlier touch to some of the formulas in your Excel workbook. So what are the error values?

    #N/A = 7
    #NUM! = 6
    #NAME? = 5
    #REF! = 4
    #VALUE! = 3
    #DIV/0! = 2
    #NULL! = 1

Basically, there are plenty of little-known functions and formulas just like these that can really round out an Excel application. While you don’t need to be very fancy with the values you get from these sorts of functions, you can use them to subtly enhance your projects to help them impress just a little bit more!

Good luck!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Microsoft Excel INFO Functions