In this comprehensive guide, we will explore the Excel formula INFO, which is a useful function for retrieving information about the current operating system, Excel version, and other details. This guide will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the INFO function.
The syntax for the INFO function in Excel is quite simple. It only requires one argument, which is a text string that specifies the type of information you want to retrieve. The syntax is as follows:
Where info_type is a text string that represents the type of information you want to obtain. The available info types are:
- “directory” – Returns the path of the current directory or folder.
- “numfile” – Returns the number of open workbooks.
- “origin” – Returns the cell address of the upper-left corner of the current sheet.
- “osversion” – Returns the current operating system version.
- “recalc” – Returns the recalculation mode (either “Automatic” or “Manual”).
- “release” – Returns the Excel version number.
- “system” – Returns the type of operating system (either “pcdos” or “mac”).
Here are some examples of how to use the INFO function in Excel:
- Retrieve the current directory: To get the path of the current directory or folder, use the formula =INFO(“directory”). This will return the path where the current workbook is saved.
- Find the number of open workbooks: To find out how many workbooks are currently open in Excel, use the formula =INFO(“numfile”). This will return the number of open workbooks.
- Get the cell address of the upper-left corner: To find the cell address of the upper-left corner of the current sheet, use the formula =INFO(“origin”). This will return the cell address, such as “A1”.
- Check the operating system version: To determine the current operating system version, use the formula =INFO(“osversion”). This will return the version number of the operating system, such as “10.0” for Windows 10.
- Find the recalculation mode: To check whether the recalculation mode is set to Automatic or Manual, use the formula =INFO(“recalc”). This will return either “Automatic” or “Manual”.
- Get the Excel version number: To find out the version number of Excel you are using, use the formula =INFO(“release”). This will return the version number, such as “16.0” for Excel 2016.
- Determine the operating system type: To check whether you are using a Windows or Mac operating system, use the formula =INFO(“system”). This will return either “pcdos” for Windows or “mac” for macOS.
INFO Tips & Tricks
Here are some tips and tricks to help you make the most of the INFO function in Excel:
- Use the INFO function in combination with other functions to create dynamic file paths, such as =CONCATENATE(INFO(“directory”), “data.xlsx”) to create a file path to a data file in the same directory as the current workbook.
- If you want to display the information retrieved by the INFO function in a more user-friendly format, consider using the CONCATENATE function or the & operator to combine the INFO function with descriptive text, such as =CONCATENATE(“Excel Version: “, INFO(“release”)).
- Keep in mind that the INFO function is volatile, which means it recalculates every time the worksheet is recalculated. This can slow down your workbook if you use the INFO function excessively or in large worksheets.
Common Mistakes When Using INFO
Here are some common mistakes to avoid when using the INFO function:
- Make sure to use the correct info_type argument. The available options are “directory”, “numfile”, “origin”, “osversion”, “recalc”, “release”, and “system”. Using an incorrect or misspelled info_type will result in a #VALUE! error.
- Remember that the INFO function requires the info_type argument to be enclosed in double quotes. Forgetting the quotes will result in a #NAME? error.
Why Isn’t My INFO Function Working?
If you encounter issues with the INFO function, consider the following troubleshooting steps:
- Check the info_type argument for spelling errors or incorrect values. Make sure you are using one of the valid options listed in the syntax section.
- Ensure that the info_type argument is enclosed in double quotes. Omitting the quotes will result in a #NAME? error.
- If you are using the INFO function in combination with other functions, double-check the syntax and arguments of the other functions to ensure they are correct.
INFO: Related Formulae
Here are some related Excel formulae that you might find useful in conjunction with the INFO function:
- CELL: The CELL function returns information about the formatting, location, or contents of a cell. For example, =CELL(“address”, A1) returns the cell address of cell A1.
- ISNUMBER: The ISNUMBER function checks if a value is a number and returns TRUE if it is, or FALSE if it is not. For example, =ISNUMBER(INFO(“numfile”)) will return TRUE, as the number of open workbooks is a numeric value.
- LEN: The LEN function returns the length of a text string, which can be useful for formatting the output of the INFO function. For example, =LEN(INFO(“osversion”)) returns the number of characters in the operating system version number.
- CONCATENATE: The CONCATENATE function combines multiple text strings into one. It can be used to create more descriptive output for the INFO function, such as =CONCATENATE(“Excel Version: “, INFO(“release”)).
- IF: The IF function performs a logical test and returns one value if the test is TRUE, and another value if the test is FALSE. This can be used to create conditional output based on the results of the INFO function, such as =IF(INFO(“system”)=”pcdos”, “Windows”, “macOS”) to display the operating system name instead of the system code.