How Excel handles different data types?

In this article we will guide you through basics of Excel especially the different data types used in Excel.

Excel understands 4 kinds of information:

  1. Logical values (True / False or say Boolean values)
  2. Numeric values (1,23, etc.)
  3. Text values (Any written text)
  4. Error Types.

So when you Enter any type of data in a cell and when you press enter the excel recognize is at any of the above mentioned data types. For example you can try to type a number with leading zeroes : 00000100

Leading zeros before enter

After entering it when either tab key or enter key is pressed it just automatically converts it to: 100

Leading zeros before enter result

Lets discuss the four types in detail:

Logical Data type: True or False

Lets check it with formula to compare 2 values:

formula to compare 2 values

Result is as follows:

formula to compare 2 values result

So when the condition is analyzed the result is a Boolean value : True.

Number Data type:

It includes all types of numbers which can be numeric or float or numbers with currency signs etc. But we can only enter numbers with maximum 15 digits:

15 Significant Digits After Enter

19 significant digits before Enter turns into 15 significant digits after Enter (truncation happen!)

Significant Digits After Enter result

Also Excel is removing leading zeros. Leading zeros before enter:

number with leading zeroes

No more leading zeros!

zeros automatically converted

For Excel 0000100 was a number. If you need these leading zeros just change data type to Text.

Text Data Type

Excel will recognize a text string of up to 32,768 characters. However, only 1024 can be displayed in a cell.

Error Data Type

Sometimes when we evaluate a result which has answer in an error e.g dividing with zero (20/0)

illegal divide

The result will be an error type:

error div

Further reading: 
How to handle errors in Excel?
Dealing with blanks 
Data validation