Data Types In Microsoft Excel

Table of Contents[Show]
Data Types In Microsoft Excel


Excel Data Types - This time we will learn about the types of data in Excel that we can enter into the cells of the Microsoft Excel worksheet.

Understanding the types of data in Excel is very important so that we can plan, compile and process data appropriately. Not infrequently I encounter several cases of failure of Excel users in compiling an Excel formula due to not understanding the type of data in Microsoft Excel correctly.

Although many explain that data types in Excel, there are four types of data. However, I prefer to divide data types in Excel with three types of data that we can input into a worksheet. Namely the data type Labels, Values, and Formulas.

When referring to 4 types of data, it is usually mentioned that the intended data types are Text Data (Label), Value Data, Time Data and Date.

Why am I more likely to divide into 3 types? Because in my opinion, date and time data in Excel are basically numeric / value data.

For more details about the kinds of data in Excel, let's discuss them one by one.

Which includes data types in Excel are:

Label / Text Data Type

What is the meaning of label data or text data?

This type of label data is often referred to as Character, String or Text data.

Type Labels in Excel are data types that are composed of character sets of letters (AZ / az), numbers (0-9) or symbol characters (! , @, #, Etc.).

The main characteristic of the type of label data or text data in Microsoft Office Excel, which cannot be operated by mathematical operators in Excel. So, it cannot be added, subtracted, multiplied or divided.

Label / Text Data Type

Label data is usually used as part of the description of an information. For example, it is used to show a name, the header of a table, or other descriptions.

We cannot use label data or text in arithmetic calculation operations such as addition, subtraction, multiplication or division.

Another feature of the label data type is that by default, if we enter it into a cell, this type of data will be displayed as aligned left.

To find out whether a cell contains text data type or not, we can test it with the IS TEXT function provided by Excel.

Value Data Type

What is the meaning of data value?

Value data type is also known as numeric data, namely data in excel cells whose main constituent characters are the numeric values ​​0-9.

The main characteristic of the value data type is that it can be used in arithmetic calculation operations such as addition, subtraction, multiplication or division.

Another feature of numeric data in Excel is that by default it will be displayed Align Right in an excel cell.

You can use the ISNUMBER function to check whether the contents of a text are numeric or not.

The types of value data in Excel can be further divided into several types, including:

  • Integer
  • Decimal number (Decimal)
  • Fraction number (Fraction)
  • Scientific Notation data type (Scientific Notation)
  • Percent data type (Percent)
  • Currency data types (Currency)
  • Logical values ​​TRUE and FALSE (Logical)
  • Date and time data type (Date and Time).
types of value data in Excel can be further divided into several type

In some cases Excel cells that appear as numbers may actually be label data or text data.

Integers

When entering the number 0-9 into Excel cells, Excel will automatically recognize the input data as a data value or number format.

Example: 250, 3456, 1000, etc.

Decimal Numbers

In simple terms, decimal numbers in Microsoft Excel are numeric numbers that contain decimal separator characters. 

Example: 0.1; 2.50; 1500, 025; 100.00; etc.

Fraction Numbers

Fractions are numbers that have a numerator and denominator. In appearance, the writing between the numerator and denominator is separated by a slash character.

Example: 1/2, 2/3, 1 3/5, etc.

To input a fraction, you can type it into the cell. When the number you type does not have an integer value, for example 1/2, then add the number 0 in front of it, for example, type the number 0 1/2, otherwise Excel will assume you are inputting date.

Scientific Notation Numbers

This type of data is displayed following the scientific notation rules Wikipedia has described as " A way of writing numbers that accommodates values ​​too large or small to easily be written in standard decimal notation ".

Example: 1, E + 03 (1000)

Percent Numbers

The percent number in Microsoft Excel is indicated by the percent symbol (%) on the displayed number.

Example: 100%, 20%, 35%, etc.

Currency Numbers

Currency numbers are indicated by a currency symbol or thousands separator. 

Example: INR 1,000,000.00

Date and time type

If you enter data that is exactly or similar to the date and / or time format into Excel worksheet cells, then Microsoft Excel will automatically convert the input data into date and / or time type value data.

Example: 01/01/2015, 1 January 2015, 12:00, 14:30:00, 01/01/2015 12:00, etc.

In Microsoft Excel, the date format is stored in the form of sequential serial numbers where the number 1 represents January 1, 1900, number 2 is January 2, 1900 and so on.

While the time format is a fraction or decimal number where the value of 1 hour = 1/24, 1 minute = 1 / (24 * 60) and 1 second = 1 / (24 * 60 * 60).

Therefore, don't be surprised if in Excel date format we can perform arithmetic operations, for example adding, or subtracting certain numbers from this data type.

Logical Data Types

This type of data is often referred to as Boolean data. If you type TRUE or false text in a cell then Excel will treat the text as numeric types where TRUE and FALSE worth the number 1 is worth the number 0.

By default this type is shown (centred).

Except for the Logic type, Excel actually stores data values ​​as whole numbers or decimals, so basically Excel only formats these numbers into certain value formats.

Especially for the TRUE or FALSE logic types, Excel still saves the data as text, but you can use this text in certain arithmetic operations.

In addition to inputting data in certain formats, we can convert this value data type from one format to another value format. 

Can we not change the value data type into a text type? Of course, I can.

If you want to input data in the form of numbers and want Excel to think of it as text data then add quotation marks (') before the number. Or by formatting the cell or range that you want to specify as the text format first, then typing the numbers you want to write as the label / text data.

If you have already entered the data value in the form of a number in a certain number format. You can convert this type of value by using the TEXT function in Microsoft Excel.

Formula Data Type

Apart from the two data types above, there is one other data type in Microsoft Excel. Namely the data type formula or excel formula.

This formula data type can be recognized by the presence of an equal character (=) at the beginning of the arrangement.

You can learn a little explanation about this excel formula in the following section: Introduction to Formulas and Functions in Microsoft Excel

A formula in the end also produces 2 previous types of data, namely data type label or type value.

This data type is usually used to give certain commands to Excel or perform certain calculations according to the function and formula structure.

Leave a Comment