In Excel, there are five basic data types.
These are Number, Date and Time, Text, Logical (boolean), and Error.
As an Excel user, knowing the different data types is important in creating useful and effective worksheets.
In addition to these standard data types, Excel offers rich data types that allow users to connect to online data sources and enrich their data with additional information.
In this article, we will see each data type individually and see what kind of data it holds.
Number Data Type
Excel uses the number data type to store numerical data such as integers, whole numbers, decimals, percentages, etc. Interestingly enough, dates may also be stored as a number.
You can type in very large numbers in Excel, but for numbers that are larger than 15 digits, those digits after the 15th would be converted to 0.
You can only type 15 significant digits in Excel. Any more significant digits that are typed will get replaced by zeroes.
Numbers in Excel are stored as double-precision floating point values. The range of numbers that can be stored is from -1.79769E308 to +1.79769E308!
By default, numbers get aligned to the left in a cell, and text strings get aligned to the right.
In the picture shown above, the number 12345 is aligned to the right, which tells us that it is stored as a number data type in the backend in Excel.
Sometimes, numbers can be stored as text in a cell in Excel, in which case, you will see that these are aligned to the right.
Date and Time Data Types
Date and Time data type allows us to show dates and time in Excel. While in the backend, date and time values are stored as numbers, in the cell, they are formatted to show the proper date and time values.
These data types are useful for performing date and time calculations, such as sorting the data chronologically or performing calculations or creating Pivot Tables.
The most commonly used date and time data types in Excel are:
- Date: This data type stores dates in the month/day/year or day/month/year format (depending on the user’s system’s clock setting). Excel stores date as sequential serial numbers starting from January 1, 1900 in Windows and January 1, 1904, in Mac.
- Time: This data type stores time values in the format of hour:minute:second. Excel stores time values as decimal fractions of a day.
- Date and Time: This data type stores date and time values in a single cell. It is useful for tracking events that occur at specific times.
Excel also provides various functions to manipulate date and time values.
For example, the TODAY function in Excel gives us the current date, and the NOW function gives us the current date and time.
The YEAR, MONTH, and DAY functions can be used to extract the year, month, or day value from a date. Similarly, the HOUR, MINUTE, and SECOND functions can be used to get the hour, minute, or second value from a time value.
When working with date and time data, it is important to ensure that format of the date/time is acceptable date/time formats. For example, while January 1, 2024 is a proper date format, January 1 2024 is not.
To apply a date or time format to a cell, select the cell and choose the desired format from the Format Cells dialog box.
Also read: How To Combine Date and Time in Excel?
Text Data Type
This data type is used to hold characters such as alphabets, numerical, and special symbols.
They may include words, sentences, addresses, etc. The primary difference between a number and text data type is that you can perform calculations on number data but not text data.
When using text data types, it is important to keep in mind that Excel treats text differently from other data types.
For example, when performing calculations on text data, Excel will treat it as a string of characters rather than as a numerical value.
This means that you cannot perform mathematical operations on text data types.
One useful feature of text data types in Excel is the ability to manipulate the text using formulas and functions.
For example, you can use the CONCATENATE function (or the CONCAT function) to combine two or more text strings together or the LEFT/MID/RIGHT functions to extract specific characters from a text string.
This can be useful for cleaning up data or extracting specific information from text fields.
You can also store numbers as text. You can do that by adding an apostrophe (‘) before the number.
Numbers such as the House Number, Street Number, Block Number, etc., are stored as text data rather than numerical data.
In the picture shown above, the number 12345 appears to be a number data type.
But if you look up the formula bar after selecting the cell, you will see an apostrophe (‘) before the number. This, along with the fact that the number is left aligned by default, shows that this number is stored as text.
The text data type can hold up to 32,768 characters, while only 1,024 characters are displayed visually in a single cell. By default, text data is aligned to the left of a cell.
It may sometimes happen that numbers have been stored as text in the cells in Excel. In such cases, you may not be able to use them in formulas for calculation purposes.
Excel will give you an error if you try to do so. So you may need to manually convert the number from a text data type to a number data type.
Also read: How to Convert Text to Date in Excel?
Logical/Boolean Data Type
The logical data type in Excel is used to store either TRUE or FALSE.
These values can be used in a variety of ways, such as in conditional statements or in calculations that require a binary result.
For example, you can create a formula that checks a value in the cell and returns TRUE if the value matches the given criteria and FALSE if it doesn’t.
When working with boolean values, it’s important to remember that they are case-insensitive. This means that TRUE, True, and true are all equivalent, as are FALSE, False, and false.
This data type is used for comparing data or for checking if certain conditions have been met.
There are four logical expressions inside Excel that return logical data types: AND, OR, NOT, and XOR.
For the sake of an example, in the above screenshot, the AND expression has been used to check if a student has both Score 1 and Score 2 more than 50.
The value shown in the ‘Result’ column is a logical data type that is returned by the AND function.
Also read: Excel Showing Formula Instead Of Result
Error Data Type
The Error data type is used for storing error values.
An example would be when a user tries to divide a number by zero. This cannot be calculated mathematically, and hence, it would give us an error.
Error data types are often displayed preceded by the hash symbol and ending in either an exclamation or a question mark.
Just like the logical data type, error data types often show up as a result of an error in formula.
These errors can happen for a variety of reasons, such as incorrect formulas, invalid references, or data type mismatches.
While the error data type does not represent any value, it is useful for the Excel user so that they can resolve the error.
The different error types are as follows.
- #NAME? Error – This error typically shows up if you have a value inside a formula without quotes or with a beginning or end quote missing. This error may also be caused by a typo in the formula.
In the example shown above, I was trying to sum the values in cells A1 and A2 and show the result in cell A3 using the SUM expression.
I made a mistake in writing ‘SU’ instead of ‘SUM’ and so Excel is showing the #NAME? error.
- #VALUE! This error indicates that the argument or operator in a function is invalid. This happens if you are trying to use a cell containing text in your formula.
In the example above, I am trying to sum up cell A1 and cell A3 and display it in cell A3.
However, Excel shows a #VALUE! error since cell A1 contains ‘NA’ which is not a number.
- #NULL! This error is quite rare in Excel and is the result of a typo where a space character is used instead of a comma (, ) or colon ( : ) between two cell references. For example, typing A2 A5 instead of A2:A5.
You can see in the above picture that I missed typing the colon ( : ) between A1 and A2 in the SUM formula above.
So Excel shows the #NULL! error.
- #REF! – The reference error occurs if you delete a cell or a range of cells that were being used in a formula.
In this picture, I was adding values from cells A1 to D1 and displayed the result in cell E1.
However, I deleted the entire B column, and now Excel shows a missing reference error.
- #N/A – This error occurs when a formula or function cannot find the value it is looking for. For example, if you’re using a VLOOKUP function and it can not find the lookup value, it would return the #N/A error
- #DIV/0! This error code indicates that you are trying to divide a number by zero. Mathematically, the result can not be calculated and is undefined, so Excel shows this error.
In the example above, I am trying to divide the value in cell A1 by 0, and so Excel is showing the #DIV/0! error.
- #NUM!: Num error occurs when a formula contains an invalid numeric value or an operation that produces a result that is too large or too small to be represented in Excel.
In this article, we have seen the different data types that are used by Excel for storing values.
The values can either be Numbers, Date and Time, Text, Logical Values, or Error Values.
Other Excel articles you may also like: