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 example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 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.
- type_text: a text value indicating which system information to be returned.
The type_text can be any of the following:
Folder path of the current default directory or folder
Total number of worksheets across all open workbooks
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
Calculation mode as either “Automatic” or “Manual”
Microsoft Excel version
Name of the operating environment:
- Macintosh = “mac”
- Windows = “pcdos”
The screenshot below is a simple example showing the use of “RELEASE” to return the Excel version number.
The formula in cell C2 is:
As I am using Excel 365, the result is 16.0. Check out the Notes section below to understand the version numbers.
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.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Display warning messages
This example demonstrates how to use the INFO function to display a warning message when Excel is in manual calculation mode.
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).
The formula in cell C24 is:
="Insert a line break" & IF(INFO("SYSTEM")="mac",CHAR(13),CHAR(10)) & "in a text string."
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.
- 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
- 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
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
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: