30 Excel Functions in 30 Days: 21 – TYPE

Icon30DayYesterday, in the 30XL30D challenge, we got cell addresses with the ADDRESS function, and combined it with INDIRECT to get a cell’s value.

For day 21 in the challenge, we’ll examine the TYPE function. It identifies the type of value in a cell, by returning a number.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the TYPE information and examples, and if you have other tips or examples, please share them in the comments.

Function 21: TYPE

The TYPE function returns a number, that identifies a value’s type:


Here’s the list of results, and the data types:


How Could You Use TYPE?

The TYPE function can tell you what kind of value is in a cell, but the logical functions, like ISERROR, ISTEXT, etc., will also check for a specific data type. However, if you just want to know what’s in a cell, the TYPE function can:

  • Identify cell value type by number
  • Test for Number type before multiplying

TYPE Syntax

The TYPE function has the following syntax:

  • TYPE(value)
    • value can be text, number, error, or any other value

TYPE Traps

Unfortunately, the TYPE function cannot identify cells that contain a formula. It only shows the type for a cell’s contents, or a formula’s result. In a few versions of Excel, the Help files incorrectly reported that a formula would return 8 with the TYPE function. This MSKB article corrects that error.


Example 1: Identify cell value type by number

The TYPE function returns number, based on a value’s type, so you can use it to see what’s in a cell. For example, if you type 123 in cell B3, the result of this formula is 1 — Number.



However, if there is an apostrophe in front of the number, the TYPE function result would be 2 — Text.


Example 2: Test for Number type before multiplying

You could use the TYPE function with CHOOSE, to multiply valid numbers, or show a message, if something else is entered.

=CHOOSE(MIN(TYPE(B3),3),B3*C3,”No text”,”Enter Qty”)

  • If a number is entered in B3, the TYPE function returns 1, so the CHOOSE function returns the result of B3*C3.


  • If text is entered in B3, the TYPE function returns 2, so the CHOOSE function returns the message “No text”.


  • If anything else is entered in B3, the TYPE function returns a 4 or higher. The MIN function result will be 3, so the CHOOSE function returns the message “Enter Qty”.


Download the TYPE Function File

To see the formulas used in today’s examples, you can download the TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the TYPE Video

To see a demonstration of the examples in the TYPE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Identify Cell Contents with Excel TYPE Function


You may also like...

3 Responses

  1. Rahul says:

    Very good and effective post. Thanks for posting such and interesting and effective post. All the 30 functions are very useful for better spreedsheet development. Keep on doing this service more and more. Post many useful articles in excel so that it will be helpful for excel learners.

  2. John Hackwood says:

    Wow really great article I hadn’t ever thought to use CHOOSE in this way.

  1. February 5, 2013

    […] TYPE function was originally designed to show what a cell contained, such as text or a formula. It returns a […]

Leave a Reply

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