Categorized | excel formula
Tags |

CELL Formula in Excel – How to Use CELL Formula and Examples




Syntax of CELL Formula
Example of CELL Formula
Possible Errors returned by the CELL Formula

CELL formula returns infomation about the various attributes of a cell such as address, color, width, column / row postitions etc.

CELL Formula Syntax in Excel

The CELL Formula has two parts:

CELL (attribute_type, cell_reference)

cell-formula-excel

attribute_type

attribute_type specifies the attribute that you are trying to find. It can be color, column / row position, width, format or a number of other attributes (see below). Only one attribute can be specified in a single formula.

cell_reference

The cell_reference is the particular cell (or a range) for which the attribute value need to be returned. If a range is used, the attribute value for the top-left cell is returned.

Here is a list of all the values that can be specified as attribute values for the CELL Formula:

cell-formula-excel-syntax

Example of a CELL Formula

Let’s look at an example of the CELL formula. Suppose we had a cell, say A10 and we wanted to know its row or column position, we could write something similar to:
=CELL(“row”, A10) or
=CELL(“col”, A10)

Let’s look at some other examples:

cell-formula-usage

As you can see, the CELL formula is pretty versatile. You can do a number of this with this formula that you would not be able to do otherwise such as return the format of the cell. It can even return the name and path of the workbook if its has been saved.

How to enter the CELL formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =CELL(

3. Type the attribute name within quotes. For exmaple for address, type “address”

4. Press the comma key (,)

5. Move the cursor using the up-down or left-right arrow keys and take it to the cell for which you would like the attribute value to be returned.

6. Close the formula bracket by typing in ).

(In the end your formula should look something like this =CELL(“row”, A10) )

Possible Errors with the CELL Formula

The CELL formula can result in the following error values:

CELL #VALUE! Error

#VALUE! Error in CELL can result from wrongly specifying the attribute type. Say for example you wanted to write =CELL(“address”,A10) but instead erroneously entered =CELL(“addreess”,A10) (one extra ‘e’). As a result of the wrong attribute type, the CELL Formula will return a #VALUE! error value

CELL #NAME? Error

#NAME? Error in CELL can result from wrongly specifying address of the cell for which the attribute value needs to be returned. Say for example you wanted to write =CELL(“col”, AA134) but instead erroneously entered =CELL(“col”, AAAA134) (two extra A’s). Now the cell AAAA134 does not exist (not even in versions beyond Excel 2007 :-) ) anywhere in the sheet and as a result the CELL function throws up the #NAME? error value. The #NAME? can also result from wrongly typing in the “CELL” in the formula, for example ‘CELLL’ instead of simply ‘CELL’.

You can know more about hiding errors in a worksheet here.

You can download an example of CELL formula here or click on the button below:

cell-formula-excel


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Aufra wrote:

    I like the way you explain the formula, i am happy…

  2. Robert Martim wrote:

    CELL is actually a “function” not a “formula”. In any case, nice job. I like the stuff you have on your site. The PivotTable data separation tip was really good and useful.

  3. mohan wrote:

    hindi mai sir bata dijiye plz

  1. So How Many Of These Excel Shortcuts Do You Know, Punk ? | Excel & VBA - da Tab Is On wrote:

    [...] quickest way around. Simply select the entire range (even a two dimensional one) and in the first cell, type in the value you would like to copy to all cells of the range. Press CTRL + ENTER. (Bear in [...]

  2. Excel Shortcuts wrote:

    [...] quickest way around. Simply select the entire range (even a two dimensional one) and in the first cell, type in the value you would like to copy to all cells of the range. Press CTRL + [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel