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:
=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_typeorreferencethe 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:
| A | B | |
|---|---|---|
| 1 | Sales | Cell Address |
| 2 | $20,000 | =CELL(“address”, INDEX(A2:A4, MATCH(MAX(A2:A4), A2:A4, 0))) |
| 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:
| A | B | |
|---|---|---|
| 1 | Sales | Cell Color |
| 2 | $20,000 | =CELL(“color”, A2) |
| 3 | $30,000 | =CELL(“color”, A3) |
| 4 | $25,000 | =CELL(“color”, A4) |
Result table:
| B | |
|---|---|
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |
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:
| A | B | |
|---|---|---|
| 1 | Data | Cell Type |
| 2 | “Excel” | =CELL(“type”, A2) |
| 3 | 2023 | =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:
| A | B | |
|---|---|---|
| 1 | Pass | =IF(CELL(“prefix”, A1)=”Pass”,”Well done”,”Try again”) |
| 2 | Fail | =IF(CELL(“prefix”, A2)=”Pass”,”Well done”,”Try again”) |
| 3 | Pass | =IF(CELL(“prefix”, A3)=”Pass”,”Well done”,”Try again”) |
📈 Result table:
| A | B | |
|---|---|---|
| 1 | Pass | Well done |
| 2 | Fail | Try again |
| 3 | Pass | Well 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:
| A | B | |
|---|---|---|
| 1 | 10 | =IF(CELL(“width”, A1)>10,SUM(A1:A3),”Cell too narrow”) |
| 2 | 20 | =IF(CELL(“width”, A2)>10,SUM(A1:A3),”Cell too narrow”) |
| 3 | 30 | =IF(CELL(“width”, A3)>10,SUM(A1:A3),”Cell too narrow”) |
📈 Result table:
| A | B | |
|---|---|---|
| 1 | 10 | 60 |
| 2 | 20 | 60 |
| 3 | 30 | 60 |
📝 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:
| A | B | C | |
|---|---|---|---|
| 1 | Type1 | Item1 | =VLOOKUP(CELL(“type”, B1), A1:B3, 2, FALSE) |
| 2 | Type2 | Item2 | =VLOOKUP(CELL(“type”, B2), A1:B3, 2, FALSE) |
| 3 | Type3 | Item3 | =VLOOKUP(CELL(“type”, B3), A1:B3, 2, FALSE) |
📈 Result table:
| A | B | C | |
|---|---|---|---|
| 1 | Type1 | Item1 | Item1 |
| 2 | Type2 | Item2 | Item2 |
| 3 | Type3 | Item3 | Item3 |
📝 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:
| A | B | |
|---|---|---|
| 1 | 10 | =IF(CELL(“format”, A1)=”G”,SUM(A1:A3), “Cell not in general format”) |
| 2 | 20 | =IF(CELL(“format”, A2)=”G”,SUM(A1:A3), “Cell not in general format”) |
| 3 | 30 | =IF(CELL(“format”, A3)=”G”,SUM(A1:A3), “Cell not in general format”) |
📈 Result table:
| A | B | |
|---|---|---|
| 1 | 10 | 60 |
| 2 | 20 | 60 |
| 3 | 30 | 60 |
📝 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:
| A | B | C | |
|---|---|---|---|
| 1 | A1 | Item1 | =VLOOKUP(CELL(“address”, A1), A1:B3, 2, FALSE) |
| 2 | A2 | Item2 | =VLOOKUP(CELL(“address”, A2), A1:B3, 2, FALSE) |
| 3 | A3 | Item3 | =VLOOKUP(CELL(“address”, A3), A1:B3, 2, FALSE) |
📈 Result table:
| A | B | C | |
|---|---|---|---|
| 1 | A1 | Item1 | Item1 |
| 2 | A2 | Item2 | Item2 |
| 3 | A3 | Item3 | Item3 |
📝 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:
| A | B | |
|---|---|---|
| 1 | 10 | =IF(CELL(“format”, A1)=”G”, “Green”, “Not Green”) |
| 2 | 20 | =IF(CELL(“format”, A2)=”G”, “Green”, “Not Green”) |
| 3 | 30 | =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:
| A | B | |
|---|---|---|
| 1 | 10 | Green |
| 2 | 20 | Not Green |
| 3 | 30 | Green |
📝 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:
| A | B | |
|---|---|---|
| 1 | 10 | =IF(CELL(“format”, A1)=”G”,AVERAGE(A1:A3), “Cell not in general format”) |
| 2 | 20 | =IF(CELL(“format”, A2)=”G”,AVERAGE(A1:A3), “Cell not in general format”) |
| 3 | 30 | =IF(CELL(“format”, A3)=”G”,AVERAGE(A1:A3), “Cell not in general format”) |
📈 Result table:
| A | B | |
|---|---|---|
| 1 | 10 | 20 |
| 2 | 20 | 20 |
| 3 | 30 | 20 |
📝 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
- 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.
- 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 yourinfo_type. This forces Excel to recalculate the function when the input cells change. - Combining with other functions: The CELL function can be nested inside other parts to perform more complex tasks, as shown in the examples above.
- 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.