Home > Formulas > Text > CELL and INFO Excel CELL and INFO functions Examples in these videos, and in the written steps, show how to use the Excel information functions, CELL and INFO. Also see the ADDRESS Function examples.

Introduction

The CELL function can return information about cell formatting, contents and location.

The INFO function can show details such as Excel version, recalculation mode and number of worksheets in all open workbooks.

To return a cell address as text, based on a row and column number, go to the ADDRESS Function page.

Video: Use the CELL Function

Use the CELL function in Excel to get details about the cell format, location and contents. The CELL function can show details such as column width, worksheet name and number format.

Watch this short videos to see examples for using the CELL function.

CELL Syntax

The CELL function has the following syntax:

• CELL(info_type,reference)
  • info_type is one of the following values
    • address...Reference of first cell in reference, as text.
    • col...Column # of cell in reference.
    • color...1 = color for negative values; otherwise 0 (zero).
    • contents...Value of upper-left cell in reference
    • coord...Absolute reference of first cell in reference
    • filename...Filename and full path
    • format...Cell number format
    • parentheses...1 = parentheses for positive or all values; otherwise 0.
    • prefix ..."label prefix" of cell (alignment)
    • protect...0 = not locked, 1 = locked.
    • row...Row # of cell
    • type...type of data in cell (empty, text, other)
    • width...Column width of cell

CELL Traps

There are a few things to watch for when using the CELL function:

• The CELL function's reference argument is optional, but if it is omitted, the result is returned for the last cell that was changed.
  • To ensure that the result is what you expect, it's best to include a reference -- you can refer to the cell that contains the CELL formula.
• When using the CELL function, you may need to recalculate the worksheet to update the CELL formula result, if the cell is changed.
• For the "filename" info_type, an empty string is returned if the workbook has not yet been saved.

CELL Function Examples

In the 3 CELL function examples shown below, see how to show information about a cell, such as:

1. Cell's number format
2. Worksheet name
3. Cell alignment or column width

CELL Ex 1: Cell's Number Format

With the "format" value, you can use the CELL function to show the number format of a cell. For example, if cell B7 has General format, the result of this formula is "G", for General:

• =CELL("format",C2)

CELL Ex 2: Get Worksheet Name

With the "filename" value, the CELL function shows the file path, file name, and worksheet name.

• =CELL("filename",B2)

From that result, you can use other functions to extract the worksheet name.

In the formula below, the MID and FIND functions are used to find closing square bracket, then return the 32 characters that follow it. (A worksheet name is limited to 31 characters)

• =MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)

CELL Ex 3: Get Info_Type From Drop Down

Instead of typing the info_type value as a string in the CELL function, you can refer to a cell that contains one of the valid values. In this example, there is a data validation drop down list in cell B4, and the CELL function refers to it for the info_type value. The cell reference is to B2.

When "protect" is selected, the result is 1 if the cell is locked, and 0 if it's not locked.

• =CELL(B4,B2)

When "width" is selected, the result shows the column width, as an integer. The measurement unit represents the width of one character in the default font size.

Video: Use the INFO Function

Use the INFO function in Excel to get details about the current operating environment. The INFO function can show details such as Excel version, recalculation mode and number of worksheets in all open workbooks.

Watch this short videos to see examples for using the INFO function.

INFO Syntax

The INFO function has the following syntax:

• INFO(type_text)
  • type_text is one of the following items, that specifies what information you want.
  • "directory" Path of current directory
  • "numfile" Number of active worksheets in open workbooks.
  • "origin" Absolute cell reference of top left visible
  • "osversion" Current operating system version, as text.
  • "recalc" Current recalculation mode; "Automatic" or "Manual".
  • "release" Microsoft Excel version, as text.
  • "system" Name of the operating environment: "pcdos" or "mac"

INFO Traps

In Excel's Help file, there is a warning that you should use the INFO function with caution, because it could reveal confidential information to other users.

For example, you might not want other people to see the file path that your Excel workbook is in. If you're sending an Excel file to someone else, be sure to remove any data that you don't want to share!

INFO Function Examples

The INFO function can show information about the Excel application, such as:

1. Microsoft Excel version
2. Number of active worksheets
3. Current recalculation mode

In previous versions of Excel you could also get memory information ("memavail", "memused", and "totmem"), but those type_text items are no longer supported.

INFO Ex 1: Microsoft Excel version

With the "release" value, you can use the INFO function to show what version of Excel is being used. The result is text, not a number.

In the screenshot below, Excel 2010 is being used, so the version number is 14.0.

• =INFO("release")

• You could use the result to display a message, based on version number.
  =IF(C2+0<14,"Time to upgrade","Latest version")

INFO Ex 2: Number of active worksheets

With the "numfile" type_text value, the INFO function can show the number of active worksheets in all open workbooks. This number includes hidden sheets, sheets in hidden workbooks, and sheets in add-ins.

In this example, an add-in is running, and it has two worksheets, and the visible workbook has five worksheets. The total sheets returned by the INFO function is seven.

• =INFO("numfile")

INFO Ex 3: Current recalculation mode

Instead of typing the type_text value as a string in the INFO function, you can refer to a cell that contains one of the valid values.

In this example, there is a data validation drop down list in cell B3, and the INFO function refers to it.

• =INFO(B3)

When "recalc" is selected, the result shows that the current recalculation mode is Automatic.

Get the Sample Files

1. Get the CELL function sample file. The file is zipped, and is in xlsx file format
2. Get the INFO function sample file. The file is zipped, and is in xlsx file format

Function Tutorials

ADDRESS Function

VLOOKUP Function

INDEX / MATCH Functions

COUNT / COUNTIF

INDIRECT Function