Data types in Excel
In Excel, data type means the type of data which can be stored in an Excel Cell. Basically, there are three data types in Excel.
- Numeric data
- Text data
- Date and Time data (internally stored as numbers)
Excel constantly monitors the data you type in Excel Cell and classifies it into above data types.
Another type which is not shown in the above list is Excel formula. Excel formulas are not constant data, but used to calculate results from constant data available in different Cells of Excel worksheets.
Numeric data
Numeric data stored in Excel Cells are used to build most of the formulas in Excel. There are two types of numeric data;
- Numeric data : Numeric data is used to store quantities. Example - 500 sacks of Cement.
- Date and Time : Date and Time data type is used to store Date and Time values. Excel stores Date and Time also as numbers internally. Click following links to learn more about different date systems in Excel and how does Excel stores time values.
Numeric data type in Excel is used to store different quantities, which can be later used for mathematical operations using Excel formulas.
In Excel (as of 2019), you can store numeric values in an Excel Cell, up to a precision of 15 digits. Let me explain what is the meaning of "15-digit precision".
Refer below image. I am trying to enter a 20-digit numeric value inside Cell A1.
You can see from the below image that once the Cell mode is changed from Enter mode to Ready mode, Excel has changed the last five digits (digits after 15) to 0.
A 15-digit number is a very large number and the above explained limitation (15-digit precision) in Excel will not cause any serious problem in its real-time application.
Following table lists the largest and smallest of positive and negative number possible in Excel (as of 2019).
| Largest or Smallest | Numeric data |
|---|---|
| Largest positive number | 9.9e+307 |
| Smallest positive number | 1e-307 |
| Largest negative number | -1e-307 |
| Smallest negative number | -9.9e+307 |
Excel numeric data type can have numeric characters (0 to 9) in Excel Cell. Apart from numeric characters (0 to 9), following special characters can also be used for different purposes.
| Characters allowed in numeric data type | Purpose |
|---|---|
| + | Positive symbol |
| - | Negative symbol |
| () | Negative symbol |
| % | Percentage symbol |
| . | Decimal symbol |
| , | Decimal symbol |
| E | Exponential symbol |
| e | Exponential symbol |
Text data
In an Excel Cell, the text data consists of a combination of alphabet characters, numeric characters or special characters. The text data is also called as string data. In an Excel Cell, text data is aligned to the left-side of the Cell and numeric data is aligned to the right-side of the Cell.
Even if an Excel Cell contains numeric characters along with alphabet characters, Excel treats that data as text. The text data inside that Cell cannot be used for any mathematical formulas. If you try to perform any mathematical operation with text data in an Excel Cell, you may get #VALUE! error as the result.
If the text data in an Excel Cell is wider than the Cell width, the text data will spill-over the right-side Cells, if the right-side Cells are empty.
If any right-side empty Cell is filled later, Excel hide the spilled text before newly filled Cell to reveal it’s data.
From below image, you can see the text data in Cell A6 is spilled over right-side Cells, because the right-side Cells are empty.
Once you have some data at a right-side Cell (in this case Cell C6), Excel stops the text spillage before the newly filled Cell to make the data inside the newly filled Cell visible.
Date and time
Date and time data is also stored internally as numeric value. Excel stores internally the date and time value as a number known as date-time serial number. Date and time data is also aligned to the right-side similar to numeric data. Data and time data type can be used to store a date value, time value or both. The date and time value can appear of different formats, based on how you format the Cell.
Date and time data can be used to store date, time or both. Remember to store the date value separated by proper date separator characters - or /. Example, 05-12-2021 or 05/12/2021. If there is no proper separator, Excel will treat the value as text data, not as date time data.
Following image shows a date value stored in different formats
As of Excel 2019, dates ranging from January 1, 1900 (date serial number 1) through December 31, 9999 (date serial number 2958465) are supported. If you have any date value beyond this range, Excel will display an unending hash (#) error inside Excel Cell.
Below screen shot shows a date beyond December 31, 9999 (date serial number 2958466). Refer below image to see the error generated by Excel.
Note that in US English, date is written using month/day/year format. But in some other parts of the world, date is written as day/month/year format. The difference in date format at different parts of the world is often confusing. For example; December 5, 2021 is written as 12/05/2021 in US English format, but as 05/12/2021 in India. In omnisecu.com Excel tutorials, I am using day/month/year format.
The separator character used to separate hour, minute and second in time is : (colon). If you want to use AM or PM with a time value, remember to keep one space between value and AM/PM. If there is no space, Excel will consider the value as text.
Following image shows a time value stored in different formats
Note that special the date January 0, 1900 is used to represent the date, where time alone is entered. Visit following lessons to learn more about how Excel stores time values, and how Excel stores date and time values together.