Various types of information can be entered into cells: numbers, symbols, time, date, logical constants (true or lie), formulas that calculate values. To change the external presentation of data in table cells, formatting is carried out - the Home toolbar (Fig. 12.7).
Rice. 12.7.
The Number tab allows you to specify the type of number format and determine the number of decimal places (Fig. 12.8).
The Alignment tab allows you to set the order of aligning the contents of cells horizontally and vertically, text orientation (rotation from +90 to -90°), breaking text into lines and word wrapping, as well as merging cells and automatically selecting column width (Fig. 12.9).
The Font tab allows you to select the font type and size, font style and additional text effects; Border is a choice
Rice. 12.8.
Rice. 12.9.
color and type of line, method of framing cells; View – select the color and pattern of cell fill; Protection – set cell protection, hide formulas.
Formulas and functions
Entering a formula always begins with an equal sign (=) or a plus sign (+)
For example: = 46 + 55; = 200 * B5; = A7/B4.
Notes
- 1. In the cell containing the formula (by default), only the calculation result is visible. The formula itself can be seen in the formula bar when this cell becomes active.
- 2. Excel evaluates the formula every time the table content that references the formula changes.
- 3. If the result of a calculation does not fit in a table cell, Excel can display the character sequence "#######" indicating that the column width needs to be increased.
- 4. When entering decimal numbers, use a comma (,) as the decimal separator.
Excel functions– these are predetermined formulas that allow you to make calculations in financial, statistical, mathematical, logical and other areas of activity.
Functions are specified using mathematical and other formulas, in accordance with which calculations are performed on given quantities, called arguments, and in the order specified by the syntax. The syntax of built-in functions is quite simple:
Function name (<аргумент1; аргумент2; аргумент3 и т.д.)
Function name shows what kind of calculations we are talking about. Examples of function names are SUM, AVERAGE, etc.
Arguments– the values that the function uses when calculating the result.
They are listed in parentheses following the function name. Arguments can be numeric values, text, Boolean values, arrays, error or reference values, date/time, and other functions and formulas. Excel distinguishes between required (which must always be specified) and optional arguments. Individual arguments are separated by a semicolon (;).
Result– the value obtained when calculating the function.
Many mathematical operations in Excel are performed using built-in functions.
Functions can be selected from a list of functions using the Insert/Function menu or by clicking the fx icon on the standard toolbar with the corresponding set of required functions.
After selecting the required function, the Function Wizard is activated, allowing the user to fill in its arguments step by step.
Functions in Excel are divided into the following main groups: mathematical, arithmetic and trigonometric; for working with dates and times; financial; logical; for working with links and arrays; for working with the database; statistical; text, etc.
In our subjective opinion, this is not true, because... A formula is not a data type, but a type of information that tells a program to perform certain commands. In addition, the formula itself can return calculation results in different data types. Also, the date, being in fact a number, cannot take negative values, and therefore cannot be classified as a number.
We distinguish 4 main groups:
- Text;
- Numerical;
- Date and time;
- General.
Moreover, a group can include several types. To see them in an Excel workbook, go to the “Home” tab, “Number” section and click the drop-down list. This allows you to quickly set the data type for a cell or range. To set it more specifically, i.e. different separators and display options, click on the arrow in the lower right corner of the same section, or right-click on the cell and select “Format Cells...”.
Let's give a brief description of the groups (for a more detailed description, follow the links):
- Text type (Text data type in Excel) – represents information as text (string). If a number is written into a cell with this type, then the application considers it to be a string. Although you can perform calculations on it, you may not be able to use it as a number in some functions.
- Numeric type (Numeric data type in Excel) – defines information as a number, as a result of which any calculations can be carried out with it and mathematical functions can be applied. The Excel application also offers various types of displaying this kind of information.
- Date and Time Format (Date and Time Data Types in Excel) – Represents information as either a date, a time, or a date and time. At its core, the date and time type is just a convenient way to represent numerical information and therefore allows you to perform mathematical operations with it. This type cannot be negative.
- General data format – used when the format is not set. The program itself will determine which format to assign certain data to.
Excel is not strict about data types and often eliminates possible errors. So, for example, if you add the number 5 in text format with 5 in numeric or general format, an error will not occur due to the program's own data type conversion. Also, if the string is converted to numeric format, no error will be generated. But in some cases the format makes a huge difference. If applied incorrectly, data type errors may occur (
Excel uses two main data types:
Ø text, that is, a sequence of characters (when entered, they are automatically aligned to the left edge of the cell);
Ø numbers, distinguishing them as numeric constants, formulas, built-in functions, or dates. When you enter numbers, Excel automatically aligns them to the right edge of the cell and performs the calculations the user needs on them.
Text data is typically used to indicate table names, column headings, text information in rows and columns, and comments.
Numeric data types are used for numeric values (or references to corresponding cells) and the arithmetic operations that link them.
For example, the following expressions are numeric data types in Excel:
When entering a formula, you always begin with an equal sign (=) or a plus sign (+).
Notes:
§ The formula contained in a cell can be seen by default in the formula bar when the cell becomes active, and only the result of the calculation is visible in the cell itself. To display formulas in cells, you must click the button Office, in the window that opens, click the button Excel Options, in the window Excel Options select option Additionally, in the group Show options for next sheet enable option Show formulas, not their values.
§ Excel evaluates a formula every time the table content that references the formula changes.
§ If the result of a calculation does not fit in a table cell, Excel can display the character sequence "#######" indicating that the column width needs to be increased.
§ When entering decimal numbers, use a comma (,) as the decimal separator.
Excel functions– these are predetermined formulas that allow you to make calculations in financial, statistical, mathematical, logical and other areas of activity. Functions are divided into built-in And user defined. Built-in functions are grouped into categories and have the same syntax: the function name and its arguments.
Functions are specified using mathematical and other formulas that perform calculations on specified values, called arguments, and in a specified order, determined by the syntax. The syntax of built-in functions is quite simple:
Function name(<аргумент1; аргумент2; аргумент3 и т.д.) , Where:
Function name shows what kind of calculations we are talking about. Examples of function names are SUM, AVERAGE, etc.
Arguments– the values that the function uses when calculating the result. The arguments are listed in parentheses following the function name. Arguments can be numeric values, text, Boolean values, arrays, error or reference values, date/time, and other functions and formulas. Excel distinguishes between required (which must always be specified) and optional arguments. Individual arguments are separated by a semicolon (;).
Result– the value obtained when calculating the function.
Many mathematical operations in Excel are performed using built-in functions.
Functions can be selected from the list of functions using the button Insert function from the group Function Library on the tab Formulas, or by clicking the icon f x in the formula bar.
After selecting the required function, it connects Function Wizard, allowing the user to fill in its arguments step by step.
Functions in Excel are divided into the following main groups:
· Mathematical, arithmetic and trigonometric functions.
· Functions for working with dates and times.
· Financial functions.
· Logical functions.
· Functions for working with databases.
· Statistical functions.
· Text functions, etc.
Mathematical and trigonometric functions are used to perform computational operations (subtraction, addition, multiplication, division), as well as exponentiation, rounding, logarithmic calculations, random number operations, and counting. The group of trigonometric functions combines direct and inverse trigonometric functions.
Date and time functions let you analyze and work with date and time values in formulas. Date and time values are stored and processed by the program as numbers. The starting date is January 1, 1900. It corresponds to the integer value 1. Each subsequent day is represented by an integer value that is one greater than the value of the previous date.
Financial functions solve mainly the problems of calculating depreciation and determining interest on loans and investments, and also analyze transactions with securities. Using these functions, you can determine indicators such as the current value of investments, the yield of securities and others.
Logical functions operate on logical values and their result is also a logical value - TRUE or FALSE.
Functions for working with databases are used to work with databases (lists) and tables with the corresponding data structure. Using these functions, you can perform analysis on worksheet data.
Statistical functions allow you to solve many different problems, both complex professional ones and simple ones, for example, determining the arithmetic mean.
Text functions are used when working with text, allowing you to search, replace or combine specific sequences of characters, as well as count the number of characters and much more.
Excel automatically groups the ten most recently used functions into the "10 Recently Used" category.
End of work -
This topic belongs to the section:
Microsoft Excel Basics
Entering and processing data in Excel. A significant part of the work in Excel involves entering data and editing it. A worksheet in Excel consists of columns and rows..
If you need additional material on this topic, or you did not find what you were looking for, we recommend using the search in our database of works:
What will we do with the received material:
If this material was useful to you, you can save it to your page on social networks:
Many Excel users do not see the difference between the concepts of “cell format” and “data type”. In fact, these are far from identical concepts, although they are certainly related. Let's find out what the essence of data types is, what categories they are divided into, and how you can work with them.
A data type is a characteristic of the information stored on a worksheet. Based on this characteristic, the program determines how to process a particular value.
Data types are divided into two large groups: constants and formulas. The difference between them is that formulas output a value to a cell, which can change depending on how the arguments in other cells change. Constants are constant values that do not change.
In turn, the constants are divided into five groups:
- Text;
- Numeric data;
- Date and time;
- Logical data;
- Wrong values.
Let's find out what each of these data types represents in more detail.
Text values
The text type contains character data and is not treated by Excel as a mathematical calculation object. This information is primarily for the user, not for the program. The text can be any character, including numbers, as long as they are formatted appropriately. In the DAX language, this type of data refers to string values. The maximum text length is 268435456 characters in one cell.
To enter a symbolic expression, you need to select the text or general format cell in which it will be stored and type the text from the keyboard. If the length of a text expression exceeds the visual boundaries of a cell, then it is superimposed on top of its neighbors, although it physically continues to be stored in the original cell.
Numeric data
Numerical data is used for direct calculations. It is with them that Excel performs various mathematical operations (addition, subtraction, multiplication, division, exponentiation, root extraction, etc.). This data type is intended exclusively for writing numbers, but can also contain auxiliary characters (%, $, etc.). Several types of formats can be used in relation to it:
- Actually numerical;
- Percentage;
- Monetary;
- Financial;
- Fractional;
- Exponential.
In addition, Excel has the ability to split numbers into digits and determine the number of digits after the decimal point (in fractions).
Entering numeric data is done in the same way as text values, which we discussed above.
Date and time
Another data type is the time and date format. This is exactly the case when data types and formats are the same. It is characterized by the fact that it can be used to indicate on a sheet and carry out calculations with dates and times. It is noteworthy that when calculating this type of data, the day is taken as a unit. Moreover, this applies not only to dates, but also to time. For example, 12:30 is considered by the program as 0.52083 days, and only then is displayed in the cell in a form familiar to the user.
There are several types of formatting for time:
- h:mm:ss;
- h:mm;
- h:mm:ss AM/PM;
- h:mm AM/PM etc.
The situation is similar with dates:
- DD.MM.YYYY;
- DD.MMM
- MMM.GG, etc.
There are also combined date and time formats, for example DD:MM:YYYY h:mm.
You also need to take into account that the program displays only values starting from 01/01/1900 as dates.
Logical data
The logical data type is quite interesting. It operates with only two values: "TRUE" And "LIE". If exaggerated, this means “the event has arrived” and “the event has not arrived.” Functions, processing the contents of cells that contain logical data, perform certain calculations.
Erroneous values
Error values are a separate type of data. In most cases, they appear when an incorrect operation is performed. For example, such incorrect operations include division by zero or introducing a function without observing its syntax. Among the erroneous values are the following:
- #VALUE! – using the wrong type of argument for a function;
- #CASE! – division by 0;
- #NUMBER! – incorrect numerical data;
- #N/A – an unavailable value was entered;
- #NAME? – incorrect name in the formula;
- #EMPTY! – incorrect entry of range addresses;
- #LINK! – Occurs when cells that were previously referenced by a formula are deleted.
Formulas
A separate large group of data types are formulas. Unlike constants, they are most often not visible in the cells themselves, but only display the result, which can change depending on changes in the arguments. In particular, formulas are used for various mathematical calculations. The formula itself can be seen in the formula bar by highlighting the cell in which it is contained.
A prerequisite for the program to perceive an expression as a formula is the presence of an equal sign in front of it (=) .
Functions are a separate type of formulas. These are unique subroutines that contain a set set of arguments and process them according to a specific algorithm. Functions can be entered manually into a cell by first placing the sign in it «=» , or you can use a special graphical shell for these purposes Function Wizard, which contains the entire list of operators available in the program, divided into categories.
By using Function Wizards You can navigate to the argument window for a specific operator. Its fields contain data or links to cells that contain this data. After pressing the button "OK" the specified operation is performed.
As you can see, in Excel there are two main groups of data types: constants and formulas. They, in turn, are divided into many other types. Each data type has its own properties, taking into account which the program processes them. Mastering the ability to recognize and correctly work with various types of data is the primary task of any user who wants to learn how to effectively use Excel for its intended purpose.