The Excel CELL function is classified as an excel Information formula. In this tutorial, we would learn about how to use the excel CELL formula.
When to Use CELL Function in Excel
The excel CELL function is an information formula that returns information about the cell or reference. This function is generally not used individually, instead, it is embedded within another formula.
- When to Use CELL Function in Excel
- Syntax and Arguments
- Examples of Using CELL Function in Excel
- Ex. 1 # Get Cell Address of Active Cell in Excel
- Ex. 2 # Get Column and Row Number of Cell in Excel
- Ex. 3 # How to Get File Name and Path of Active Workbook
- Ex. 4 # Find Width of Cell in Excel Using Formula
- Ex. 5 # Check If Excel Cell Is Locked (Protected) or Not
- Ex. 6 # How to Find the Cell Format of Cell in Excel
This function is used to get address of the cell, row and column number, width of the cell, file name and path of the excel file, etc.
Syntax and Arguments
The CELL excel function has two arguments – info_type and [reference].
- info_type – In this argument, specify the type of information that you want to get about the cell/excel.
You can retrieve cell address, cell formatting, width, row number, column number, contents, filename (discussed later)
- [reference] – In this optional argument, specify reference about which you want to retrieve the information.
The below image shows the list of types of information in CELL excel formula:
Examples of Using CELL Function in Excel
Let us now look at some examples which should how to use the CELL formula in excel with different types of information.
Ex. 1 # Get Cell Address of Active Cell in Excel
To get the R1C1 address of the active cell in excel, simply use the following formula in the cell.
As a result, excel returns the cell address of the cell in which the formula resides.
In order to get the cell address of some another cell, give cell reference as second argument value [reference].
Suppose we want to find the cell address of cell B4 inside cell A1, simply use the following formula in cell A1:
Ex. 2 # Get Column and Row Number of Cell in Excel
To extract the column and row number of active cell in excel, simply use the following formulas:
As a result, the above formulas will return the column and row number of the active cell, respectively.
Ex. 3 # How to Get File Name and Path of Active Workbook
To get the file name and full path of the active workbook in the excel cell, use the following formula:
As a result, the excel would return the full path of the excel workbook, along with the worksheet name. See the image below:
Blog readers are also interested to learn about how to get the filename of all the files in a folder in excel.
Ex. 4 # Find Width of Cell in Excel Using Formula
To get the information about the cell width in excel using formula, simply enter the following formula in the cell.
As a result, excel would return the cell width number rounded off to the nearest integer.
Ex. 5 # Check If Excel Cell Is Locked (Protected) or Not
In one of our blogs, we had learned how to lock and protect a cell in excel so that it is not editable. Check this link.
A cell protection property in excel can be either protected or not protected.
To find whether the excel cell property is protected or not, simply use the following formula in the target cell.
The result of this formula may be either 1 or 0 wherein 1 represents ‘LOCKED’ and 0 represents ‘NOT LOCKED’.
In the above example, cell B2 is a locked cell. Therefore, the result of the CELL Excel function is 1.
Ex. 6 # How to Find the Cell Format of Cell in Excel
To find the cell format of a particular cell in excel, use the following formula in the respective cell.
As a result, excel would return some weird text output values when you use the “format” as an info_type.
Refer to the below list of the text values for format info_type.
|If the Excel format is||The CELL function returns|
|# ?/? or # ??/??||“G”|
|m/d/yy or m/d/yy h:mm or mm/dd/yy||“D4”|
|d-mmm-yy or dd-mmm-yy||“D1”|
|d-mmm or dd-mmm||“D2”|
For example, in the below example the cell with date formatted cell returns the value as D1.
Similarly, a ‘General’ formatted cell will return the value as ‘G’.
With this, we have completed with this cell information formula.
Thank You 🙂