Excel Data Types: Everything You Need to Know

In this article, you’ll have a decisive knowledge about different types of data in Excel. Excel’s variety of data types helps a user to analyze an event or experiment with convenient data available in Excel.

For example, users commonly need to use dates or times in their dataset. Excel offers this specific data type, so people can apply it and make necessary calculations with it. This saves us a sustainable amount of time as we don’t have to convert or transform a data type into another; rather, we can use them directly.

Excel data types can help with data organization, validation, calculations, analysis, reporting, visualization, and data integration. Users can leverage the potential of Excel as a strong tool for data management and analysis by appropriately utilizing these capabilities. Here is an overview of the data types available in Excel.

Notice that the Number data type is on the right side of the cells, whereas the Text data type is on the left side by default. This is the basic visual difference between a number and text data type in Excel.

Download Practice Workbook

What Are Excel Data Types?

There are basically two types of data in Excel. They are:

1. Number
2. Text

Excel also offers some special types of data for geographical and stock analysis. They are available to Microsoft 365 only. The name of these data types is given below.

1. Stock
2. Currencies
3. Geography

Sometimes, we also use or generate logical data in formulas for calculation. The logical data are TRUE and FALSE. The values of TRUE and FALSE data are 1 and 0 respectively. Moreover, when Excel makes a mistake in a formula, it returns an Error which we can also consider a data type.

Excel Data Types: 5 Types with Practical Examples

Earlier, we mentioned the classification of the data types in Excel. In the sections below, we will go through a detailed description of the data types.

1. Number Data

The most commonly used data is the Number data. But this data type has several formats. 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 respectively. For negative numbers, the largest and smallest values are  -1e-307 and -9.9e+307 respectively.

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. We will discuss fractional number formats later. Right now, let’s have a look at the numbers formatted as General.

You can see that there’s actually no specific format to any of these numbers. The number of decimal places in a decimal number can be anything for the General format. However, you can see up to 10 digits of a decimal number in a cell.

Moreover, for large numbers, 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 convert to the Scientific format, as shown below in the above image.

1.2 Number

The Number format shows a number by its exact or approximate value with 2 decimal places by default. You can insert a large number in a cell with this format. However, it’s better to use 15-digit numbers with this format. If your number contains more than 15 digits, you will see 15 significant 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.

As shown in the previous image, you see only the Dollar symbol for different currencies. To change the currency symbol,

• Select the cell containing a number.
• After that, press Ctrl + 1 to open the Format Cells You can also open it from the Number Group in the ribbon.
• Next, choose the desired currency from the drop down list in the Currency option and click OK. Follow the steps marked in the image below to understand the procedure.

Thus, you can select any symbol of currency that is suitable for you.

Note: The number of significant digits for the Currency format is similar to the Number format, meaning you can insert a 15-digits currency 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

The Date format is very useful for the user, as it’s a common data type to work with. 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 from the Format Cells dialog box. Just press Ctrl + 1 to open it and explore those date formats to transform the date data to your suitable format.

Here are a few examples of commonly used date formats in Excel:

Note:

• Remember to choose the format that corresponds to the date conventions in your region. The date formats offered may differ depending on your Excel version and Regional.
• One more thing, 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. Here is a table that gives you an idea of how Excel stores date from the serial numbers.

1.6 Time

As you have seen earlier, 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 part and keep the time part.

Let me provide you with an easy explanation of this data type. You can see that 1.5 is converted to 12:00:00 PM. It’s because 1 is stored as 1st January 1900 in Excel, as we have discussed earlier. 0.5 is half of one which means half a day and so it returns the time 12:00:00 PM. As a result, 1.5 is stored as 12:00:00 PM, 1st January 1900 by Excel, but when it’s converted to Time only, then the date part is removed. Only the Time portion remains.

1.7 Percentage

You can also show the decimal values as percentages when it’s needed in Excel. Basically, we use the percentage to interpret growth or decrease in sales or profit. The following image shows the percentage formatted data.

I will provide an example here to show the use of the Percentage in a practical aspect. Say, the previous and current prices of a product are given. We want to find out the increment or decrement of the price in percentage. What we need first is the difference between the previous and current prices. Then we divide the difference by the previous price. This would return a decimal value by default. Converting this decimal value to a Percentage provides the percentage increment or decrement of the price of the product.

The Percentage formatted data type helps users to convert a decimal value into percentage format without applying a formula for it.

1.8 Fraction

If you insert fractions in Excel without formatting, you will face some issues. For instance, Excel stores “½” as 2nd January by default. If your inserted fraction matches with a date, Excel will return that date. Otherwise, it returns text data. So you cannot calculate fractions properly. Watch the image below to understand this dilemma.

