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:
- Logical values (True / False or Boolean values)
- Numeric values (1,23, etc.)
- Text values (any type of written text)
- 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
After entering it, when either the tab key or enter key is pressed, it just automatically converts it to: 100
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:
The 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, floats, or numbers with currency signs, etc. But we can only enter numbers with a maximum of 15 digits.
The 19 significant digits before Enter turn into 15 significant digits after Enter (truncation happens!)
Also, Excel is removing leading zeros. Leading zeros before entering:
No more leading zeros!
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)
The result will be an error type:
Further reading: How to handle errors in Excel? Dealing with blanks Data validation