How Excel handles different data types?
- Logical values (True / False or say Boolean values)
- Numeric values (1,23, etc.)
- Text values (Any written text)
- 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
After entering it when either tab key or enter key is pressed it just automatically converts it to: 100
Lets discuss the four types in detail.
Logical Data type: True or False
Lets check it with formula to compare 2 values:
Result is as follows:
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:
19 significant digits before Enter turns into 15 significant digits after Enter (truncation happen!)
Also Excel is removing leading zeros. Leading zeros before enter:
No more leading zeros!
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)
The result will be an error type: