How Excel handles different data types?

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

Excel understands four kinds of information:

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

So, when you enter any type of data in a cell and press Enter, Excel recognizes it as one of the data types listed above. For example, you can try to type a number with leading zeroes: 00000100

Leading zeros before enter

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

Leading zeros before enter result

Let's discuss the four types in detail.

Logical Data type: True or False

Let's check it with a formula to compare two values:

formula to compare 2 values

The 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, floats, or numbers with currency signs, etc. But we can only enter numbers with a maximum of 15 digits.

15 Significant Digits After Enter

The 19 significant digits before Enter turn into 15 significant digits after Enter (truncation happens!)

Significant Digits After Enter result

Also, Excel is removing leading zeros. Leading zeros before entering:

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

To check data type just click CTRL + 1.

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:

excel data type error div

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