You can see in the above image that the values we get using the fraction operator are not fractional data types. To solve this issue, we can follow two ways. One is putting an equal symbol (=) before each piece of data and converting it to a Fraction.

Now select these fractions and select the Fraction format from the Number group drop down.

However, the fractions we see are not the actual fractions we inserted earlier. In this case, we need to change the formatting of the fractions. The following image shows how you can do this job.

• Here, we selected the cell containing a two digit fraction and opened the Format Cells window by pressing Ctrl + 1.
• After that, we choose Fraction >> Up to two digits and click OK.
• Similarly, we can format the three digit fractional data of cell C7.

After formatting, you will see the data as shown below.

The other way to insert fractional data in a cell is to convert the formatting of that cell to Fraction before inserting it. In that case, you don’t have to put a comma before each data.

You can see that there is no comma before any data from 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, we need to format using the Custom Formatting feature. This will be discussed later in this article.

1.9 Scientific

We need the Scientific format to show very large or small numbers. The following image shows how Scientific formatting may look like.

Here, for the scientific number 2.63E+19, it 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 actual value that we inserted. Because the decimals taken by Excel are two approximate places by default. If you want to increase or decrease the decimal places, click on the Increase or Decrease Decimals button in the Number group. Follow the video below to see the procedure.

In the video, we select the cell containing the scientific formatted number and then click on the Increase Decimal button to increase the decimal places. After that, we used the Decrease Decimal button to do the opposite.

1.10 Text

If you don’t want to calculate with the numeric data in your Excel workbook, you can store them as Text. Moreover, if you want 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 insert the number. Here is an image showing numbers in the Text format.

Note: Text formatted data shows the Number Stored as Text error. You can ignore it as you don’t want to perform calculations using them and the error doesn’t matter in this case. Just click on the drop down shown in the image below and select Ignore Error.

1.11 Custom Formatting Data

Excel offers more formatting to numeric data which you can find in the Custom option of the Format Cells window. I’m going to show a few examples so that you can explore this feature and be able to customize the formatting of a cell on your own.

Now, to apply the Custom format feature,

• Select cells for where you want to apply the Custom option.
• After that, press Ctrl + 1, and it will open the Format Cells dialog box.
• Under Category, select Custom.

Showing Thousands and Millions Units by Formatting Cells

Say, we want to show the numbers in thousands and millions. In the Type section, write the code below to show the numbers in the thousands of units with separators, and click OK.

After that, if you insert the numbers in column B in the formatted cells, you will see these numbers in the Thousands format with separators.

And using the code below will return the numbers in Millions format with separators.

Showing Fractions

Earlier, we mentioned that you can format fractional data according to your convenience. If a fraction has more than 4 digits of denominators or numerators and we want to display it in an Excel cell, we have to use the Custom format feature for it.

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

Here, we just use some # symbols to determine the number of digits in a fraction.

Insert some 4 digit fractions, and you will see them in the proper format.

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

Thus, you can format the fractional data types according to your choice in Excel.

Formatting Negative Numbers

We can also format the negative numbers using the following codes. It is sometimes preferable to display negative numbers differently so that the user understands that the data has decreased values.

Codes:

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

There are many more formattings available in Excel’s Custom feature. We’ve shown some commonly used formattings here. Use the data type from these formattings that suits your dataset.

1.12 Logical Data

Logical data are also numeric data in Excel. We denote them as TRUE and FALSE. The values of TRUE and FALSE are 1 and 0 only. They are very useful for formulas when we apply conditions in them.

We have provided a short introduction to some common logical functions in Excel. These functions return TRUE or FALSE based on the operations

• AND: We use this if we want to find out whether the data meets multiple conditions. Say, we have different values in two cells. The condition is: one should be greater than zero while the other should be less than 1. We can find out whether both of these logics are true or false by the AND function.
• OR: This function compares values or statements in the Excel sheet that satisfies a condition. If none of the data meets your criteria, Excel returns a False value.
• XOR: The XOR function is used when only one of your data’s arguments can be labeled as True or False.
• NOT: You can use it when you want to filter arguments that don’t match your conditions. Arguments in the data that fulfill your conditions are marked False, while those that do not are marked True by this function.

2. Text Data

Anything that is not numeric data is stored as Text data in Excel. However, numeric data can be stored as Text which you have seen in the previous section. But here, we’ll keep our discussion on Text data only.

Text data includes characters such as alphabetical, numerical, and special symbols. We know that text data cannot be applied for calculation. But it’s useful in some cases. For example, if your Excel file contains date data, and you want to work with this file on another user’s PC, there is a possibility that the date data may convert to numbers. If you have stored them as Text using the TEXT function, this problem won’t happen.

In the picture above, I’ve shown some commonly used data types that are stored as Text in Excel. Here you can see that alphabets, words, sentences, symbols etc. are in Text format. If you store dates or times following other conventions that are not familiar with Excel, they will be stored as Texts too. Notice that the date in the image has dots as separators, which is not compatible for calculations by Excel. So this date and the time in cell C10 are stored as Texts.

There are no default options to format Text data as we have seen for numeric data. But you can add texts automatically with other text data that you insert in a cell. Let’s have a look at the procedure below.

Say, we have some names and we want to add ‘Mr.’ before each name. For this purpose,

• Select the cell range that will store the names and press Ctrl + 1 to open the Format Cells.
• After that, select Custom and insert the code below in the Type option.

• Now, type the names, and you will see ‘’ is added before each name.

Another interesting thing is, you can also add texts with numeric data. Say, you want to add ‘years old’ after each age of the person in the dataset. Just use the code below similarly for the column that contains ages.

After that, type the ages in the cells, and you will see the desired text added to each of them.

Note: The Age data are numeric, meaning they can be used for calculation purposes. In other words, generally adding texts to numeric data using the Custom formatting feature does not convert numeric data to text.

3. Compound Data

Compound data is a special kind of data which are basically mathematical functions. Here, we created a function that calculates the multiplication of two numbers divided by two. The LAMBDA function can be applicable to create this custom function. The function made by the LAMBDA function is compound data. The numeric value of the Compound data type is 128. We used the TYPE function to show how Excel identifies the 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, we cannot add text with other numeric data. So if you do this, Excel will return a #VALUE! Error. Here is an image showing this error.

Here we added 5 and A which is not a compatible calculation. So Excel returns an error. The numeric value for the error data type is 16.

The following description shows some error data that commonly occur in calculations.

• #VALUE!: In Excel, the #VALUE! error often arises when a calculation has one or more cells with the incorrect data type or an incompatible operation. For example, attempting to calculate the sum of a range of cells where one cell has alphabetical characters can result in a #VALUE! result.
• #REF!: If you remove or paste things in a cell or range of cells where you previously input a formula, an invalid cell reference error value may result. To resolve this problem, undo your earlier action and enter your new data into a cell or cell range that does not contain a formula.
• #N/A: Excel returns this error value when there is no value available for the function to calculate. This error implies that a formula is unable to locate the value of interest.
• #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: This error value may appear if you attempt to divide an integer by zero. Excel utilizes #DIV/0 to signify when you can try a new equation because the result is an undefined integer.
• #NULL!: When you insert an erroneous range reference in a formula, Excel displays the #NULL! error. This error indicates that the formula range you specified is invalid.

5. Excel Linked Data Types (Special Features)

Excel offers some special Data Types to ease the calculations regarding Stock and Geography analysis. The following section covers the basics of these data types.

5.1 Stock Data Type

The Stock data type in Excel is a recent addition (available in Excel 365 or Excel 2021) that allows you to obtain real-time stock and investment information directly into your spreadsheets. It gives you access to information such as stock prices, company information, historical data, and more.

• To operate with Stock data, we need to type the keywords or full names of the companies.
• After that, select the company name range and go to Data >> Stock from the Data Types group.

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

Here, we extracted the Headquarters of the Stock companies.

You can see that there are more options available to retrieve other important data. Thus Excel’s Stock data type helps users to analyze different aspects of the Stock companies.

5.2 Currencies Data Type

Excel can be a useful tool to know about currency rates. If your workplace requires global economic analysis, you may need to know the conversion between different currencies.

The Currencies Data Type is actually a part of the Stock Data Type. Both of these data types open the same fields for the data recognized by them. You can find this data type just beside the Stock Data Type in the Data tab.

Say, we want to know the currency amount of Euro against 1 US Dollar. For this reason, you should type “USD/EUR” or “USD:EUR” in a cell and select the Currencies Data Type for this cell. Apply this feature in the same way we did for the Stock Data Type.

Here, we have shown some currency rates against another currency. For example, the 7th row in the Excel sheet provides the information that 1 US Dollar is worth 82.03 Indian Rupees.

5.3 Geography Data Type

The Geography Data Type returns the necessary information about different places of the world. This is a different data type from the other two we just described.

To know the geographic information about specific places, we need to insert their names in the Excel cells and apply the Geography Data Type to them. The following image shows the procedure for applying this feature to selected cells.

This will convert the above places to Geography data. If you click on the marked icon below in the image, you will see various fields about those places to look at such as Area, Country, Population, Longitude, Latitude etc. Right now, we want to know the regions of these places, so we select the Country/region option.

This operation extracts the countries or regions of corresponding places.

Thus, the Geography Data Type is useful for statistical data of different places in the world.

How to Create Data Types in Excel

We have seen the default Data Types available in Excel, which are Stock, Currencies and Geography. But users can also create their own data types. However, this data type cannot be used outside the workbook. Still, it can be useful to save data from the website and use them when needed.

Here, we want to create a list of the top scorers of the UEFA Champions League. Please follow the steps below.

• First, select Data >> From Web.

• After that, the From Web window will appear. Insert a link that has the list of top scorers of UCl.
• Next, click OK.

• The tables available on the website will be shown as a preview in the Navigator Search for the desired table and click the Transform Data button. Here, Table 0 contains the list of the players.

• The table will appear in the Power Query Editor. This step is a bit tricky. Select the column heading of the column that you want to appear first for the linked data.
• After that, hold the Ctrl button and select other column headings of the columns that contain the necessary data you want to represent in the Data Type. Here I have selected the column headings marked by serial numbers 1 to 5.
• Next, we select Transform >> Create Data Type.

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

• Later, select unnecessary columns, right-click on any of them and remove them by selecting the Remove Columns option from the Context Menu.

• After that, select Home >> Close & Load.

The linked data type will appear in a new sheet. Click on the icon marked as 1 and you will see the fields which we selected earlier in the Power Query Editor. Here, we choose goals to show the number of goals of corresponding players.

The goals of the corresponding players will be filled in the table.

Thus you can create a custom Data Type in Excel for user purposes.

[Fixed!] Excel Data Types Missing

Sometimes the data types may not be seen in the Data tab. These data types are available only on Excel 365 and Excel 2021. If you use an older version of Excel, you may not be able to access this feature. However, even if you don’t have this feature using Excel 365 or Excel 2021, there’s a solution for this.

1. Restart the PC

Sometimes, Excel files can be bugged so that it doesn’t work properly. Hence, the Data Types can be missing. Restarting the PC can be the primary solution to this issue as this starts the PC from the beginning and closes all the apps that run in the background. So restart the PC and see if the issue gets fixed. If this doesn’t help, follow the next solution.

2. Repair Or Reset Excel 365

By resetting the Excel App, we can simply resolve the missing Data types Stocks and Geography. Several things can contribute to Microsoft Office 365 installation problems. Almost the majority of these issues can be fixed by reinstalling the Office apps. Let’s fix the problem by following the steps below!

• To begin, press Windows + I on your keyboard to open Settings Now, select Apps >> Installed Apps.

• Next, search for the Office app and click on the dotted icon marked as 2 in the picture below.
• After that, select Advanced options.

• Thereafter, choose Repair or Reset according to your choice. If you want the Excel app data unchanged or intact, repair the app. If that doesn’t work too, reset it and start from the beginning. However, resetting will remove your app data.

Thus you can solve the issue of Excel Data Types missing issue.

How to Check Data Type in Excel

We have shown a few examples of how Excel identifies some data types by numeric values. Here is a table showing 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

• Be aware that depending on the context, Excel may automatically transform data from one type to another. If you enter a number as text, Excel may convert it to a numeric value if the pattern is recognized. This automated conversion, however, is not always ideal, therefore double-check the data type after entry.
• Different Excel formulas and functions have different data type requirements. Check that the data you’re referencing or passing as arguments is of the expected type. Incompatible data types might lead to errors or unexpected outcomes.
• Excel may treat numbers and text differently when sorting or filtering data. Text values are normally sorted alphabetically, while numerical values are often ordered ascending or downwards. Ascertain that your data is correctly sorted according to the specified data type.
• Pay attention to the data types in the source and destination when importing data into Excel or exporting it to other apps. To avoid data loss or conversion concerns, ensure that the data types are compatible and appropriately mapped.

Frequently Asked Questions

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

Answer: Excel employs error values (for example, #DIV/0!, #VALUE!) to show calculation problems or data type incompatibilities. To find and handle errors in formulas, use error-handling functions such as IFERROR, ISERROR, or ISERR.

2. What type of files are supported in Excel?

There are various types of files compatible with Excel. They are:

• 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)

Conclusion

In the end, we can consider that this article will guide you through all about Excel data types. Excel allows you to use various data types to analyze statistical events or experiments, complex calculations etc. If you have any ideas or questions regarding this article, please leave them in the comment box. Your ideas will enrich my upcoming articles.

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 Entering and Editing Data in Excel | Learn Excel