How to Use the CELL Function in Excel
Excel and Google Sheets are powerful tools equipped with a myriad of functions that allow for efficient data manipulation. This guide will delve into the CELL function, which is ubiquitous across both Excel and Google Sheets, offering insights into a cell’s formatting, location, or contents.
Basic Syntax
The syntax for the CELL function remains consistent between Excel and Google Sheets:
=CELL(info_type, [reference])The info_type parameter is a text string that denotes the type of information you wish to retrieve about the cell. It can be any of the following values:
- “address” – Returns the cell’s address as text.
- “col” – Returns the column number of the cell.
- “row” – Returns the row number of the cell.
- “color” – Returns the cell’s background color in hexadecimal format.
- “contents” – Returns the actual contents of the cell.
- “format” – Returns a numeric code that corresponds to the cell’s format.
The optional reference parameter specifies the cell to be examined. If omitted, the CELL function defaults to the cell where the function is applied.
Examples
Example 1: Getting the Address of a Cell
Consider a situation where you have data in cell B2 and you want to display its address elsewhere:
| Data | Address |
|---|---|
| B2 | =CELL(“address”, B2) |
In this instance, =CELL("address", B2) outputs “B2” as the address of cell B2 in the designated cell.
Example 2: Checking Cell Formatting
To ascertain the format of a specific cell, such as A1, which has currency formatting:
| Data | Format |
|---|---|
| $100.00 | =CELL(“format”, A1) |
Here, =CELL("format", A1) returns a numeric value indicative of A1’s format. The precise interpretation of this format code can be obtained from the Excel or Google Sheets documentation.
Example 3: Getting Cell Color
To determine the background color of a cell, such as C3, you can use the “color” info_type.
| Data | Color Code |
|---|---|
| Pink | =CELL(“color”, C3) |
The formula =CELL("color", C3) fetches the hexadecimal code of C3’s background color, useful for tasks like conditional formatting or color comparisons.
By mastering the CELL function in Excel and Google Sheets, you enhance your ability to perform in-depth data analysis and improve your reports.
More information: https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf