# Data Types in Excel and Their Uses: A Complete Guide

Learn to identify and format all types of data in Excel, then explore valuable tips on converting between data types to make your spreadsheet more functional.
Jun 2024  · 9 min read

If you work with data, it’s important to understand the different data types in Excel. This will help you input data correctly and perform the appropriate operations.

In this article, we will explore data types such as `Number`, `Text`, `Dates`, and more—explaining how you can identify them and convert them to support your spreadsheet's functionality.

If you're new to Excel, start with our Introduction to Excel course to build a solid foundation.

## How to Identify Data Types in Excel

To determine the data type in Excel, right-click on any cell or a range of cells and choose Format Cells. The Number tab here will display the available data types, such as `Number`, `Date`, or `Text`

Selecting the Format Cell option. Source: Image by author

List of data types in Excel. Source: Image by author

Alternatively, you can go to the Number group from the Home tab and see the data types.

Different data types as displayed in the Home tab. Source: Image by author

## Different Data Types in Excel

Data types help you insert data into the document correctly. If there are incorrect data types within the dataset, the data can be misrepresented and cause calculation errors. So, knowing what data types are suitable for datasets is important.

Let’s take a look at some common data types in Excel:

### Number data

Number data can be any value, including large numbers, small fractions, or qualitative data. In this section, we will take a look at examples including currency amounts, whole numbers, percentages, decimals, dates, times, and telephone numbers. As we will see, in order to ensure that Excel interprets your numeric data accurately, we have to define them using proper symbols and formats.

Keep in mind, there are subtle differences. For example, in a case where one cell has financial data and another has a date, Excel registers them both as `Numeric`, yet they are not identical.

#### Currency

You will be familiar with the `Currency` data type if you work with financial data. It formats monetary values and ensures that financial data is accurately represented by appropriate currency symbols and decimal places.

Here’s how you can apply the currency data type. Here we will convert numbers into currency.

• Select the range of cells you want to change.
• Right-click.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Currency data type and format your values.

Converting numbers into Currency Format. Source: Image by author

Numbers converted to Currency values. Source: Image by author

All the selected columns have been converted to the currency data type. You can modify the currency symbol and decimal placement according to your preferences.

#### Date and Time

`Date` and `Time` data types store dates and times in different formats. These formats help with chronological data analysis, scheduling, and time-sensitive calculations.

Let's take a look at the example of how to convert a `Text` into a `Date`. For time formatting, follow these steps and select the `Time` data type instead.

• First, select the cell you want to change.
• Right-click on the cell.
• Select the Format cells option.
• Go to the Number tab.
• Select the Date data type and format your values.

• If you want to write the current time, simply write `=NOW()`
• To convert the date to `Text`, all you have to do is apply  `=TEXT(Cell number, "MM/DD/YYYY")`.

Changing the text to the Date and Time data types. Source: Image by author

Values converted to Date and Time format. Source: Image by author

Changing Date format to Text data type. Source: Image by author

#### Percentage

The `Percentage` data type converts numbers into percentages, making it easier to read and interpret ratio data and proportional values.

Here’s how you can convert numbers to percentages:

• Select the range of cells you want to change.
• Right-click on it.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Percentage data type and format your values.

Converting numbers into percentages. Source: Image by author

Numbers converted into percentages. Source: Image by author

#### Fractions

You can use the `Fraction` data type to display your value in fractions instead of decimals.

Let's see how you can convert decimal numbers to fractions.

• Select the range of cells you want to change.
• Right-click on it.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Fraction data type and format your values.

Converting decimal values to Fractions. Source: Image by author

Decimal values converted to Fractions. Source: Image by author

#### Scientific

The `Scientific` data type displays a number in exponential notation, using E+n to represent that the number preceding E is multiplied by 10 to the nth power.

Let's take a look at how to convert the `number` data type to the `scientific` data type:

• Select the range of cells you want to change.
• Right-click on it.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Scientific data type and format your values

Changing to scientific data type. Source: Image by author

Numbers converted to scientific Numbers. Source: Image by author

#### Special

Excel's `Special` data type includes formatting for zip codes, phone numbers, and Social Security numbers (SSNs). These formats keep the leading zeros in the case of zip codes and correctly format phone numbers and SSNs using the appropriate separators.

Let's take a look at how to convert `Values` to `Special` data types:

• Select the cell you want to change.
• Right-click on it.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Special data type and format your values

The dialog box of Special cells. Source: Image by author

Values converted to a special data type. Source: Image by author

#### Custom

As the name suggests, `Custom` data type allows you to customize the formatting according to your needs. It provides various formatting suggestions that can help you customize your values.

Let's look at an example of how you can customize a date.

• Select the cell you want to change.
• Right-click on it.
• Select the Format cell option, and a dialog box will appear.
• Go to the Number tab.
• Select the Custom data type and format your values.
• For this example, we choose d-mmm-yy as our date format.

Selecting the Custom data type. Source: Image by author

### Text data

`Text` data is the basic type that allows you to input characters, including alphabetical, numerical, and special symbols.

Unlike number data, symbols inputted as `Text` do not support calculations. That’s why you can manually manage `Text` and `Number` data according to your needs.

However, by default, Excel interprets figures it does not understand as text data.

To convert `Numbers` to `Text`, here’s what you have to do:

• Highlight the cells or columns with text data you wish to change.
• Right-click on the selected cells.
• Click Format cells.
• Go to the Number tab.
• Select the `Text` data type.

Converting numbers into Text Format. Source: Image by author

Numbers converted to Text. Source: Image by author

Verifying whether the data has been changed or not. Source: Image by author

### Boolean data

Excel's `Boolean` data type represents logical values that perform logical operations. It only has two possible values: `TRUE` and `FALSE`. These values are used in functions and formulas to test conditions and return logical results.

Take a look at how we test whether the two given values are equal or not using the  Boolean data type: `=value1=value2`.

Applying a boolean operator on unequal values. Source: Image by author

Applying a boolean operator on equal values. Source: Image by author

In the rest of this section, we will take a closer look at how Boolen data types are used in logical function. You can also find some logical functions by heading over to Formulas > Logical.

Lists of all the logical operators in Excel. Source: Image by author

Logical functions help users perform complex decision-making processes within their spreadsheets. Logical functions such as `IF()`, `AND()`, `OR()`, and `NOT()` allow users to test conditions and return specific values based on whether the conditions are met.

#### IF() function

`IF()` determines if the value in one cell is greater than the other. If the condition is true, it returns `TRUE`. Otherwise, it returns `FALSE`.

Using the IF operator. Source: Image by author

#### AND() function

`AND()` tests conditions to see if all the conditions are true.

Using AND operator. Source: Image by author

#### OR() function

`OR()` tests values or logical statements—checks if any values meet the arguments.

Using OR operator. Source: Image by author

#### XOR() function

`XOR()` tests if exactly one argument is true or false.

Using XOR operator. Source: Image by author

#### NOT() function

`NOT()` reverses the argument's value. If the argument is valid, then the `NOT()` function will return `FALSE`. Conversely, if the argument is false, then the `NOT()` function will return `TRUE`. This function can also be used to find duplicates in Excel.

Using the NOT operator. Source: Image by author

### Error data

Excel can generate error values whenever it recognizes an error or if data is missing as the entry is being processed. This indicates where things go wrong so you can correct the problem and get the result you are looking for.

All error values begin with a `#`. Here are the common error values that you must look out for:

1. #NAME?: This error occurs when any value is not written within quotes in a formula or when a quote is missing.
2. #DIV/0!: This indicates you have divided the number by zero, but the divisor is not equal to zero.
3. #REF!: This occurs when you have an invalid cell reference or when you delete or paste stuff in place of a formula reference.
4. #NUM!: This occurs when you input an invalid formula or function or when the calculation outcome is too large for Excel to display.
5. #N/A: Excel displays this error for empty or unreadable cells.
6. #VALUE!: This occurs when there is no valid argument or operator in the function.
7. #NULL!: This error condition is raised if you reference the intersection of a range of cells that don't intersect or if a range of cells in a function doesn't have separating commas.

## Verifying Data Types in Excel

In this section, we will review two examples of how to verify data types in Excel. In the first example, we can check if a cell is empty by applying the `ISBLANK()` function.

Checking if the cell is empty. Source: Image by author

Next, we check if a cell contains data using the `ISNUMBER()` function.

Checking if a cell contains a number. Source: Image by author

## What Are Linked Data Types in Excel?

`Linked` data types in Excel connect your worksheet to rich data from the web, which means you can bring in data related to companies, currencies, cities, foods, stocks, and more.

This way, you can manage your currency conversion, stock market analysis, recipe nutritional summaries, and fitness tracking tasks with much less time.

Here are some key features:

1. Rich Data Integration: `Linked` data types bring live connected data directly into your spreadsheet.

2. Automatic Data Updates: Data linked from the web is updated automatically.

3. Data Cards: When you click on a cell containing a `Linked` data type, a data card showing detailed information about that data pops up.

4. Easy Data Extraction: You can extract specific fields from the `Linked` data type into your worksheet.

## Summary table

Let's take everything we know now about data types into a table for easy comparison and reference.

Data Type Description Example How to Verify
Number Values including large numbers, small fractions, and more. 100, 0.25, 1000.50 ISNUMBER()
Currency Monetary values with currency symbols and decimal places. \$100.00, €200.00 Verify by checking currency symbols and decimal placement.
Date and Time Stores dates and times for chronological analysis and calculations. 01/01/2024, 12:00 PM ISDATE() (custom function), check date/time format.
Percentage Converts numbers into percentages for ratio and proportional values. 50%, 75% Verify by checking for percentage symbol and correct decimal placement.
Fraction Displays values as fractions instead of decimals. 1/2, 3/4 Verify by checking if numbers are displayed as fractions.
Scientific Displays numbers in exponential notation using E+n format. 1.23E+04 Verify by checking for exponential notation (E+n).
Special Formats values for zip codes, phone numbers, and Social Security numbers. 12345, (123) 456-7890, 123-45-6789 Verify by checking the special format (e.g., zip code, phone number, SSN).
Custom Allows customized formatting according to user needs. d-mmm-yy, 0.00 "units" Verify by checking the custom format applied.
Text Basic type for characters including alphabetical, numerical, and special symbols. "Hello", "123abc" ISTEXT()
Boolean Represents logical values (TRUE or FALSE) used in functions and formulas. TRUE, FALSE Verify by using logical tests (e.g., =A1=B1).
Error Indicates errors or missing data with specific error values starting with #. #NAME?, #DIV/0!, #REF!, #NUM!, #N/A, #VALUE! Verify by identifying error messages starting with #.
Linked Data Connects worksheet to live data from the web for companies, currencies, cities, foods, stocks, etc. Linked data fields with real-time updates Verify by checking data cards and updates from the web.

## Final Thoughts

Excel data types help users identify, manage, and convert data types as needed to prevent errors. With its robust features, Excel can handle a wide range of data types, including `Number`, `Text`, `Dates`, `Boolean`, and more. The differences between these types are more subtle than they seem, and in order to make the most of Excel, it's a good idea to take our Data Analysis in Excel course. Also, check out our Data Preparation in Excel and Data Visualization in Excel courses to really accelerate your journey.

Author
Laiba Siddiqui

I'm a content strategist who loves simplifying complex topics. I’ve helped companies like Splunk, Hackernoon, and Tiiny Host create engaging and informative content for their audiences.

### How can I prevent Excel from misinterpreting a numerical entry as text?.css-18x2vi3{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:rotate(0.5turn) translate(21%, -10%);-moz-transform:rotate(0.5turn) translate(21%, -10%);-ms-transform:rotate(0.5turn) translate(21%, -10%);transform:rotate(0.5turn) translate(21%, -10%);-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

Before entering the data, ensure the cell format is set to 'Number' or another appropriate numeric format.

### How can I automate the identification of data types in Excel using VBA?.css-167dpqb{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:none;-moz-transform:none;-ms-transform:none;transform:none;-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

Use VBA functions like IsNumeric, IsText, and IsDate to check and validate data types within your VBA code.

### How do I handle missing data in Excel to avoid calculation errors?

Use error-handling functions like `IFERROR()`, which allows you to specify a value or action if an error is detected, e.g., `=IFERROR(A1/B1, "Error")`.

### What is the difference between Number and Text data types in Excel?

Number data can be used for calculations, whereas Text data includes characters and symbols that do not support calculations.

Topics

Learn with DataCamp

Course

### .css-1531qan{-webkit-text-decoration:none;text-decoration:none;color:inherit;}Data Analysis in Excel

3 hr
42.1K
Learn how to analyze data with PivotTables and intermediate logical functions before moving on to tools such as what-if analysis and forecasting.
See Details
Start Course

Course

### Introduction to Excel

4 hr
55.1K
Master the Excel basics and learn to use this spreadsheet tool to conduct impactful analysis.

Course

### Data Preparation in Excel

3 hr
23.8K
Understand how to prepare Excel data through logical functions, nested formulas, lookup functions, and PivotTables.
See More
Related

tutorial

### How to Clean Data in Excel: A Beginner's Guide

Learn essential data cleaning techniques in Excel, including removing duplicates, handling missing values, and maintaining consistent formatting.

Laiba Siddiqui

15 min

tutorial

### Conditional Formatting in Excel: A Beginner’s Guide

Explore conditional formatting in Excel with simple to advanced examples and their best practices.

Joleen Bothma

7 min

tutorial

### How to Create Data Models in Excel: A Comprehensive Guide

We create data models by formatting data, creating relationships, using Power Query, and leveraging Power Pivot for seamless data integration and analysis.

Vikash Singh

13 min

tutorial

This tutorial will give you a basic understanding of the terminology in spreadsheets along with learning how to create a basic table.

Ryan Sheehy

5 min

tutorial

Jess Ahmet

12 min

tutorial

### Correlation Matrix In Excel: A Complete Guide to Creating and Interpreting

Learn the statistical concept of correlation, and follow along in calculating and interpreting correlations for a sample dataset, in a step-by-step tutorial.

Arunn Thevapalan

9 min

See MoreSee More