30 Excel Functions in 30 Days: 04 – INFO

Icon30DayYesterday, in the 30XL30D challenge, we cleaned things up with the TRIM function, and learned that it’s no SUBSTITUTE for calorie counting.

For day 4 in the challenge, we’ll examine the INFO function. Excel Help warns us to be careful with this function, or we could reveal private information to other users.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the INFO information and examples, and if you have other tips or examples, please share them in the comments. And remember to guard your secrets!

Function 04: INFO

The INFO function shows information about the current operating environment.

Info00

How Could You Use INFO?

The INFO function can show information about the Excel application, such as:

  • Microsoft Excel version
  • Number of active worksheets
  • Current recalculation mode

In previous versions of Excel you could also get memory information (“memavail”, “memused”, and “totmem”), but those type_text items are no longer supported.

INFO Syntax

The INFO function has the following syntax:

  • INFO(type_text)
    • type_text is one of the following items, that specifies what information you want.
    • “directory” Path of current directory
    • “numfile” Number of active worksheets in open workbooks.
    • “origin” Absolute cell reference of top left visible
    • “osversion” Current operating system version, as text.
    • “recalc” Current recalculation mode; “Automatic” or “Manual”.
    • “release” Microsoft Excel version, as text.
    • “system” Name of the operating environment: “pcdos” or “mac”

INFO Traps

In Excel’s Help file, there is a warning that you should use the INFO function with caution, because it could reveal confidential information to other users. For example, you might not want other people to see the file path that your Excel workbook is in. If you’re sending an Excel file to someone else, be sure to remove any data that you don’t want to share!

Example 1: Microsoft Excel version

With the “release” value, you can use the INFO function to show what version of Excel is being used. The result is text, not a number. In the screenshot below, Excel 2010 is being used, so the version number is 14.0.

=INFO(“release”)

Info01

You could use the result to display a message, based on version number.

=IF(C2+0<14,”Time to upgrade”,”Latest version”)

Info01b

Example 2: Number of active worksheets

With the “numfile” type_text value, the INFO function can show the number of active worksheets in all open workbooks. This number includes hidden sheets, sheets in hidden workbooks, and sheets in add-ins.

In this example, an add-in is running, and it has two worksheets, and the visible workbook has five worksheets. The total sheets returned by the INFO function is seven.

=INFO(“numfile”)

Info02

Example 3: Current recalculation mode

Instead of typing the type_text value as a string in the INFO function, you can refer to a cell that contains one of the valid values. In this example, there is a data validation drop down list in cell B3, and the INFO function refers to it.

=INFO(B3)

Info03

When “recalc” is selected, the result shows that the current recalculation mode is Automatic.

Info03b

Download the INFO Function File

To see the formulas used in today’s examples, you can download the INFO function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the INFO Video

To see a demonstration of the examples in the INFO function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Get Excel Version Number with INFO Function

 

_____________

You may also like...

6 Responses

  1. Przemek says:

    Firstly, big thank you for sharing 30XL30D challenge. This is great learning experience, very educative (just like the blog itself) that I widely recommend among friends : )

    =INFO() is my 1st favorite. Good to know that Excel offers such functionality, without going into VBA. I’ll now probably go through Information section in Excel to see more…

    Best regards

  2. @Przemek, you’re welcome, and thank you for your comment!

  3. Contextures Blog » 30 Excel Functions in 30 Days: 11 - CELL says:

    […] Day 4 of the 30XL30D challenge, we got details about the operating environment, with the INFO function — things like the Excel version and recalculation […]

  4. Khushnood Viccaji says:

    I tried using the “directory” type_text, and discovered that it doesn’t show the ‘current’ directory of the active workbook.
    Rather, it shows either
    – the (fixed) path to the user’s Documents folder opened in the current Excel session, or…
    – the path of the last file saved with the File >> SaveAs command

    As a workaround, I wrote this formula to get the directory of the active workbook:
    =LEFT((CELL(“filename”)),(SEARCH(“[“,(CELL(“filename”)))-1))

    Khushnood

  5. Fred Chidester says:

    It worked yes – as I have been working through the days – yes I am behind – but I have a workbook with the days and a few extra of my own items on worksheets trying the formulas. I had the one worksheet where I hid the master password pwd. I tried this nu of active worksheets – its telling me “9? I count “8? tabs what the – yes dud I right click unhide bingo “9? sheets great. Many times I hide sheets with drop downs etc. so they don’t attemp to change them. Great as always learning is great from you thanks.

  6. @Khushnood Viccaji – thanks for the tip on getting the current directory

    @Fred – thank for describing how you used the numfile option. Glad you found that missing sheet!

Leave a Reply

Your email address will not be published. Required fields are marked *