Info Function
An Information function that gives the user details about the working environment of the Excel software.
What is the INFO Function?
The INFO function in Excel gives the user information about the current working environment.
The different types of details that you could ask the function to retrieve are the path of the current folder or directory, the number of active worksheets in the workbook, the origin cell, the operating system version, what Excel version you have, etc.
If you have a workbook consisting of an ‘n’ number of worksheets, then the function makes it easy to determine how many active worksheets are present in the file.
Similarly, we know that not everyone may use the same Excel version. This creates the ‘compatibility issue’ with the functions that can ruin your Excel model.
Thus, even before you run the Excel model, you can check the Excel version and make the necessary changes to avoid the break.
The function is as simple to use as it sounds since there are a limited number of arguments it can accept to return the result to the user. This article will show the INFO function and how to use it in Excel.
Key Takeaways
- The INFO is categorized as an Information function that returns details about the version of Excel and its corresponding working environment.
- The function accepts a single argument - type_text, which can be seven different types of text strings to return varied results.
- The function does not accept additional arguments or values apart from what is already stored; if a different value is used, it returns a #VALUE! Error.
- The ‘directory’ returns the current directory of the Excel file, ‘numfile’ gives the total number of active worksheets in the workbook, and ‘origin’ gives the first visible top left corner cell from the Excel sheet.
- You can determine the operating system version using the ‘osversion,’ know whether the Excel is in automatic or manual calculation mode using the ‘recalc’ argument, and then find the Excel file version using ‘release.’
- Finally, the ‘system argument will give you the type of operating system environment you are working in, i.e., whether it is MAC or PCDOS.
Understanding INFO function
The INFO function is categorized as an Information function that gives the user details about the working environment of the Excel software.
For example, if you need to determine the path of the current directory or even the folder, all you need to do is type in the argument as a ‘directory.’
There are seven different keywords as the argument that will return different information statuses for the user. All these keywords can be understood from the syntax of the INFO function.
The syntax for the function is
=INFO(type_text)
where
- type_text - (required) text value specifies what type of information needs to be returned by Excel.
There is a limitation to what different text values you can use for the type_text argument.
The different types of values, along with their description, are:
| Value | Description |
|---|---|
| directory | Current directory or folder path |
| numfile | Number of active worksheets in a workbook |
| origin | The first cell visible to the user in the upper left corner |
| osversion | Returns the current operating system version |
| recalc | Checks and returns the current recalculation mode; the value can be "Automatic" or "Manual" |
| release | Returns the version of MS Excel |
| system | Returns the name of the operating system, i.e., Macintosh("mac") or Windows("pcdos") |
The best thing about the function is that you don’t need to type in each of these values; they can be selected from the drop-down while using the function.
Users can input each text value and return the corresponding results in Excel using the INFO function.
Examples of the INFO function
In this section, we will see exactly what results from the function returns when you use it in Excel. We will iterate through each of the values separately to understand it in-depth.
1. DIRECTORY
The argument ‘DIRECTORY’ returns the Excel user's current directory or folder path. For example, suppose you open a new workbook, work on a few line items and finally decide you need to save the file.
Suppose you need to check the path where the file will be saved. In that case, all you need to do is type in the formula =INFO("DIRECTORY"), which gives the directory path as C:\Users\Lenovo\Documents\
You can then change the file directory while saving the folder or easily retrieve the file if you weren’t aware of where the file was being saved.
2. NUMFILE
The ‘NUMFILE’ argument returns the total number of active worksheets in a given workbook. By default, an Excel file contains three empty spreadsheets.
However, let’s assume you already have a workbook with an in-built Excel model consisting of an ‘n’ number of sheets. How do you determine how many worksheets you have?
In this case, we will use the formula =INFO(“NUMFILE”) that gives the result as 3.
3. ORIGIN
The ‘ORIGIN’ argument checks what cell is present in the upper left corner of the spreadsheet. An example would help to understand the argument better than the text. For example, suppose the top left cell visible in scenario one is cell A1.
By using the formula =INFO("ORIGIN") in almost any cell (but with the condition that the A1 cell is visible), we will get the result as $A:$A$1, indicating that A1 is present in the upper left corner.
Let’s scroll the spreadsheets down for a bit for scenario two. The top left corner of the spreadsheet looks as illustrated below:
Using the formula =INFO("ORIGIN"), our top left cell becomes A1048552 since it is the first ‘visible’ cell in the top left corner.
4. OSVERSION
The formula =INFO("OSVERSION") returns the current version of the operating system. This will vary depending on what operating system you have installed on your laptop.
It's a 64-bit Windows operating system with a tenth version of the NT family of versions.
5. RECALC
The ‘RECALC’ argument tells whether the calculations made in the workbook are in automatic mode or manual. This is one of the wisest use cases you can find for the function.
Whenever I worked on Excel models built by fellow analysts, I used to wonder why the formulas wouldn’t work despite zero errors. An in-depth analysis would later say that the calculation mode was switched from ‘Automatic’ to ‘Manual.’
Thus, I needed to press the F9 key to make the formulas work. It is a cool trick if you need to make your file work faster, but a newbie like me just wastes so much valuable time, right?
If you do not want to go through a similar scenario, you must type the formula =INFO("RECALC") in any cell, giving the result:
6. RELEASE
The ‘RELEASE’ argument returns the version of the Excel file as a text string. For example, using the formula =INFO(“RELEASE”), we get the version of Excel as 16.0.
Again, the value would differ depending on what type of Excel version file you are working on.
7. SYSTEM
The ‘SYSTEM’ argument returns what type of operating environment you are working in. Generally, the argument would return two different results, i.e., pcdos and mac. If you are working on an Apple Inc device, the function returns the result as ‘mac.’
Any other device except Apple will return the result as pedos. We will use the formula =INFO("SYSTEM"), which gives the result for us as ‘pcdos.’
Thus, you can use the different arguments in the INFO function to find specific information about Excel working conditions and the corresponding environment.
or Want to Sign up with your social account?