Contents
Any person who has heard at least a little about programming knows the concept of “data type”. This term is found both in specialized literature and popular and applies to all programming languages without exception. Working with Excel spreadsheets also has a lot in common with programming: there are functions, there are formulas, and there are data types.
Very often people confuse two key Excel terms: cell format and data type. They are indeed similar, but there is still a certain difference between them. Today we will try to figure out what a data type is and how it affects working with spreadsheets.
What is meant by the term “data type”
A data type is a characteristic that allows a spreadsheet to define information and understand how to process it.
Kinds of Data Types Compatible with Excel
In general, Excel distinguishes two large groups into which data types can be divided. The first one is constants. These are values that never change, but can be used in calculations. Another group of data is also distinguished – formulas. This is analogous to variables in programming languages, but has a wider application.
The values of the formulas may change depending on the contents of those cells that are used there. This is their key difference from variables. Cells are variables in Excel. These data types are considered immutable in Excel:
- Numbers.
- The text.
- Time and date.
- Boolean data.
- Errors.
Let’s take a closer look at each of these data types.
formula
We will consider this option first. The fact is that formulas are fundamentally different from all other data types. As we understood above, formulas change depending on what values are passed to them. With the help of formulas, you can perform a variety of operations, not only mathematical, but also logical. They can be used in a variety of areas: mathematics, banking, and so on. The result of formulas is displayed in the same cell in which it is written. If you need to see the formula itself, then it is displayed in a special line, which is called the formula line. 
It is necessary before you write the formula, put the sign “Equals”. This is done so that the program can recognize the expression as a command to perform certain calculations. You can use both relative and absolute values. The difference between them is that the relative ones change depending on the content of other cells, while the absolute ones remain constant. 
A function is a standardized form of a formula that makes calculations easier. It contains its own algorithm, according to which the calculations take place. Therefore, the user does not need to independently enter the necessary parameters and enter some mathematical operations several times.
The formula is inserted directly after the equal sign. In addition, you can use a special wizard that can be called up by pressing the fx key directly to the left of the formula bar, or using the menu. Next, a list of functions appears, you need to select the correct one and confirm your actions with the OK button. 
Next, fill in the window with arguments and confirm your actions by pressing the “OK” button. 
After that, the formula is entered. The formula itself can contain many different types of data combined in different combinations. For example, a combination of logical functions and mathematical functions allows you to perform calculations only if a certain condition is met. Excel is a flexible program that can realize almost any user desire related to its functionality.
Boolean data
There are two main types of boolean values: TRUE and FALSE. The first is returned if the expression meets a certain condition. Second, if not. But it can be used in other situations as well.
Errors
Although many users consider errors not a data type, they still contain information that can be classified. The errors look like this.
Depending on the error code, the user can determine the cause of the problem. As a rule, this is when the formula is entered incorrectly or some problems appear. Let’s look at some of the most common errors and what they mean. 
Date and Time
Date and time in Excel is a functional data type that can be converted to a number format, which makes it possible to perform additional data. For example, you can sort by date or find items that arrived at the warehouse before a certain time. This is the case when you can draw an equal sign between the data type and the cell format. Working with dates and times is carried out in the same way as with ordinary numbers, and Excel will automatically convert them to the correct format.
How are dates and times converted to numbers? Excel defines a day as the number 1. Accordingly, all values less than a day are part of the number 1. For example, the time 8:30 will be translated by the program into the number 0,35. This is easy to check by assigning a number format to the date cell. In turn, in the formula bar and in the cell, the number, provided that the cell itself is in the time format, will be displayed correctly. And here we begin to encounter the difference between the concepts of cell format and data type. Because the same format: date and time can be represented in different ways:
- Hours, minutes, seconds.
- Hours, minutes.
- Hours, minutes (day/night).
- Hours, minutes, seconds (daytime, nighttime).
There are a huge number of representations that you can choose from in the Format Cells dialog box on the Number tab. Similar information can be said about the date. On the “Number” tab, you can select the “Date” number format and then select the display type.
There is one more option. The user can choose to display the date and time at the same time. 
Attention! Excel can only display dates starting from 1.01.1990/1/2019. It is important to remember that the date itself is displayed in a form familiar to humans, but the computer itself processes it as a numeric value. Therefore, if we enter January 43466, XNUMX, then the computer will read this value as XNUMX.
In the same way as in the time example, if we change the cell format to numeric, we will see a number that corresponds to the date that was entered in the cell.
Text
And here we see an example of a format that very often diverges from the data type. After all, a number can be written into a cell, and as a result, the program will define it as text. Accordingly, such a number will be defined differently by formulas and read like a string.
The task of a text format is to carry certain information. It can contain both numbers and text. In addition, you can use any characters. Information is entered in the same way as in a numeric cell. An interesting characteristic of any text strings is that they do not fit inside the cell if they are too long and the default settings are set. They will overlap the contents of other cells if their value is too large, as shown in this screenshot.
Numbers
This is the most common format. It is used in absolutely all calculations in various fields of activity. But this data type is not limited to just one numeric data format, because there are many ways to display data in Excel, which should be discussed separately.
- General. This is the most standard format and is used by default. As a rule, the appearance of numbers in this case corresponds to how they are entered. In this case, if the cell is too small for the number, the values contained in it are rounded off. If there are more than 12 digits in the number, then the format is automatically changed to exponential.
- Numerical. This is the most standard format used in mathematical and arithmetic operations. For this format, you can adjust the number of decimal characters, how negative numbers are displayed, and a number of other options.
- Monetary. The specificity of this display method is that in addition to the number, the cell also indicates the sign of the currency. In all other respects, this format is similar in settings to the standard numeric.
- Financial. The difference between this and the previous one is that the designations of monetary units and decimal separators in the column are aligned.
- Date and time. These are also number formats for the reasons described above. Therefore, the user can select them in the “Number” tab.
- Percentage. If a cell is formatted as a percentage, the value it contains is automatically incremented by a factor of 100, and a percent sign is added to the displayed result. Of the available settings – the number of characters in the fractional part.
- Fractional. A number in this format is displayed as a fraction.
- Exponential. A display method that was actively used earlier in order to reduce the number of digits. It is formed as follows: a part of the number is replaced by E + n. The first letter shows us that the number is in exponential format. This means that the number that was before it is multiplied by 10 to the power of n. For example, if you represent the number 12345678901 in exponential format, you get 1,23E+10. To decipher this code, you need to multiply 1,23 by 10 to the power of 10. For this number, you can adjust the number of decimal places.
- Text. The number is displayed as text. In this case, the display form is fully consistent with the one that was at the input.
- Additional. There are several other ways to present. If you select the Optional format, the number will be displayed as a phone number, zip code, or insurance number.
- All formats. This is a setting that allows you to customize how the number is displayed. It is possible to specify up to 250 custom number formats.
What to do in order to set the cell format? This can be done in two ways.
- Go to the “Home” tab and find the “Number” group there. There you need to find a button with an arrow next to the format selection field.
- You can also configure the way the cell is displayed through the context menu. To call it, you need to right-click on the cell that you want to change and click on the “Format Cells” button.
- The user can select the number of decimal places to display after the decimal point. This applies to absolutely all formats, with the exception of only fractional. If the standard number format is used, then it is also possible to add a separator between the digits so that the numbers look like this: 555 444 333.
- Entering values into a cell is done as follows. First, the cell must be selected, and then characters must be typed. You can also copy and paste the required element. There is another option – entering the required values in the formula bar.
- The user can do another way: do not change the format from the very beginning, but first enter the required value. It can also be handy in some cases.
Conclusion
Summarizing all of the above, then in Excel there are the following main categories of data types: formulas and constants. The latter are a complex category that includes many private data types, each of which can be used to achieve very specific purposes.
To make data processing in spreadsheets more efficient, you need to perform several tasks. First of all, to correctly configure the parameters of the cells and the way they are displayed. Care must be taken to ensure that the correct data type is used. It also requires the user to be able to combine different types of data in order to achieve maximum results. Good luck to you in this field.