INTRODUCTION
CELL FUNCTION comes under the INFORMATION category in Excel.CELL FUNCTION gives us a lot of information about any cell or the contents stored in the cell.This kind of information may be needed when we are writing a code for the MACRO or making any complex formulawhere we need to check the content or the properties of the cell to decide the further action.
Returns information about the formatting, location, or contents of the upper-left cell in a reference.
PURPOSE OF CELL FUNCTION IN EXCEL
CELL FUNCTION returns the content , properties or location of any specified cell.
PREREQUISITES TO LEARN CELL
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: CELL FUNCTION
The Syntax for the function is
=CELL(INFORMATION TYPE, CELL ADDRESS)
INFORMATION TYPE Type of information needed.
Different types of information available are enlisted in next section.
CELL ADDRESS Cell address of the location, of which information will be displayed.
INFORMATION TYPE AVAILABLE FOR CELL FUNCTION
DIFFERENT INFORMATION TYPE AVAILABLE ARE
| “address” | Reference of the first cell in reference, as text. |
| “col” | Column number of the cell in reference. |
| “color” | 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero). |
| “contents” | Value of the upper-left cell in reference; not a formula. |
| “coord” | Absolute reference of the cell range of the first cell in reference, as text. |
| “filename” | Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved. |
| “format” | Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values. |
| “parentheses” | 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0. |
| “prefix” | Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else. |
| “protect” | 0 if the cell is not locked, and 1 if the cell is locked. |
| “row” | Row number of the cell in reference. |
| “type” | Text value corresponding to the type of data in the cell. 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” | Column width of the cell rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size. |
In addition to these types, there are different codes which are returned in the “FORMAT” options. The following table gives the information about those codes.
FORMAT OPTION IN CELL FUNCTION
FOLLOWING ARE THE CODES WHICH ARE RETURNED WHEN A CELL HAS ANY FORMAT FROM THE BUILT IN NUMBER FORMATS IN EXCEL.
Let us take the same line from the introduction and try our latest learnt function on that.
| If the Microsoft Excel format is | CELL returns |
|---|---|
| General | “G” |
| 0 | “F0” |
| #,##0 | “,0” |
| 0.00 | “F2” |
| #,##0.00 | “,2” |
| $#,##0_);($#,##0) | “C0” |
| $#,##0_);[Red]($#,##0) | “C0-“ |
| $#,##0.00_);($#,##0.00) | “C2” |
| $#,##0.00_);[Red]($#,##0.00) | “C2-“ |
| 0% | “P0” |
| 0.00% | “P2” |
| 0.00E+00 | “S2” |
| # ?/? or # ??/?? | “G” |
| m/d/yy or m/d/yy h:mm or mm/dd/yy | “D4” |
| d-mmm-yy or dd-mmm-yy | “D1” |
| d-mmm or dd-mmm | “D2” |
| mmm-yy | “D3” |
| mm/dd | “D5” |
| h:mm AM/PM | “D7” |
| h:mm:ss AM/PM | “D6” |
| h:mm | “D9” |
| h:mm:ss | “D8” |
EXAMPLE:CELL FUNCTION IN EXCEL
DATA SAMPLE
We will try to use Cell function with different types of attributes. The CELL function will return the attribute which is asked for.
Let us check the different attributes of G7 TO G11.
For the example,
we have taken few attributes.
Address, type, row, col and format.
The picture below shows the application of the function
and output after applying the function with different attributes.
STEPS TO USE CELL FUNCTION (EXAMPLE )
The sample text is contained in the cells G7 to G11. The content can be numbers text or anything else. The CELL function mostly displays the information about the features of the cells.
The Functions are put in the RESULT COLUMN as =CELL(“ADDRESS”,G7) =CELL(“TYPE”,G8) =CELL(“ROW”,G9) =CELL(“COL”,G10) =CELL(“FORMAT”,G11)
for address, type , row , column and format respectively.
The function used is also given in the next cell.
as we can see the structure of the function is as per syntax,
=CELL(“ATTRIBUTE”, REFERENCE).
The outputs can be matched with the mentioned tables.
IF REFERENCE IS NOT GIVEN OR ANY CELL ADDRESS IS NOT GIVEN, THE FUNCTION WILL APPLY TO THE LAST EDITED CELL.
EXAMPLE:CELL FUNCTION USAGE :ANIMATED STEPS
