CELL function in Excel and examples of its use

Very often, when working in Excel, you need to use data about addressing cells in a spreadsheet. For this was provided the function CELL. Consider its use on specific examples.



Function CELL value and properties in Excel

It should be noted that Excel uses several functions for addressing cells:

  • - ROW;
  • - COLUMN and others.

CELL function return information about the formatting, address, or contents of a cell. The function can return detailed information about the cell format, thereby eliminating the need to use VBA in some cases. The function is especially useful if you need to display the full path of the file in the cells.

How does the CELL function in Excel work?

Function in its work uses the syntax that consists of two arguments:

Function CELL.

=CELL(info_type,[reference])

  1. Info_type is a text value that specifies the type of cell information that is required. When you enter a function manually, a drop-down list is displayed where all possible values of the “information type” argument are shown:
    2. info_type.
  2. [Reference] is an optional argument. The cell for which you want to receive information. If this argument is omitted, the information specified in the information_type argument is returned for the last cell changed. If the link argument points to a range of cells, function returns information only for the upper-left value of the range.


Examples of using the CELL function in Excel

Example 1. Given the table of accounting work of employees of the organization of the form:

Example 1.

It is necessary with the help of the CELL function to calculate in which row and column the salary is in the amount of 235,000 dollars.

To do this, we introduce the following formula:

calculate in which row and column.

here:

  • - "row" and "column" - output parameter;
  • - C8 - address data with a salary.

As a result of the calculations, we obtain: row №8 and column № 3 (C).

How to know width of an Excel spreadsheet?

Example 2. It is necessary to calculate the width of the table in characters. Immediately it should be noted that in Excel, by default, the width of columns and cells is measured in the number of characters that they fit in their value are available for display in a cell without a line break.

Note. The height of lines and cells in Excel is by default measured in units of the basic font - in (pt) points. The larger the font, the higher the line for full display of characters in height.

Let's enter into C14 the formula for calculating the sum of the width of each column of the table:

Example 2.

here:

  • - "width" is a function parameter;
  • - A1 - the width of a specific column.

How to get the value of the first value in the range

Example 3. In the condition of example 1, you need to display the contents of only the first (upper left) value from the range A5:C8.

We introduce the formula for calculating:

first value in range.

Download examples CELL function in Excel

Description of the formula is similar to the previous two examples.


en ru