# CELL Function in Excel

## Part 1: Introduce

#### Definition

The CELL function in Excel is a built-in function that gives you Information about a cell’s formatting, location, or contents.

#### Purpose

The CELL function is primarily used to obtain specific details about the properties of a cell in Excel, such as address, type, color, content, etc. This can be very useful when calculating or functioning based on these details.

#### Syntax & Arguments

The syntax of the CELL function is as follows:

syntax
`=CELL(info_type, [reference]) `

#### Arguments

The CELL function has two arguments:

• `info_type` (required) – This text value specifies what type of cell information you want.
• `reference` (optional) – This is the cell reference. If omitted, the Information specified in the info_type argument is returned for the last changed cell.

#### Return value

The CELL function will return Information about a cell; the type of Information produced is dependent on the `info_type` argument.

#### Remarks

• If the referenced cell is blank, the CELL function returns a zero (0) or a zero-length string (“”).
• The CELL function will return a #VALUE! error if the `info_type` or `reference` the argument is not valid.

## Part 2: Examples of the CELL Function in Microsoft Excel

#### Example 1: Using CELL to Get Cell Address

Purpose of Example: To get the highest value’s address in a sales data list.

Data table:

AB
3\$30,000
4\$25,000

Result table:

B
2\$A\$3

Explanation: We use the INDEX and MATCH functions to find the highest sales figure, then use the CELL function to return its cell address.

#### Example 2: Using CELL to Get Cell Color

Purpose of Example: To return the color index of cells in a sales data list.

Data table:

AB
1SalesCell Color
2\$20,000=CELL(“color”, A2)
3\$30,000=CELL(“color”, A3)
4\$25,000=CELL(“color”, A4)

Result table:

B
21
30
41

Explanation: The CELL function is used to return the color index of the cell. An index of 1 indicates that the cell is formatted with a color (other than white), and 0 indicates no color formatting.

#### Example 3: Using CELL to Get Cell Type

Purpose of Example: To identify the data type in a list of data.

Data table:

AB
1DataCell Type
2“Excel”=CELL(“type”, A2)
32023=CELL(“type”, A3)
4\$20,000=CELL(“type”, A4)

Result table:

B
2“label”
3“value”
4“value”

Explanation: The CELL function returns the data type of the cell content. “label” indicates that the cell contains text, and “value” suggests that the cell has a number or date.

### Example 4: Use the CELL function with IF to Display Messages Based on Cell Content

Purpose of Example: To show how to use the CELL function nested with IF to display custom messages based on cell content.

π Data tables and formulas:

AB
1Pass=IF(CELL(“prefix”, A1)=”Pass”,”Well done”,”Try again”)
2Fail=IF(CELL(“prefix”, A2)=”Pass”,”Well done”,”Try again”)
3Pass=IF(CELL(“prefix”, A3)=”Pass”,”Well done”,”Try again”)

π Result table:

AB
1PassWell done
2FailTry again
3PassWell done

π Explanation: In this example, the CELL function is nested with the IF function to display “Well done” if the value in column A is “Pass” and “Try again” otherwise. This is an excellent way to provide feedback or guidance based on the values in your Excel worksheet.

### Example 5: Use the CELL function with SUM to Calculate the Sum of a Range based on Cell Width

Purpose of Example: To show how to use the CELL function nested with SUM to perform calculations based on cell width.

π Data tables and formulas:

AB
110=IF(CELL(“width”, A1)>10,SUM(A1:A3),”Cell too narrow”)
220=IF(CELL(“width”, A2)>10,SUM(A1:A3),”Cell too narrow”)
330=IF(CELL(“width”, A3)>10,SUM(A1:A3),”Cell too narrow”)

π Result table:

AB
11060
22060
33060

π Explanation: In this example, the CELL function is used with the SUM function to calculate the sum of the range A1:A3, but only if the cell’s width in column A is greater than 10. If the width is less than or equal to 10, the formula returns “Cell too narrow”. This is an example of using CELL to add a condition to a calculation based on cell properties.

### Example 6: Use the CELL function with VLOOKUP to Retrieve Information based on Cell Type

Purpose of Example: To show how to use the CELL function nested with VLOOKUP to retrieve Information based on cell type.

π Data tables and formulas:

ABC
1Type1Item1=VLOOKUP(CELL(“type”, B1), A1:B3, 2, FALSE)
2Type2Item2=VLOOKUP(CELL(“type”, B2), A1:B3, 2, FALSE)
3Type3Item3=VLOOKUP(CELL(“type”, B3), A1:B3, 2, FALSE)

