Examples in these videos, and in the written steps, show how to use the Excel information functions, CELL and INFO.
Also see the ADDRESS Function examples.
The CELL function can return information about cell formatting, contents and location.
The INFO function can show details such as Excel version, recalculation mode and number of worksheets in all open workbooks.
To return a cell address as text, based on a row and column number, go to the ADDRESS Function page.
Use the CELL function in Excel to get details about the cell format, location and contents. The CELL function can show details such as column width, worksheet name and number format.
Watch this short videos to see examples for using the CELL function.
The CELL function has the following syntax:
There are a few things to watch for when using the CELL function:
In the 3 CELL function examples shown below, see how to show information about a cell, such as:
With the "format" value, you can use the CELL function to show the number format of a cell. For example, if cell B7 has General format, the result of this formula is "G", for General:
With the "filename" value, the CELL function shows the file path, file name, and worksheet name.
From that result, you can use other functions to extract the worksheet name.
In the formula below, the MID and FIND functions are used to find closing square bracket, then return the 32 characters that follow it. (A worksheet name is limited to 31 characters)
Instead of typing the info_type value as a string in the CELL function, you can refer to a cell that contains one of the valid values. In this example, there is a data validation drop down list in cell B4, and the CELL function refers to it for the info_type value. The cell reference is to B2.
When "protect" is selected, the result is 1 if the cell is locked, and 0 if it's not locked.
When "width" is selected, the result shows the column width, as an integer. The measurement unit represents the width of one character in the default font size.
Use the INFO function in Excel to get details about the current operating environment. The INFO function can show details such as Excel version, recalculation mode and number of worksheets in all open workbooks.
Watch this short videos to see examples for using the INFO function.
The INFO function has the following syntax:
In Excel's Help file, there is a warning that you should use the INFO function with caution, because it could reveal confidential information to other users.
For example, you might not want other people to see the file path that your Excel workbook is in. If you're sending an Excel file to someone else, be sure to remove any data that you don't want to share!
The INFO function can show information about the Excel application, such as:
In previous versions of Excel you could also get memory information ("memavail", "memused", and "totmem"), but those type_text items are no longer supported.
With the "release" value, you can use the INFO function to show what version of Excel is being used. The result is text, not a number.
In the screenshot below, Excel 2010 is being used, so the version number is 14.0.
With the "numfile" type_text value, the INFO function can show the number of active worksheets in all open workbooks. This number includes hidden sheets, sheets in hidden workbooks, and sheets in add-ins.
In this example, an add-in is running, and it has two worksheets, and the visible workbook has five worksheets. The total sheets returned by the INFO function is seven.
Instead of typing the type_text value as a string in the INFO function, you can refer to a cell that contains one of the valid values.
In this example, there is a data validation drop down list in cell B3, and the INFO function refers to it.
When "recalc" is selected, the result shows that the current recalculation mode is Automatic.
Last updated: March 14, 2023 4:24 PM