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
Contents
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.
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.
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."
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
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
The Mac release numbers are partially incorrect. When Mac Excel 2016 first came out, the release was 15.0. After a period of time, during which Mac Excel improved to be more like Windows Excel, it was changed to 16.0, where it has remained unchanged for Excel 2019 and Microsoft 365. And in fact, on the Mac, INFO(“RELEASE”) gives two digits after the decimal point, e.g., 16.46, reflecting the way the Mac Excel version number is incremented.