π Result table:

ABC
1Type1Item1Item1
2Type2Item2Item2
3Type3Item3Item3

π Explanation: In this example, the CELL function is used with the VLOOKUP function to return the corresponding item in column B for the type in column A. However, please note that “type” in the CELL function does not replace the specific style in the sense of categories like Type1, Type2, etc., but rather it returns “l” if the cell content is a text (label) and “v” for other types of cell content (value). In this example, as all cells in B1:B3 are text, it will return “l,” and hence VLOOKUP will not find a match.

### Example 7: Use the CELL function with SUM to calculate the sum based on the Cell Format

Purpose of Example: To demonstrate how to use the CELL function nested with SUM to perform calculations based on cell formatting.

π Data tables and formulas:

AB
110=IF(CELL(“format”, A1)=”G”,SUM(A1:A3), “Cell not in general format”)
220=IF(CELL(“format”, A2)=”G”,SUM(A1:A3), “Cell not in general format”)
330=IF(CELL(“format”, A3)=”G”,SUM(A1:A3), “Cell not in general format”)

π Result table:

AB
11060
22060
33060

π Explanation: Here, the CELL function is used with the SUM function to calculate the sum of the range A1:A3, but only if the format of the cell in column A is “General” (denoted by “G”). If the cell format is not “General”, the formula returns “Cell not in the general format”. This provides a way of using CELL to perform calculations conditionally based on cell formatting.

### Example 8: Use the CELL function with VLOOKUP to retrieve Information based on the Cell Address

Purpose of Example: To demonstrate how to use the CELL function nested with VLOOKUP to retrieve Information based on a cell’s address.

π Data tables and formulas:

ABC

π Result table:

ABC
1A1Item1Item1
2A2Item2Item2
3A3Item3Item3

π Explanation: In this scenario, the CELL function is used with the VLOOKUP function to return the corresponding item in column B for the cell address in column A.

### Example 9: Use the CELL function with IF to determine cell color

Purpose of Example: To demonstrate how to use the CELL function nested with IF to determine if a cell is highlighted with a particular color.

Note: The `CELL` the function doesn’t directly return the color of a cell, but by using it in conjunction with conditional formatting and the `IF` process, we can accomplish this. This is more of an indirect usage of the `CELL` function.

π Data tables and formulas:

AB
110=IF(CELL(“format”, A1)=”G”, “Green”, “Not Green”)
220=IF(CELL(“format”, A2)=”G”, “Green”, “Not Green”)
330=IF(CELL(“format”, A3)=”G”, “Green”, “Not Green”)

Note: Conditional formatting would need to be applied to the cells in column A such that cells with “General” format are highlighted in green.

π Result table:

AB
110Green
220Not Green
330Green

π Explanation: This formula checks if the cell format in column A is “General”. If it is, it returns “Green”. If not, it replaces “Not Green”. This is an indirect way of checking if the cell is highlighted in green.

### Example 10: Use the CELL function with AVERAGE to calculate the average based on Cell Format

Purpose of Example: To demonstrate how to use the CELL function nested with AVERAGE to perform calculations based on cell formatting.

π Data tables and formulas:

AB
110=IF(CELL(“format”, A1)=”G”,AVERAGE(A1:A3), “Cell not in general format”)
220=IF(CELL(“format”, A2)=”G”,AVERAGE(A1:A3), “Cell not in general format”)
330=IF(CELL(“format”, A3)=”G”,AVERAGE(A1:A3), “Cell not in general format”)

π Result table:

AB
11020
22020
33020

π Explanation: Here, the CELL function is used with the AVERAGE function to calculate the average of the range A1:A3, but only if the format of the cell in column A is “General” (denoted by “G”). If the cell format is not “General”, the formula returns “Cell not in the general format”. This provides a way of using CELL to perform calculations conditionally based on cell formatting.

## Part 3: Tips and tricks

1. Use Carefully: The CELL function in Excel may return incorrect results when used with conditional formatting or if the cells are modified after the workbook is opened. Always double-check your results.
2. Recalculation: If you want the CELL function to update automatically when the input cells change, you should use the second argument, `reference`, even if you don’t need it for your `info_type`. This forces Excel to recalculate the function when the input cells change.
3. Combining with other functions: The CELL function can be nested inside other parts to perform more complex tasks, as shown in the examples above.
4. Avoid Volatile Functions: As the CELL function is volatile, it will recalculate every time the worksheet changes, which can slow down more extensive worksheets. If you don’t need real-time updates, consider using a non-volatile function.