Performing calculations using formulas in Excel. Calculations in Excel. The order of performing calculations in Excel

Over the past decade, the computer has become an indispensable tool in accounting. Moreover, its application is diverse. First of all, of course, this is the use of an accounting program. To date, quite a lot of software tools have been developed, both specialized ("1C", "Info-Accountant", "BEST", etc.), and universal, like Microsoft Office. At work, and even in everyday life, you often have to do a lot of different calculations, maintain multi-line tables with numerical and text information, doing all kinds of calculations with the data, printing options. To solve a number of economic and financial problems, it is advisable to use the numerous features of spreadsheets. Consider in this connection the computational functions of MS Excel.

Source: Journal "Accountant and Computer" No. 4 2004.

Like any other spreadsheet, MS Excel is primarily designed to automate calculations, which are usually done on a piece of paper or using a calculator. In practice, in professional activity, there are quite complex calculations. That's why we'll talk in more detail about how Excel helps us automate their execution.

Formulas use operators to indicate an action, such as addition, subtraction, and so on.

All operators are divided into several groups (see table).

Arithmetic operators are used to represent basic mathematical operations on numbers. The result of performing an arithmetic operation is always a number. Comparison operators are used to refer to the comparison of two numbers. The result of the comparison operation is the logical value TRUE or FALSE.

Excel uses formulas to perform calculations. Using formulas, you can, for example, add, multiply and compare table data, that is, you should use formulas when you need to enter a calculated value into a sheet cell (automatically calculate). Formula entry begins with the “\u003d” symbol (equal sign). It is this sign that distinguishes the input of formulas from the input of text or simple numerical values.

When entering formulas, you can use normal numeric and text values. Recall that numeric values \u200b\u200bcan only contain numbers from 0 to 9 and special characters: (plus, minus, forward slash, parentheses, period, comma, percent and dollar signs). The text values \u200b\u200bcan contain any characters. It should be noted that text expressions used in formulas must be enclosed in double quotation marks, for example “constant1”. In addition, in formulas, you can use cell references (including in the form of names) and numerous functions that are interconnected by operators.

References are cell addresses or ranges of cells that are included in a formula. Cell references are specified in the usual way, that is, in the form A1, B1, C1. For example, in order to get the sum of cells A1 and A2 in cell A3, it is enough to enter the formula \u003d A1 + A2 (Fig. 1).

When entering a formula, cell references can be typed directly from the Latin keyboard character by character, but more often it is much easier to specify them with the mouse. For example, to enter the formula \u003d A1 + B2, you need to do the following:

Select the cell where you want to enter the formula;

Start entering the formula by pressing the “\u003d” (equal) key;

Click on cell A1;

Enter the “+” symbol;

Click on cell B2;

Finish entering the formula by pressing the Enter key.

A range of cells is a certain rectangular area of \u200b\u200ba worksheet and is uniquely determined by the addresses of cells located in opposite corners of the range. Separated by “:” (colon), these two coordinates make up the address of the range. For example, to get the sum of the cell values \u200b\u200bin the range C3: D7, use the formula \u003d SUM (C3: D7).

In the special case, when the range consists entirely of several columns, for example, from B to D, its address is written in the form B: D. Likewise, if the range consists entirely of lines 6 through 15, then it has an address of 6:15. In addition, when writing formulas, you can use the concatenation of several ranges or cells, separating them with a ";" (semicolon), for example C3: D7; E5; F3: G7.

Editing an already entered formula can be done in several ways:

Double-click the left mouse button on a cell to correct the formula directly in that cell;

Select a cell and press the F2 key (Fig. 2);

Select a cell by moving the cursor to the formula bar, left-click.

As a result, the program will go into editing mode, during which you can make the necessary changes to the formula.

When filling out a table, it is customary to set calculation formulas only for the “first” (initial) row or “first” (initial) column, and fill the rest of the table with formulas using copy or fill modes. An excellent result is obtained by using autocopying formulas using auto-filler.

Let us recall how to correctly implement the copy mode. There can be various options (and problems too).

It should be borne in mind that when copying, the addresses are transposed. When you copy a formula from one cell to another, Excel reacts differently to formulas with relative and absolute references. For relative Excel, by default, it transposes addresses, depending on the position of the cell into which the formula is copied.

For example, you need to add row by row the values \u200b\u200bof columns A and B (Fig. 8) and place the result in column C. If you copy the formula \u003d A2 + B2 from cell C2 to cell C3 * (and further down through C), then Excel will convert addresses of the formula, respectively, as \u003d A3 + B3 (and so on). But if you need to put a formula, say, from C2 into cell D4, then the formula will already look like \u003d B4 + C4 (instead of the required one \u003d A4 + B4), and, accordingly, the result of the calculations will be incorrect! In other words, pay close attention to the copying process and manually adjust the formulas if necessary. By the way, copying itself from C2 to C3 is done as follows:

1) select cell C2 from which you want to copy the formula;

2) press the "Copy" button on the toolbar, or the Ctrl + C keys, or select "Edit ® Copy" from the menu;

3) select cell C3, into which we will copy the formula;

4) press the "Paste" button on the toolbar, or the Ctrl + V keys, or through the "Edit ® Paste" menu with pressing Enter.

Let's consider the autocomplete mode. If you need to move (copy) a formula into several cells (for example, in C3: C5) down the column, then it is more convenient and easier to do this: repeat the previous sequence of actions to point 3 of cell selection C3, then move the mouse cursor to the starting cell of the range ( С3), press the left mouse button and, without releasing it, drag down to the required last cell of the range. In our case, this is cell C5. Then release the left mouse button, move the cursor to the "Insert" button on the toolbar and press it, and then Enter. Excel itself converts the addresses of the formulas in the range we have selected to the corresponding line addresses.

Sometimes it becomes necessary to copy only the numeric value of a cell (range of cells). To do this, you need to do the following:

1) select a cell (range) from which you want to copy data;

2) click the "Copy" button on the toolbar or select "Edit ® Copy" from the menu;

3) select the cell (upper left of the new range), into which the data will be copied;

4) select “Edit ® Paste Special” in the menu and press Enter.

When copying formulas, the computer immediately performs calculations on them, thus giving a quick and visual result.

:: Functions in Excel

Functions in Excel make it easy to do calculations and interact with spreadsheets. The most commonly used function is the sum of cell values. Recall that it is called SUM, and the ranges of summed numbers are used as arguments.

In a table, you often want to calculate the total for a column or row. For this, Excel offers an automatic sum function, performed by clicking the button (“AutoSum”) on the toolbar.

If we enter a series of numbers, place the cursor under them and double-click on the auto-sum icon, then the numbers will be added (Fig. 3).

In the latest version of the program, to the right of the auto-summation icon, there is a list button that allows you to perform a number of frequently used operations instead of summing (Fig. 4).

:: Automatic calculations

Some calculations can be performed without entering formulas at all. Let's make a small lyrical digression, which may be useful for many users. As you know, a spreadsheet, thanks to its user-friendly interface and computing capabilities, can completely replace calculations using a calculator. However, practice shows that a significant part of people who actively use Excel in their activities keep a calculator on their desktop to perform intermediate calculations.

Indeed, in order to perform an operation of summing two or more cells in Excel to obtain a temporary result, it is necessary to perform at least two extra operations - find a place in the current table where the total amount will be located, and activate the auto-sum operation. And only after that you can select those cells, the values \u200b\u200bof which need to be summed up.

That is why, starting with version Excel 7.0, the auto-calculation function has been built into the spreadsheet. Excel spreadsheets now have the ability to quickly perform some mathematical operations in automatic mode.

To see the result of the intermediate summation, simply select the required cells. This result is also reflected in the status bar at the bottom of the screen. If the amount does not appear there, move the cursor to the status bar at the bottom of the frame, right-click and in the drop-down menu next to the Amount row, press the left mouse button. Moreover, in this menu on the status bar, you can select various options for the calculated results: sum, arithmetic mean, number of elements, or the minimum value in the selected range.

