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**

**Table of Contents**Expand

## 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:

**Short Date****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:

Date Format | Description | Example |
---|---|---|

MM/DD/YYYY |
Displays the date in the format of month/day/year | 01/05/2023 |

DD/MM/YYYY |
Displays the date in the format of day/month/year | 05/01/2023 |

YYYY-MM-DD |
Displays the date in the format of year-month-day | 2023-01-05 |

MMMM DD, YYYY |
Displays the date in the format of month spelled out, followed by day and year | January 05, 2023 |

DD/MMM/YYYY |
Displays the date in the format of day/month abbreviated/month spelled out/year | 05/Jan/2023 |

**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.

Number | Date |
---|---|

1 | 01/01/1900 |

2 | 01/02/1900 |

3 | 01/03/1900 |

… | … |

2958465 | 12/31/9999 |

**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**.

`#,###.00,\K`

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.

`#,##0.000,,\M`

**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.

`General; General; General; "Mr. "@`

- 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.

`0 "years old"`

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.

Data Type | TYPE Function Output |
---|---|

Number | 1 |

Text | 2 |

Logical value | 4 |

Error value | 16 |

Array | 64 |

Compound data | 128 |

## Length of Different Numeric Data Type in Excel

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

Data Type | Size (Byte) | Description |
---|---|---|

Byte | 1 | A number ranging from 0 to 255 that is used to store binary data. |

Integer | 2 | Integer from -32,768 to 32,767. |

Long | 4 | Integer from -2,147,483,648 to 2,147,483,647 |

Single | 4 | Float precision to 6 decimal places |

Double | 8 | Float precision with double precision |

Decimal | 14 | Fixed precision and scale (precision up to 28). |

Boolean | 2 | Logical value (TRUE/FALSE) |

String | Text object. Flexible length or 64 kilobytes. | |

Object | 4 | Reference to an object. |

Date | 8 | Date Range: 1/1/100 to 12/31/9999 |

Currency | 8 | A number with fixed 4 decimal places |

Variant | 16 | Special values such as Null, numeric value, text, reference to the object or variable array. |

## 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**