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

Other functions
Returns a number corresponding to an error type
Returns information about the current operating environment
Returns TRUE if the value is blank
Returns TRUE if the value is any error value except #N/A
Returns TRUE if the value is any error value
Returns TRUE if the number is even
Returns TRUE if there is a reference to a cell that contains a formula
Returns TRUE if the value is a logical value
Returns TRUE if the value is the #N/A error value
Returns TRUE if the value is not text
Returns TRUE if the value is a number
Returns TRUE if the number is odd
Returns TRUE if the value is a reference
Returns TRUE if the value is text
Returns a value converted to a number
Returns the error value #N/A
Returns the sheet number of the referenced sheet
Returns the number of sheets in a reference
Returns a number indicating the data type of a value