CELL Function in Excel: Explained

In this article, you will learn how to use the Excel CELL function.

‍

What is the CELL function in Excel?

The CELL function in Excel is used to retrieve information about a cell's formatting, location, or contents.

Note: The CELL function in Excel is volatile (depending on the argument) meaning when the Excel sheet is recalculated, the CELL function will recalculate.

‍

When is the CELL function in Excel helpful?

The Excel CELL function is helpful when you need to know further information on a cell. Examples of this include:

• Error Checks: In large worksheets, you might want to check if any cells are left blank or if certain cells contain non-numeric data. By using the CELL function with the "type" info_type, you can quickly locate these cells.
• Documentation: The CELL function can also be used as part of your documentation process, allowing you to easily include key details about cells, such as their format or location, in your reports.

‍

Understanding the syntax of the CELL function in Excel

The syntax for the CELL function is the following:

=CELL(info_type, [reference])

Info_type: This is a required argument that specifies what type of cell information you want to retrieve. Below is the list of info_type values:

• “address”: Returns an absolute reference to the top left cell in reference.
• “col”: Returns the column number of the cell in reference.
• “color”: Returns 1 if the cell is formatted in color for negative values; otherwise returns 0.
• “contents”: Returns the value of the cell (not formatted).
• “filename”: Returns the full file path and name of the file that contains the reference.
• “format”: Returns the number format of the cell (e.g. mm/dd/yy returns “D4”).
• “parentheses”: Returns 1 if the cell is formatted with parentheses for negative values; otherwise returns 0.
• “prefix”: Returns the label prefix of the cell.
• “protect”: Returns 0 if the cell is not locked; returns 1 if the cell is locked.
• “row”: Returns the row number of the cell in reference.
• “type”: Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.
• “width”: Returns the column width of the cell in reference.

Note of caution: The info_type values must be entered with quotation marks (e.g. =CELL(“color”, B2)) or the CELL function will return a #NAME error.

‍

Reference: This is an optional argument that references the cell you want information on. If you omit this argument, the function will use the data from the most recently edited cell in the sheet.

‍

How to use the CELL function in Excel

Suppose you are a financial analyst at a company and you have a data set that includes the division names, different types of investments, and the projected return rate for each investment for the next year. Below is a screenshot of using the CELL function with this data set.

‍

Another way to use the CELL function is in conjunction with other Excel functions. Continuing to use the example data set below, suppose you wanted to create a new table that labeled the projected returns as “High”, “Medium”, or “Low”. To do this you could write the following syntax:

=IF(CELL("contents",D3)>9%,"High",IF(CELL("contents",D3)>=6%,"Medium","Low"))

By combining the IF and CELL functions, we can categorize investment returns via a specified condition. If the return (retrieved using CELL's "contents") exceeds 9%, we label it as "High". If it's at least 6%, it's "Medium". Otherwise, it's "Low". Applying this formula across the new table column efficiently categorizes all return rates in the dataset. A screenshot of these results can be seen below.

‍

Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.