ERROR.TYPE and CELL: Important Information Functions in Excel

Information Functions can be used when one deals with important data. Two important functions in this category are CELL and ERROR.TYPE. We will look at these in detail in this article.

Information Functions – CELL

This function can be used to return the information of the cell one is entering in.

For example, Sreya has the following sheet. She has to mention the kind of formatting she has used for each row at the end of the row so that Sravan can make a sheet just like hers.  To do this, she can use the CELL function.

CELL ERROR.TYPE 1

Syntax:

=CELL(information,[reference])

Here, ‘information’ refers to the type of information that needs to be displayed. The options available are as follows:

  • address
  • col
  • color
  • contents
  • filename
  • format
  • parentheses
  • prefix
  • protect
  • row
  • type
  • width

‘Reference’ option is an optional entry that can be used to include the cell reference of the cell you want to display information about.

The formula she can use is

=CELL(“format”,B1) and drag it to the rest of the cells of the row.

the result will appear as follows

CELL ERROR.TYPE 2Alternatively, she can go to the ‘Function Library’ in ‘Formulas’ tab and choose ‘More Functions’. Here she should choose ‘Information’ and then ‘CELL’. The following dialogue box will appear.

CELL ERROR.TYPE 3

She can give the necessary details and click ‘OK’ to proceed.

Information Functions – ERROR.TYPE

This function can be used to return a particular value instead of displaying error on Excel.

For example, in the sheet that Sreya was using, she enters a function wrong and the error displayed is as ‘#NAME?’.

CELL ERROR.TYPE 4

She wants to replace this with a value. She can do this using this function.

Syntax:

=ERROR.TYPE(error_value)

Here, ‘error_value’ refers to the number to be displayed in case of error. The error types and their subsequent values are as follows:

  • #NULL! – 1
  • #DIV/0 – 2
  • #VALUE! – 3
  • #REF! – 4
  • #NAME? – 5
  • #NUM! – 6
  • #N/A – 7
  • #GETTING_DATA – 8
  • All other errors – N/A

To display her error as a value, she can use the following formula

=ERROR.TYPE(B5)

The result will display the number corresponding to the error.

Alternatively, she can go to the ‘Function Library’ in ‘Formulas’ tab and choose ‘More Functions’. Here she should choose ‘Information’ and then ‘ERROR.TYPE’. The following dialogue box will appear.

CELL ERROR.TYPE 5

She can give the necessary details and click ‘OK’ to proceed.

Leave a Comment

Your email address will not be published. Required fields are marked *