For example, we will use this function to calculate the sum of values \u200b\u200bfor the range B3: B9. Highlight the numbers in the range of cells B3: B9. Please note that in the status bar located at the bottom of the working window, there is an inscription Sum \u003d X, where X is a number equal to the sum of the selected range numbers (Fig. 5).

As you can see, the results of the usual calculation by the formula in cell B10 and the auto calculation are the same.

:: Function Wizard

In addition to the summation function, Excel allows you to process data using other functions. Any of them can be entered directly in the formula bar using the keyboard, however, to simplify the input and reduce the number of errors in Excel there is a "Function Wizard" (Fig. 6).

You can call the "Wizards" dialog box using the "Insert ® Function" command, the Shift + F3 key combination or the button on the standard toolbar.

The first dialogue of the “Function Wizards” is organized by topic. Having selected a category, in the lower window we will see a list of the names of the functions contained in this group. For example, you can find the SUM () function in the "Mathematical" group, and in the "Date and time" group there are the NUMBER (), MONTH (), YEAR (), TODAY () functions.

In addition, to speed up the selection of functions, Excel “remembers” the names of 10 recently used functions in the corresponding group. Note that at the bottom of the window there is a quick reference on the purpose of the function and its arguments. If you click the Help button at the bottom of the dialog box, Excel will open the corresponding Help topic.

Suppose you want to calculate the depreciation of the property. In this case, enter the word “depreciation” in the function search area. The program will select all functions for depreciation (Fig. 7).

After filling in the corresponding fields of the function, the depreciation of the property will be calculated.

Often you need to add numbers that satisfy some condition. In this case, you should use the SUMIF function. Let's consider a specific example. Let's say it is necessary to calculate the amount of commission if the value of the property exceeds 75,000 rubles. To do this, we use the data from the table of dependence of commissions on the value of property (Fig. 8).

Our actions in this case are as follows. Place the cursor in cell B6, use the button to launch the “Function Wizards”, in the “Mathematical” category, select the SUMIF function, set the parameters as in fig. nine.

Please note that we select the interval of cells A2: A6 (property value) as the range for checking the condition, and B2: B6 (commissions) as the summation range, while the condition looks like (\u003e 75000). The result of our calculation will be 27,000 rubles.

:: Give a name to the cell

For the convenience of working in Excel, it is possible to assign names to individual cells or ranges, which can then be used in formulas along with regular addresses. To quickly name a cell, select it, position the pointer over the name field on the left side of the formula bar, click and enter a name.

When assigning names, it must be remembered that they can consist of letters (including the Russian alphabet), numbers, periods and underscores. The first character in the name must be a letter or an underscore. Names cannot have the same appearance as cell references, such as Z $ 100 or R1C1. The name can contain more than one word, but spaces are not allowed. Underscores and periods can be used as word delimiters, for example Sales_Tax or First.Quarter. The name can be up to 255 characters long. In this case, uppercase and lowercase letters are perceived the same.

To insert a name into a formula, you can use the command “Insert ® Name ® Insert” by selecting the required name in the list of names.

It is useful to remember that names in Excel are used as absolute references, that is, they are a kind of absolute addressing, which is convenient when copying formulas.

Names in Excel can be defined not only for individual cells, but also for ranges (including non-adjacent ones). To assign a name, simply select the range and then enter the name in the name field. In addition, to specify the names of ranges containing titles, it is convenient to use the special “Create” command in the “Insert ® Name” menu.

To remove a name, select it in the list and click the “Remove” button.

When you create a formula that references data from a worksheet, you can use row and column headers to specify the data. For example, if you assign a column name to the column values \u200b\u200b(Figure 10),

then the formula \u003d SUM (Commissions) is used to calculate the total amount for the "Commission" column (Fig. 11).

:: More Excel Features - Templates

MS Excel includes a set of templates - Excel tables, which are designed to analyze the economic activities of an enterprise, draw up an invoice, order, and even for accounting for a personal budget. They can be used to automate common tasks. So, you can create documents based on the templates “Advance report”, “Invoice”, “Order”, which contain the forms of documents used in business activities. These forms do not differ from standard ones in appearance and when printed, and the only thing that needs to be done to receive a document is to fill in its fields.

To create a document based on a template, execute the “New” command from the “File” menu, then select the required template on the “Solutions” tab (Fig. 12).

Templates are copied to disk in a typical Excel installation. If templates do not appear in the New Document dialog box, run Excel Setup and install the templates. For details on installing templates, see the “Installing Microsoft Office Components” section in Excel Help.

For example, to create a series of financial documents, select the “Financial Templates” template (Figure 13).

This group of templates contains the forms of the following documents:

Travel certificate;
... advance report;
... payment order;
... invoice;
... waybill;
... power of attorney;
... incoming and outgoing orders;
... payments for telephone and electricity.

Select the required form to fill out, and then enter all the necessary details in it and print it. If desired, the document can be saved as a regular Excel spreadsheet.

Excel allows the user to create document templates by himself, as well as edit existing ones.

However, the forms of the documents may change over time, and then the existing template will become unusable. In addition, in the templates that are available in the Excel delivery, it would be nice to include in advance such permanent information as data about your organization, the leader. Finally, you may need to create your own template: for example, in the planning department, you will most likely need templates for preparing estimates and calculations, and in accounting, an invoice form with the corporate logo of your organization.

For such cases, Excel, like many other programs that work with electronic documents, provides the ability to create and edit templates for frequently used documents. An Excel template is a special workbook that can be used as a reference when creating other workbooks of the same type. Unlike a regular Excel workbook with the * .xls extension, the template file has the * .xlt extension.

When creating a document based on a template, Excel program automatically creates its working copy with the * .xls extension, adding a serial number to the end of the document name. The original template remains intact and can be reused later.

To automatically enter the date, you can use the following method: enter the TODAY function in the date cell, after which it will display the current day of the month, month and year, respectively.

Of course, you can use all the above actions on templates when working with regular Excel workbooks.

The formula tells Excel how to deal with numbers, values \u200b\u200bin a cell, or group of cells. Without formulas, spreadsheets are unnecessary.

Formula constructs include: constants, operators, references, functions, range names, parentheses containing arguments, and other formulas. Using an example, we will analyze the practical application of formulas for novice users.

Excel formulas for dummies

To set a formula for a cell, you must activate it (place the cursor) and enter equals (\u003d). You can also enter an equal sign in the formula bar. After entering the formula, press Enter. The result of the calculation appears in the cell.

Excel uses standard mathematical operators:

The "*" symbol is always used for multiplication. Omitting it, as is customary in written arithmetic calculations, is unacceptable. That is, the record (2 + 3) 5 Excel will not understand.

Excel can be used as a calculator. That is, to enter numbers and operators of mathematical calculations into the formula and immediately get the result.

But more often, cell addresses are entered. That is, the user enters a cell reference, with the value of which the formula will operate on.

When the values \u200b\u200bin the cells change, the formula automatically recalculates the result.

The operator has multiplied the value of cell B2 by 0.5. To enter a cell reference into a formula, simply click on that cell.

In our example:

1. We put the cursor in cell B3 and entered \u003d.
2. We clicked on cell B2 - Excel “designated” it (the cell name appeared in the formula, a “flashing” rectangle formed around the cell).
3. They entered the * sign, the value 0.5 from the keyboard and pressed ENTER.

If several operators are used in one formula, the program will process them in the following sequence:

• %, ^;
• *, /;
• +, -.

You can change the sequence by using parentheses: Excel first evaluates the value of the expression in parentheses.

How to designate a constant cell in an Excel formula

There are two types of cell references: relative and absolute. When you copy a formula, these links behave differently: the relative ones change, the absolute ones remain constant.

Find the autocomplete marker in the lower right corner of the first cell in the column. Click on this point with the left mouse button, hold it and "drag" down the column.

Release the mouse button - the formula will be copied to the selected cells with relative links. That is, each cell will have its own formula with its own arguments.

Formulas in Excel are its main essence, for which this program was created by Microsoft. Formulas allow you to calculate the values \u200b\u200bof cells based on the data of other cells, and if the original data changes, then the result of calculations in the cell where the formula stands will be recalculated automatically!

Creating formulas in Excel

Let's consider the work of formulas using the simplest example - the sum of two numbers. Let the number 2 be entered in one Excel cell, and 3. It is necessary that the sum of these numbers appear in the third cell.

The sum of 2 and 3 is, of course, 5, but you do not need to enter a five manually in the next cell, otherwise the meaning of calculations in Excel will be lost. In the cell with the total, you must enter the sum formula and then the result will be calculated by the program automatically.

In the example, the calculation looks simple, but when the numbers are large or fractional, you simply cannot do without a formula.

Formulas in Excel can contain arithmetic operations (addition +, subtraction -, multiplication *, division /), coordinates of cells of the source data (both individually and a range) and calculation functions.

Consider the formula for the sum of numbers in the example above:

SUM (A2; B2)

Each formula begins with an equal sign. If you want to add a formula to a cell by writing it manually, then this particular character should be written first.

Next in the example is the SUM function, which means that it is necessary to sum up some data, and already in the parentheses of the function, separated by semicolons, some arguments are indicated, in this case the coordinates of the cells (A2 and B2), the values \u200b\u200bof which must be added and the result should be placed in the cell where the formula is written. If you needed to add three cells, then you could write three arguments to the SUM function, separating them with a semicolon, for example:

SUM (A4; B4; C4)

When you need to add a large number of cells, then specifying each of them in the formula will take a very long time, so instead of a simple enumeration, you can use an indication of a range of cells:

SUM (B2: B7)

The range of cells in Excel is indicated by the coordinates of the first and last cells, separated by a colon. This example adds the values \u200b\u200bof the cells from cell B2 to cell B7.

Functions in formulas can be connected and combined as you need to get the desired result. For example, the task is to add three numbers and, depending on whether the result is less than 100 or more, multiply the sum by a factor of 1.2 or 1.3. The following formula will help to solve the problem:

IF (SUM (A2: C2)

Let's analyze the solution to the problem in more detail. Two functions were used IF and SUM. The IF function always has three arguments: the first is the condition, the second is the action if the condition is true, and the third is the action if the condition is not true. As a reminder, the arguments are separated by a semicolon.

IF (condition; true; false)

As a condition, it is indicated that the sum of the range of cells A2: C2 is less than 100. If during the calculation, the condition is fulfilled and the sum of the cells of the range is equal, for example, 98, then Excel will perform the action specified in the second argument of the IF function, i.e. SUM (A2: C2) * 1.2. If the sum exceeds 100, then the action in the third argument of the IF function will be performed, i.e. SUM (A2: C2) * 1.3.

Built-in functions in Excel

There are a lot of functions in Excel and it is simply impossible to know everything. Some of the frequently used ones can be remembered, and some you will need only occasionally, and remembering their name and, moreover, the form of recording is very difficult.

But Excel has a standard way to insert functions with their full list. If you want to add a function to a cell, then click on the cell and select insert function from the main menu. The program will display a list of functions and you can select the one that is needed to solve the problem.

To insert a function into Excel 2007, select the "Formulas" item in the main menu and click on the "Insert Function" icon, or press the Shift + F3 key combination on the keyboard.

In Excel 2003, a function is inserted through the "Insert" -\u003e "Function" menu. The Shift + F3 key combination also works.

An equal sign will appear in the cell where the cursor was, and the "Function Wizard" window will appear on top of the sheet.

Function in Excel is divided into categories. If you know to which category your proposed function may belong, then choose a selection by it. Otherwise select "Complete alphabetical listing". The program will display all available functions in the function list.

Scroll through the list and select with the mouse the name of the function you are interested in. Its notation form, the required arguments and a short description that will explain the purpose of the function will appear just below the list. When you find what you need, click on the "OK" button to proceed to specifying the arguments.

The arguments window contains fields named "Number 1", "Number 2", etc. They must be filled with the coordinates of the cells (or ranges) in which you want to take data. You can fill in manually, but it is much more convenient to click on the table icon at the end of the field in order to indicate the source cell or range.

The arguments window will be simplified. Now you need to click with the mouse on the first source cell with data, and then again on the table icon in the arguments window.

The "Number 1" field will be filled with the coordinates of the selected cell. The same procedure should be done for the "Number 2" field and for the following fields if you have more than two function arguments.

Having filled in all the arguments, you can already preview the result of calculating the resulting formula. To make it appear in a cell on the sheet, click the "OK" button. In this example, cell D2 contains the product of the numbers in cells B2 and C2.

The considered method of inserting a function is universal and allows you to add any function from the general list of standard Excel functions.

Excel 2007 uses formulas and functions to perform various calculations. The basic concepts of formulas and functions in Excel 2003 are presented on the page. The stated information about formulas and functions for Excel 2003 is also typical for Excel 2007/2010.

Performing calculations using formulas in Excel 2007 is shown in Figure 1.

Figure: 1

Formula calculations always begin with an equal sign "\u003d". In addition, the formula can include references to cells in which certain values \u200b\u200bare specified in one of the number formats, names, numeric values \u200b\u200band functions combined with arithmetic signs (^; *; /; +; -;). The cell in which the calculations are performed displays the values, and the formula appears in the input line.

Cell references are either relative or absolute. Absolute references differ from relative references in that they contain a “$” character (for example, $ B $ 2) in front of all attributes of the cell address. Mixed references are cell references that prefix one of the cell address attributes with a “$” character (for example, B $ 2 or $ B2).

Sample formula (Y \u003d 2sin (nx) cos 5 (2nx) + sin (5 nx) 2, where n is the number PI \u003d 3, 14), which contains the functions sin (), cos (), PI (), the variable x - a relative reference to cell "A2", shown in the screenshot, Figure 2.

Figure: 2

Cell range names can also be used in a formula, but you must assign names to the cell ranges. To name a range of cells in Excel 2007, you need to follow these steps. Select the range of cells you want, go to the Formulas tab, and click the Assign Name drop-down button in the Defined Names group.

From the list select the inscription "Assign a name ...", a dialog box "Create a name" will open. In the "Name" line, enter a name, for example, "Purchase1" and click the OK button. As a result, the selected range of cells will be assigned an appropriate name (Figure 3).

Figure: 3

To use the name of a range of cells in a formula, select the cell and insert the required formula, for example SUM (). Use the name of the cell range as an argument, for example "Purchase1". To do this, on the "Formulas" tab, in the "Defined Names" group, click on the "Use in Formula" command, and select the required name in the list of names that opens (Figure 4).

Figure: 4

Formulas in Excel can be moved and copied. Absolute references do not change when the formula is copied (spreading the formula), but only those attributes of the cell address that are not preceded by the “$” symbol will change (ie, relative references are changed). Moving the formula to a different location in the table does not change the absolute and relative references in the formula.

If you've never used a spreadsheet to create documents before, we recommend reading our Excel guide for dummies.

After that, you can create your first spreadsheet with tables, graphs, math formulas and formatting.

Detailed information about the basic functions and capabilities of the MS Excel spreadsheet processor. Description of the main elements of the document and instructions for working with them in our material.

Working with cells. Padding and formatting

Before you start any specific steps, you need to understand the basic element of any document in Excel. An Excel file consists of one or more sheets divided into small cells.

A cell is a basic component of any Excel report, spreadsheet or graph. Each cell contains one block of information. It can be a number, date, currency, unit of measure, or other data format.

To fill in a cell, just click on it with the pointer and enter the required information. To edit a previously filled cell, double-click on it.

Figure: 1 - an example of filling cells

Each cell on the sheet has its own unique address. Thus, you can carry out calculations or other operations with it. When you click on a cell, a field with its address, name and formula will appear at the top of the window (if the cell is involved in any calculations).

Let's select the cell "Share of shares". Its location address is A3. This information is indicated in the properties panel that opens. We can also see the content. This cell has no formulas, so they are not shown.

More cell properties and functions that can be used in relation to it are available in the context menu. Click on the cell with the right key of the manipulator. A menu will open with which you can format the cell, analyze the content, assign a different value and other actions.

Figure: 2 - context menu of a cell and its main properties

Sorting data

Often users are faced with the task of sorting data on a sheet in Excel. This feature helps you quickly select and view only the data you want from the entire table.

Before you is a completed table (we'll figure out how to create it later in the article). Imagine that you want to sort the data for January in ascending order. How would you do it? A banal retyping of a table is an extra work, besides, if it is voluminous, no one will do it.

Excel has a special function for sorting. The user is only required to:

• Select a table or block of information;
• Open the "Data" tab;
• Click on the "Sort" icon;

Figure: 3 - "Data" tab

• In the window that opens, select the column of the table, over which we will carry out actions (January).
• Next, the sorting type (we are grouping by value) and, finally, the order is ascending.
• Confirm the action by clicking on "OK".

Figure: 4 - setting sorting parameters

The data will be automatically sorted:

Figure: 5 - the result of sorting the digits in the column "January"

Similarly, you can sort by color, font and other parameters.

Mathematical calculations

The main advantage of Excel is the ability to automatically carry out calculations in the process of filling the table. For example, we have two cells with values \u200b\u200b2 and 17. How can we enter their result into the third cell without doing the calculations ourselves?

To do this, you need to click on the third cell in which the final result of the calculations will be entered. Then click on the function icon f (x) as shown in the figure below. In the window that opens, select the action you want to apply. SUM is the sum, AVERAGE is the average, and so on. A complete list of functions and their names in the Excel editor can be found on the official website of Microsoft.

We need to find the sum of two cells, so we click on "SUM".

Figure: 6 - selection of the "SUM" function

There are two fields in the function arguments window: "Number 1" and "Number 2". Select the first field and click on the cell with the number "2". Its address will be written to the argument string. Click on "Number 2" and click on the cell with the number "17". Then confirm the action and close the window. If you need to perform math with three or more boxes, just keep entering argument values \u200b\u200bin the Number 3, Number 4, and so on boxes.

If in the future the value of the summed cells changes, their sum will be updated automatically.

Figure: 7 - the result of the calculations

Creating tables

Any data can be stored in Excel tables. With the help of the quick setup and formatting function, it is very easy to organize a personal budget control system, a list of expenses, digital data for reporting, and so on in the editor.

Tables in Excel take precedence over the similar option in Word and other office programs. Here you have the opportunity to create a table of any dimension. The data is filled in easily. There is a function panel for editing content. In addition, the finished table can be integrated into the docx file using the usual copy-paste function.

To create a table, follow the instructions:

• Click the Insert tab. On the left side of the options pane, select Table. If you need to summarize any data, select the "Pivot Table" item;
• Using the mouse, select a place on the sheet that will be allocated for the table. And also you can enter data location in the element creation window;
• Click OK to confirm the action.

Figure: 8 - creating a standard table

To format the appearance of the resulting plate, open the contents of the constructor and in the "Style" field, click on the template you like. If desired, you can create your own look with a different color scheme and cell selection.

Figure: 9 - table formatting

The result of filling the table with data:

Figure: 10 - filled table

For each cell in the table, you can also customize the data type, formatting and information display mode. The designer window contains all the necessary options for further configuration of the plate, based on your requirements.

Adding graphs / charts

To build a chart or graph, a ready-made plate is required, because the graphic data will be based precisely on information taken from individual lines or cells.

To create a chart / graph, you need:

• Select the table completely. If you need to create a graphic element only to display the data of certain cells, select only them;
• Open the insert tab;
• In the recommended charts box, select the icon that you think will best visually describe the tabular information. In our case, this is a 3-D pie chart. Move the pointer to the icon and select the appearance of the element;

Similarly, you can create scatter plots, line charts, and table element dependencies. All obtained graphic elements can also be added to Word text documents.

There are many other functions in the Excel spreadsheet editor, however, the techniques described in this article will be enough for initial work. In the process of creating a document, many users independently master more advanced options. This is due to the convenient and intuitive interface of the latest versions of the program.

Thematic videos: