Excel Data Types – Everything You Need to Know

This is an overview:

The Number data type is displayed on the right side of the cells, whereas the Text data type is displayed on the left side by default.

Download Practice Workbook

What Are Excel Data Types?

There are basically two types of data in Excel:

1. Number
2. Text

Excel also offers some special types of data for geographical and stock analysis. They are available in Microsoft 365 only:

1. Stock
2. Currencies
3. Geography

Logical data is used in formulas. The values of TRUE and FALSE are 1 and 0.

Excel Data Types – 5 Types with Practical Examples

1. Number Data

The following image shows the default Number formats available in Excel.

The largest and smallest positive numbers in Excel are 9.9e+307 and 1e-307. For negative numbers, the largest and smallest values are  -1e-307 and -9.9e+307.

1.1 General

The General format is the default format of Excel data. When you type any data in an Excel cell, it will be in the General format, except for fractional numbers.

The number of decimal places in a decimal number can be anything for the General format. However, you can only see up to 10 digits of a decimal number in a cell.

If the number of digits is more than 11, you will see no change in the format. But if the number digit crosses 11, the number will automatically be converted to the Scientific format.

1.2 Number

The Number format shows a number by its exact or approximate value with 2 decimal places by default. If your number contains more than 15 digits, you will see 15 digits and any digit after the fifteenth is replaced with zero.

1.3 Currency

The Currency format adds the currency symbol before any number. The currency symbol is added based on the Regional Settings of your computer.

To change the currency symbol,

• Select the cell containing the number.
• Press Ctrl + 1 to open Format Cells.  You can also open it in Number on the ribbon.
• Choose a currency in Currency and click OK.

This is the output.

Note: The number of digits for the Currency format is similar to the Number format: you can insert a 15-digits in a cell. Any currency data that has more than 15-digits will have zeros after the 15th digit.

1.4 Accounting

The Accounting format is similar to the Currency format. The only difference is that the currency symbol lies on the left side of the cell while the number stays on the right.

1.5 Date

By default, there are two types of date formats. They are:

1. Short Date
2. Long Date

The following image shows the date 5th January 2023 in these two formats.

You can find more date formats in the Format Cells dialog box. Press Ctrl + 1 to open it and explore the date formats.

These are examples of commonly used date formats in Excel:

Note:

• Choose the format that corresponds to the date conventions in your region.
• Dates are basically serial numbers starting from 1 to 2958465. 1 represents the date 1st January, 1900. Similarly, 2 and 3 represent the 2nd and 3rd days of January 1, 1900. The table below shows how Excel stores date in the serial numbers.

1.6 Time

Dates are serial numbers in Excel starting from the 1st of January 1900 which is stored as 1. If you insert a decimal number, Excel will return date and time formatted data. But when you select the Time format only, it will remove the date.

In the image above, 1.5 is converted to 12:00:00 PM (1 is stored as 1st January 1900. 0.5 is half one: half a day –12:00:00 PM).

1.7 Percentage

You can also show the decimal values as percentages. The following image shows percentage formatted data.

In the dataset below the previous and current prices of a product are given. To find the increment or decrement of the price in percentage, calculate the difference between the previous and current prices. Then divide the difference by the previous price. It will return a decimal value by default. Convert this decimal value to a Percentage to see the percentage increment or decrement.

1.8 Fraction

If you insert fractions in Excel without formatting, you will face issues. Excel stores “½” as 2nd January by default. If your inserted fraction matches a date, Excel will return that date. Otherwise, it returns text data.

• To solve this issue, enter an equal symbol (=) before the data and convert it to a Fraction.

• Select the fractions and choose the Fraction format in Number.

The fractions displayed are not the fractions entered: you need to change the formatting:

• Select the cell containing a two digit fraction and open the Format Cells window by pressing Ctrl + 1.
• Choose Fraction >> Up to two digits and click OK.
• Format three digit fractional data in C7.

This is the output.

You can also convert the cell formatting to Fraction before entering fractional data.

There is no comma before data in the above image.

Note: You can also format fractional data as Halves, Quarters, Eighths etc. in Excel (see the 4th image of this section) by default. If your fraction contains a lot of digits, you need to format it using the Custom Formatting feature. 

1.9 Scientific

The Scientific format shows very large or small numbers:

2.63E+19 is equivalent to 2.63 multiplied by 10 raised to the power of 19, which can be written as 2.63 x 10^19 or 263 x 10^17. This means that the number is 263 followed by 17 zeros: 26,300,000,000,000,000,000. However, it’s not the entered value, because the decimals taken by Excel are two approximate places by default. If you want to increase or decrease the decimal places, click Increase or Decrease Decimals in Number .

1.10 Text

You can store data as Text. To store a large number that is not compatible with any other number format and you want to display the number as it is, you can convert the cell format to Text and enter the number:

Note: Text formatted data shows the Number Stored as Text error. You can ignore it.

1.11 Custom Formatting Data

Customize the formatting of a cell:

• Select cells to apply the Custom option.
• Press Ctrl + 1 to open the Format Cells dialog box.
• Select Custom.

Showing Thousands and Millions Units by Formatting Cells

To show the numbers in thousands and millions:

• In Type, enter the code below to show the numbers with separators, and click OK.

If you enter numbers in column B, you will see the Thousands format with separators.

• Use the code below to see the numbers in Millions format with separators.

Showing Fractions

If a fraction has more than 4 digits of denominators or numerators and you want to display it in an Excel cell, use the Custom format feature.

• Use the code below for 4 digit fractions in Type of the Format Cells window.

• Use # symbols to determine the number of digits in a fraction.
• Enter 4 digit fractions.

This is the output.

For 5 digit fractions, just add another # symbol. You may have to add an equal symbol before the fractional data if Excel stores the value as Text.

Formatting Negative Numbers

You can also format negative numbers using the following codes.

Codes:

1. #.00; (#.00)
2. 0.00_); (0.00)

1.12 Logical Data

Logical data is also numeric data in Excel. It is TRUE and FALSE. The values of TRUE and FALSE are 1 and 0 only.

These are common logical functions in Excel returning TRUE or FALSE based on the operations:

• AND: to find out whether the data meets multiple conditions.
• OR: compares values or statements that meet a condition.
• XOR: is used when only one of the data arguments can be labeled as True or False.
• NOT:  filters arguments that don’t match the conditions.

2. Text Data

Anything that is not numeric data is stored as Text data in Excel. Numeric data can also be stored as Text as described previously. section.

Text data includes alphabetical and numerical characters, and special symbols.

The picture above shows commonly used data types that are stored as Text in Excel.

There are no default options to format Text data but you can add texts automatically.

To add ‘Mr.’ before each name:

• Select the cell range and press Ctrl + 1 to open Format Cells.
• Select Custom and use the code below in Type.

• Enter the names to see the output.

You can also add texts with numeric data.

To add ‘years old’ after the age in the dataset:

• Use the code below.

• Enter the ages to see output.

Note:  Age data is numeric and can be used for calculation purposes. 

3. Compound Data

Compound data refers to mathematical functions.

A function was created to calculate the multiplication of two numbers divided by two. The LAMBDA function can be used to create this custom function. The numeric value of the Compound data is 128. The TYPE function shows how Excel identifies Compound data with this numeric value.

Note: The LAMBDA function can be used to create custom functions without the help of VBA.

4. Error Data

If Excel identifies data mismatch, improper function application, or any kind of malfunction in the sheet, it returns Error data. For example, if you add text to numeric data, Excel will return a #VALUE! Error:

5 was added to A, which is not a compatible calculation. The numeric value for the error data type is 16.

These are common errors:

• #VALUE!: a calculation has one or more cells with incorrect data type or an incompatible operation.
• #REF!: If you remove or paste data in a cell or range of cells where you previously input a formula, an invalid cell reference error value may result.
• #N/A:  there is no value available for the function to calculate.
• #NUM!: If you enter an invalid formula or function, a #NUM! value may show. It may also occur if the total produced by a formula or function is too large for Excel to display in a cell.
• #NAME?: If you have a value inside a formula without quotations or with a missing beginning or end quote, you may see this value. It may also happen if there is an error in the formula.
• #DIV/0: If you attempt to divide an integer by zero, Excel displays #DIV/0.
• #NULL!: When you insert an erroneous range reference in a formula, Excel displays the #NULL! error.

5. Excel Linked Data Types (Special Features)

5.1 Stock Data Type

• To operate with Stock data, enter the keywords or full names of the companies.
• Select the company name range and go to Data >> Stock in Data Types.

The selected companies will be converted to Stock. You can access a lot of information about the companies by clicking the icon marked 2 in the image below.

Here, the Headquarters of the Stock companies.

5.2 Currencies Data Type

To know the currency amount of Euro against 1 US Dollar:

• Enter “USD/EUR” or “USD:EUR” in a cell and select Currencies Data Type for this cell.

The image above displays  currency rates.

5.3 Geography Data Type

The following image shows how to apply this feature to selected cells.

The above places are converted to Geography data. If you click the icon marked below, you will see several fields about the place: Area, Country, Population, Longitude, Latitude etc.

• To know the regions of these places, select Country/region.

This is the output.

How to Create Data Types in Excel

To create a list of the top scorers of the UEFA Champions League:

• Select Data >> From Web.

• In the From Web window, insert a link that has the list of top scorers of UCl.
• Click OK.

The tables available on the website will be shown as a preview in the Navigator Search.

• Click Transform Data. Here, Table 0 contains the list of players.

The table will be displayed in the Power Query Editor.

• Select the column heading of the column that you want to appear first in the linked data.
• Hold Ctrl and select other column headings. Here, 1 to 5.
• Select Transform >> Create Data Type.

• In the Create Data Type window, name this Data Type and click OK.

• Select unnecessary columns, right-click any of them and remove them by selecting Remove Columns.

• Select Home >> Close & Load.

The linked data type will appear in a new sheet.

• Click the icon marked as 1 and you will see the fields selected earlier in the Power Query Editor. Choose goals.

The goals different players scored will be displayed.

Excel Data Types are Missing – Solutions

Data types may not be seen in the Data tab.

1. Restart the PC

Sometimes, Excel files can be bugged and Data Types can be missing. Restarting the PC can be the primary solution to this issue.

2. Repair Or Reset Excel 365

Most issues can be fixed by reinstalling the Office apps.

• Press Windows + I to open Settings.
• Select Apps >> Installed Apps.

• In the Office app, click the dotted icon marked 2 in the picture below.
• Select Advanced options.

• Choose Repair or Reset. If you want the Excel app data unchanged or intact, repair the app. If that doesn’t work, reset it. Resetting will remove your app data.

How to Check Data Type in Excel

The table shows the numeric values for all data types.

Length of Different Numeric Data Type in Excel

The following table shows the size of different numeric data in bytes in Excel.

Things to Remember

• Depending on the context, Excel may automatically transform data from one type to another.
• Different Excel formulas and functions have different data type requirements.
• Excel may treat numbers and text differently when sorting or filtering data. Text values are normally sorted alphabetically, while numerical values are often sorted in ascending or descending order.
• Mind data types in the source and destination when importing data into Excel or exporting it to other apps. Ensure data types are compatible and appropriately mapped.

Frequently Asked Questions

1. How can I identify and handle errors in Excel data types?

Answer: Use error-handling functions such as IFERROR, ISERROR, or ISERR.

2. What type of files are supported in Excel?

These files are compatible with Excel:

• Excel Workbook (.xlsx)
• Excel Macro-Enabled Workbook (.xlsm)
• Excel Binary Workbook (.xlsb)
• Template (.xltx)
• Template (Code; .xltm)
• Excel 97- Excel 2003 Workbook (.xls)
• Excel 97- Excel 2003 Template (.xlt)
• Microsoft Excel 5.0/95 Workbook (.xls)
• XML Spreadsheet 2003 (.xml)
• XML Data (.xml)
• Excel Add-In (.xlam)
• Excel 97-2003 Add-In (.xla)
• Excel 4.0 Workbook (.xlw)
• Works 6.0-9.0 spreadsheet (.xlr)

Related Articles 

• How to Enable Editing in Excel 
• How to Press Enter in Excel Without Changing Cells
• Making Same Change to Multiple Worksheets in Excel

<< Go Back to Learn Excel