INFO function in Excel

INFO Function in Excel - Featured ImageINFO Function in Excel - Featured Image

The INFO function in Excel returns information about the current operating environment.  In total, there are seven pieces of information available:

  • File path of the current folder
  • Number of worksheets in open workbooks
  • Cell reference to the top-left visible cell of the window
  • Operating system version
  • Excel’s calculation mode
  • Excel version
  • Type of operating system (e.g., PC or Mac)

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0046 INFO function in Excel.xlsx 

The arguments of the INFO function

The INFO function in Excel is easy to understand, as it only takes a single argument and returns a single piece of information.

=INFO(type_text)
  • type_text: a text value indicating which system information to be returned.

Argument options

The type_text can be any of the following:

“directory”
Folder path of the current default directory or folder

“numfile”
Total number of worksheets across all open workbooks

“origin”
Returns the cell reference of the top-left cell within the view at the time of the last calculation.

Every result is prefixed with “$A:”.  This is intended to be compatible with Lotus 1-2-3, so it does not serve many purposes these days.

The value returned depends on the current cell referencing style.  If the top-left cell in the view were C2, the value returned would be:

  • A1 Style = $A:$C$2
  • R1C1 Style = $A:R2C3

“osversion”
Operating system

“recalc”
Calculation mode as either “Automatic” or “Manual”

“release”
Microsoft Excel version

“system”
Name of the operating environment:

  • Macintosh = “mac”
  • Windows = “pcdos”

Basic usage

The screenshot below is a simple example showing the use of “RELEASE” to return the Excel version number.

Basic example of INFO

The formula in cell C2 is:

=INFO("RELEASE")

As I am using Excel 365, the result is 16.0.  Check out the Notes section below to understand the version numbers.

CALCULATION

Excel is designed to calculate cells only when necessary.  However, volatile functions are a special category of function. Any cells containing them or relying on them recalculate every time there is a change, even if those cells are unaffected.  INFO is one of those volatile functions

The values displayed are only correct at the point of calculation.  The value may change, such as the calculation mode or top-left visible cell, but the value displayed will not change until a recalculation occurs.

Examples of using the INFO function

In this section, we’ll look at some practical examples of using the INFO function.

Display system information

The screenshot below shows all seven possible arguments and the results which are achieved on my PC.  Depending on your IT environment, your results will vary.

INFO all the available options

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Display warning messages

This example demonstrates how to use the INFO function to display a warning message when Excel is in manual calculation mode.

INFO for warnings messages

The formula in cell C19 is:

=IF(INFO("RECALC")="Manual","WARNING: Manual calculation mode has been set","")

The formula will trigger the warning message only if the calculation mode is set to Manual at the time of the last recalculation.

Insert line breaks with CHAR function

A common compatibility issue between Windows and Mac is that the line break character, as it is different between the two systems.

  • Windows – CHAR(10) is a line break
  • Mac – CHAR(13) is a line break

In this example, we can see the INFO function’s SYSTEM argument being used to test for the operating system, so that we can apply the correct CHAR value for the line break.  If the operating system is a Mac, it uses CHAR(13), else it uses CHAR(10).

Line break within INFO and CHAR

The formula in cell C24 is:

="Insert a line break" &
IF(INFO("SYSTEM")="mac",CHAR(13),CHAR(10)) &
"in a text string."

Notes

Before Excel 2007, there were three additional information types:

  • memavai – returned the memory available
  • memused – returned the memory used
  • totmem – returned the total memory

These information types are no longer supported in Excel 2007 onwards.  If used, they will generate a #N/A error


The INFO function is not available in Excel online.


The DIRECTORY argument may not work as you expect.  It does not return the Excel workbook’s file path, but it returns the current default folder, which is the most recent folder location used in the Save As window.


The RELEASE argument will display values according to the following version numbering.  Due to the way Excel’s subscription model now operates, the version numbering no longer increases for each software release.

Windows

  • 2.0 = Excel 2
  • 3.0 = Excel 3
  • 4.0 = Excel 4
  • 5.0 = Excel 5
  • 7.0 = Excel 95
  • 8.0 = Excel 97
  • 9.0 = Excel 2000
  • 10.0 = Excel 2002
  • 11.0 = Excel 2003
  • 12.0 = Excel 2007
  • 14.0 = Excel 2010
  • 15.0 = Excel 2013
  • 16.0 = Excel 2016, Excel 2019, Excel 365

Mac

  • 1.0 = Excel 1
  • 1.5 = Excel 1.5
  • 2.2 = Excel 2
  • 3.0 = Excel 3
  • 4.0 = Excel 4
  • 5.0 = Excel 5
  • 8.0 = Excel 98
  • 9.0 = Excel 2000
  • 10.0 = Excel 2001
  • 11.0 = Excel 2004
  • 12.0 = Excel 2008
  • 14.0 = Excel 2011
  • 15.0 = Excel 2016, Excel 2019, Excel 365

Conclusion

In this post, we’ve seen how to use the INFO function in Excel. While many of the arguments have limited uses in everyday work, there are some strong use cases, such as:

  • Selecting the optional solution for each operating system
  • Displaying warning messages for calculation modes or mac users

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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