PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-CELL

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 formula
where 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

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 isCELL 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.

 

GYANKOSH CELL FUNCTION DATA SAMPLE
EXCEL:CELL FUNCTION DATA SAMPLE

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

GYANKOSH_CELL
EXCEL:CELL FUNCTION ANIMATED EXAMPLE

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: