The CELL function is a useful tool that allows you to retrieve specific information about a cell's formatting, location, and content.
Contents:
Syntax
= CELL(info_type, [reference])info_type - info type options specifying which information to retrieve
[reference] - optional parameter, allows you to specify which cells info to retrieve. If left out, defaults to the last changed cell
Explanation
The CELL function is part of the "Information" group of functions within Excel.
The CELL function is a useful tool that lets you retrieve specific information about a cell's characteristics, such as its address, format, or content.
By specifying an info_type parameter, users can obtain various details, including the cell's row or column number, data type, or even the workbook's file name and path.
This function is particularly useful when creating dynamic references, applying conditional formatting, or troubleshooting formulas and formatting issues.
However, it is important to note that the CELL function is sensitive to changes in the workbook, and the results may be affected by the addition or deletion of cells.
Note:
Additionally, some `info_type` options might not work as expected in certain situations, such as with shared workbooks or protected sheets.
Every CELL Info Type in Excel
There are 12 different info types that can be used in the CELL function. Here is a list with what each type returns, as well as a use case for each.
"address" - Returns the cell address. - Find the address of a specific cell, which can be helpful in creating dynamic ranges or for troubleshooting formulas.
"col" - Returns the column number. - Find the column number of a specific cell, which can be useful in creating dynamic column references or for use with other Excel functions like INDEX or MATCH.
"color" - Returns the value 1 if the cell is formatted to display in color for negative values - Detect conditional formatting
"contents" - Returns the cell's value. - Retrieve the value in a specific cell, which can be useful for creating dynamic references or for use with other Excel functions like IF, SUMIF, or VLOOKUP.
"filename" - Returns the full file name and path. - Retrieve the file name and path of the current workbook, which can be helpful when creating file management systems or for displaying the file location in a header or footer.
"format" - Returns the cell format as a text value. - Determine the number format applied to a cell, which can be helpful for applying conditional formatting based on the cell format or for troubleshooting formatting issues.
"parentheses" - Returns the value 1 if the cell is formatted to display parentheses for positive values - Determine the alignment of text within a cell, which can be helpful for applying conditional formatting based on alignment or for troubleshooting formatting issues.
"prefix" - Returns the text alignment of the cell. - Determine the alignment of text within a cell, which can be helpful for applying conditional formatting based on alignment or for troubleshooting formatting issues.
"protect" - Returns the cell's protection status. - Determine if a cell is locked or unlocked, which can be useful for creating conditional formatting based on protection status or for troubleshooting worksheet protection settings.
"row" - Returns the row number.
- Similar to "col", finding the row number can be helpful in creating dynamic row references or for use with other Excel functions like INDEX or MATCH.
"type" - Returns the cell's data type.
- Determine the data type of a cell (e.g., text, number, or logical), which can be helpful for applying conditional formatting based on data type or for troubleshooting data entry issues.
"width" - Returns the column width in characters, rounded off to an integer. - Find the width of a specific column, which can be useful for adjusting column widths dynamically or for creating user-defined print settings.
Examples
How to Return the Column or Row Number from Active Cell
In this example, you can see that by combining the CELL function with the "col" or "row" info type, and referencing the cell that we're placing the formula in, we can actually return the column or row number that each cell is in.
Here are the two formulas to return the current cell's row and column position.
Column Number from Active Cell:
= CELL("col")
Row Number from Active Cell:
= CELL("row")
How to Return the File Pathway for Excel Workbook Using a Formula
The CELL function can also be used to extract the file pathway that any workbook is saved under.
This is especially helpful when creating a dynamic macro that needs to run off of a specific file pathway.
Using the "filename" data type will return the full file pathway as well as the workbook name in brackets, and the sheet that the formula is entered in.
= CELL("filename")