Yesterday, 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.
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.
The INFO function has the following syntax:
- 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"
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.
You could use the result to display a message, based on version number.
=IF(C2+0<14,"Time to upgrade","Latest version")
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.
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.
When "recalc" is selected, the result shows that the current recalculation mode is Automatic.
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