Working with formulas in Excel detailed analysis. Complete information about formulas in Excel X-el program for beginners

• “less than or equal” sign – “<=»;

• “not equal” sign – “<>».

Text concatenation operator

The special character “&” (ampersand) is used for this purpose. Using it, you can connect different fragments into one whole - the same principle as with the “CONNECT” function. Here are some examples:

1. If you want to merge text in cells, then you need to use the following code.

1. In order to insert any symbol or letter between them, you need to use the following construction.

1. You can merge not only cells, but also ordinary symbols.

Any text other than links must be quoted. Otherwise the formula will generate an error.

Please note that the quotes used are exactly the same as in the screenshot.

The following operators can be used to define links:

• in order to create a simple link to the desired range of cells, just indicate the first and last cell of this area, and between them the symbol “:”;
• to combine links the sign “;” is used;
• if it is necessary to determine cells that are at the intersection of several ranges, then a “space” is placed between the links. In this case, the value of cell “C7” will be displayed.

Because only it falls under the definition of “intersection of sets.” This is the name given to this operator (space).

Using links

While working in the Excel editor, you can use various types of links. However, most novice users know how to use only the simplest of them. We will teach you how to correctly enter links of all formats.

Simple links A1

As a rule, this type is used most often, since they are much more convenient to compose than others.

• columns – from A to XFD (no more than 16384);
• lines – from 1 to 1048576.

Here are some examples:

• the cell at the intersection of row 5 and column B is “B5”;
• the range of cells in column B starting from line 5 to line 25 is “B5:B25”;
• the range of cells in row 5 starting from column B to F is “B5:F5”;
• all cells in row 10 are “10:10”;
• all cells in rows 10 to 15 are “10:15”;
• all cells in column B are “B:B”;
• all cells in columns B to K are “B:K”;
• The range of cells B2 to F5 is “B2-F5”.

Sometimes formulas use information from other sheets. It works as follows.

The second sheet contains the following information.

If there is a space in the name of the sheet, then it must be indicated in the formula in single quotes (apostrophes).

Absolute and relative links

Excel editor works with three types of links:

• absolute;
• relative;
• mixed.

Let's take a closer look at them.

All the previously mentioned examples refer to relative cell addresses. This type is the most popular. The main practical advantage is that the editor will change the references to a different value during migration. In accordance with where exactly you copied this formula. For the calculation, the number of cells between the old and new positions will be taken into account.

Imagine that you need to stretch this formula across an entire column or row. You will not manually change letters and numbers in cell addresses. It works as follows.

1. Let's enter a formula to calculate the sum of the first column.

1. Press the hotkeys Ctrl + C. In order to transfer the formula to an adjacent cell, you need to go there and press Ctrl + V.

If the table is very large, it is better to click on the lower right corner and, without releasing your finger, drag the pointer to the end. If there is little data, then copying using hot keys is much faster.

1. Now look at the new formulas. The column index changed automatically.

If you want all links to be preserved when transferring formulas (that is, so that they do not change automatically), you need to use absolute addresses. They are indicated as "$B$2".

As a result, we see that no changes have occurred. All columns display the same number.

This type of address is used when it is necessary to fix only a column or row, and not all at the same time. The following constructions can be used:

• $D1, $F5, $G3 – for fixing columns;
• D$1, F$5, G$3 – for fixing rows.

Work with such formulas only when necessary. For example, if you need to work with one constant row of data, but only change the columns. And most importantly, if you are going to calculate the result in different cells that are not located along the same line.

The fact is that when you copy the formula to another line, the numbers in the links will automatically change to the number of cells from the original value. If you use mixed addresses, then everything will remain in place. This is done as follows.

1. Let's use the following expression as an example.

1. Let's move this formula to another cell. Preferably not on the next or on another line. Now you see that the new expression contains the same line (4), but a different letter, since it was the only one that was relative.

3D links

The concept of “three-dimensional” includes those addresses in which a range of sheets is indicated. An example formula looks like this.

In this case, the result will correspond to the sum of all cells “A5” on all sheets, starting from 1 to 4. When composing such expressions, you must adhere to the following conditions:

• such references cannot be used in arrays;
• three-dimensional expressions are prohibited from being used where there is an intersection of cells (for example, the “space” operator);
• When creating formulas with 3D addresses, you can use the following functions: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNTA, COUNT, MIN, MAX, MINA, MAX, VARVE, PRODUCT, VARIANCE, VAR. and DISPA.

If you break these rules, you will see some kind of error.

R1C1 format links

This type of link differs from “A1” in that the number is assigned not only to rows, but also to columns. The developers decided to replace the regular view with this option for convenience in macros, but they can be used anywhere. Here are some examples of such addresses:

• R10C10 – absolute reference to the cell, which is located on the tenth line of the tenth column;
• R – absolute link to the current (in which the formula is indicated) link;
• R[-2] – a relative link to a line that is located two positions above this one;
• R[-3]C is a relative reference to a cell that is located three positions higher in the current column (where you decided to write the formula);
• RC is a relative reference to a cell that is located five cells to the right and five lines below the current one.

Use of names

Excel allows you to create your own unique names for naming ranges of cells, single cells, tables (regular and pivot), constants, and expressions. At the same time, for the editor there is no difference when working with formulas - he understands everything.

You can use names for multiplication, division, addition, subtraction, calculation of interest, coefficients, deviation, rounding, VAT, mortgage, loan, estimate, timesheets, various forms, discounts, salaries, length of service, annuity payment, working with VPR formulas , “VSD”, “INTERMEDIATE.RESULTS” and so on. That is, you can do whatever you want.

There is only one main condition - you must define this name in advance. Otherwise Excel will not know anything about it. This is done as follows.

1. Select a column.
2. Call the context menu.
3. Select "Assign a name".

1. Specify the desired name for this object. In this case, you must adhere to the following rules.

1. To save, click on the “OK” button.

In the same way, you can assign a name to a cell, text or number.

You can use the information in the table both using names and using regular links. This is what the standard version looks like.

And if you try to insert our name instead of the address “D4:D9”, you will see a hint. Just write a few characters and you will see what fits (from the name database) the most.

In our case, everything is simple - “column_3”. Imagine that you will have a large number of such names. You won't be able to remember everything by heart.

Using Functions

There are several ways to insert a function in Excel:

• manually;
• using the toolbar;
• using the Insert Function window.

Let's take a closer look at each method.

In this case, everything is simple - you use your hands, your own knowledge and skills to enter formulas in a special line or directly in a cell.

If you do not have working experience in this area, then it is better to use easier methods at first.

In this case it is necessary:

1. Go to the "Formulas" tab.
2. Click on any library.
3. Select the desired function.

1. Immediately after this, the Arguments and Functions window will appear with the function already selected. All you have to do is enter the arguments and save the formula using the “OK” button.

Substitution Wizard

You can apply it as follows:

1. Make any cell active.
2. Click on the “Fx” icon or use the keyboard shortcut SHIFT + F3.

1. Immediately after this, the “Insert Function” window will open.
2. Here you will see a large list of different features sorted by category. In addition, you can use the search if you cannot find the item you need.

All you have to do is type in some word that can describe what you want to do, and the editor will try to display all the suitable options.

1. Select a function from the list provided.
2. To continue, you need to click on the “OK” button.

1. You will then be asked to specify "Arguments and Functions". You can do this manually or simply select the desired range of cells.
2. In order to apply all the settings, you need to click on the “OK” button.

1. As a result of this, we will see the number 6, although this was already clear, since the preliminary result is displayed in the “Arguments and Functions” window. The data is recalculated instantly when any of the arguments changes.

Using Nested Functions

As an example, we will use formulas with logical conditions. To do this, we will need to add some kind of table.

Then follow the following instructions:

1. Click on the first cell. Call up the “Insert Function” window. Select the "If" function. To insert, click on “OK”.

1. Then you will need to create some kind of logical expression. It must be written in the first field. For example, you can add the values ​​of three cells in one row and check whether the sum is greater than 10. If “true”, indicate the text “Greater than 10”. For a false result – “Less than 10”. Then click “OK” to return to the workspace.

1. As a result, we see the following - the editor showed that the sum of the cells in the third line is less than 10. And this is correct. This means our code works.

1. Now you need to configure the following cells. In this case, our formula simply extends further. To do this, you first need to hover the cursor over the lower right corner of the cell. After the cursor changes, you need to left click and copy it to the very bottom.

1. As a result, the editor recalculates our expression for each line.

As you can see, the copying was quite successful because we used the relative links we talked about earlier. If you need to assign addresses to function arguments, then use absolute values.

You can do this in several ways: use the formula bar or a special wizard. In the first case, everything is simple - click in a special field and manually enter the necessary changes. But writing there is not entirely convenient.

The only thing you can do is make the input field larger. To do this, just click on the indicated icon or press the key combination Ctrl + Shift + U.

It's worth noting that this is the only way if you don't use functions in your formula.

If you use functions, everything becomes much simpler. To edit you must follow the following instructions:

1. Make the cell with the formula active. Click on the "Fx" icon.

1. After this, a window will appear in which you can change the function arguments you need in a very convenient way. In addition, here you can find out exactly what the result of recalculating the new expression will be.

1. To save the changes you have made, use the “OK” button.

To remove an expression, just do the following:

1. Click on any cell.

1. Click on the Delete or Backspace button. As a result, the cell will be empty.

You can achieve exactly the same result using the “Clear All” tool.

Possible errors when creating formulas in the Excel editor

Listed below are the most popular mistakes made by users:

• The expression uses a huge number of nestings. There should be no more than 64 of them;
• formulas indicate paths to external books without the full path;
• Opening and closing brackets are placed incorrectly. This is why in the editor, in the formula bar, all brackets are highlighted in a different color;

• the names of books and sheets are not placed in quotation marks;
• numbers are used in the wrong format. For example, if you need to enter $2000, you need to simply enter 2000 and select the appropriate cell format, since the $ symbol is used by the program for absolute references;

• Required function arguments are not specified. Note that optional arguments are enclosed in square brackets. Everything without them is necessary for the formula to work properly;

• The cell ranges are specified incorrectly. To do this, you must use the “:” (colon) operator.

Error codes when working with formulas

When working with a formula, you may see the following error options:

• #VALUE! – this error indicates that you are using the wrong data type. For example, you are trying to use text instead of a numeric value. Of course, Excel will not be able to calculate the sum between two phrases;
• #NAME? – such an error means that you made a typo in the spelling of the function name. Or are you trying to enter something that doesn’t exist. You can't do that. Besides this, the problem could be something else. If you are sure of the function name, then try looking at the formula more closely. Perhaps you forgot a parenthesis. In addition, you need to take into account that text fragments are indicated in quotation marks. If all else fails, try composing the expression again;
• #NUMBER! – displaying a message like this means that you have some problem with the arguments or the result of the formula. For example, the number turned out to be too huge or, on the contrary, small;
• #DIV/0! – this error means that you are trying to write an expression in which division by zero occurs. Excel can't override the rules of math. Therefore, such actions are also prohibited here;
• #N/A! – the editor can show this message if some value is not available. For example, if you use the SEARCH, SEARCH, MATCH functions, and Excel does not find the fragment you are looking for. Or there is no data at all and the formula has nothing to work with;
• If you are trying to calculate something and Excel writes the word #REF!, then the function argument is using the wrong range of cells;
• #EMPTY! – this error appears if you have an inconsistent formula with overlapping ranges. More precisely, if in reality there are no such cells (which happen to be at the intersection of two ranges). Quite often this error occurs by accident. It is enough to leave one space in the argument, and the editor will perceive it as a special operator (we talked about it earlier).

When you edit the formula (the cells are highlighted), you will see that they do not actually intersect.

Sometimes you can see a lot of # characters that completely fill the width of the cell. In fact, there is no error here. This means that you are working with numbers that do not fit in a given cell.

To see the value contained there, just resize the column.

In addition, you can use cell formatting. To do this you need to follow a few simple steps:

1. Call the context menu. Select Format Cells.

1. Specify the type as "General". To continue, use the “OK” button.

Thanks to this, the Excel editor will be able to convert this number into another format that fits in this column.

Examples of using formulas

The Microsoft Excel editor allows you to process information in any way convenient for you. There are all the necessary conditions and opportunities for this. Let's look at a few examples of formulas by category. This will make it easier for you to understand.

In order to evaluate the mathematical capabilities of Excel, you need to perform the following steps.

1. Create a table with some conditional data.

1. To calculate the amount, enter the following formula. If you want to add just one value, you can use the addition operator (“+”).

1. Oddly enough, in the Excel editor you cannot take away using functions. For subtraction, the usual “-” operator is used. In this case, the code will be as follows.

1. In order to determine how much the first number is from the second as a percentage, you need to use this simple construction. If you want to subtract several values, you will have to enter a “minus” for each cell.

Note that the percent symbol is placed at the end, not at the beginning. In addition, when working with percentages, you do not need to additionally multiply by 100. This happens automatically.

1. Excel can add based on several conditions at once. You can calculate the sum of cells in the first column whose value is greater than 2 and less than 6. And the same formula can be set for the second column.

=SUMIFS(B3:B9,B3:B9,">2",B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2",C3:C9,"<6")

1. You can also count the number of elements that satisfy some condition. For example, let Excel count how many numbers we have greater than 3.

=COUNTIF(B3:B9,">3") =COUNTIF(C3:C9,">3")

1. The result of all formulas will be as follows.

Mathematical functions and graphs

Using Excel, you can calculate various functions and build graphs based on them, and then conduct graphical analysis. As a rule, such techniques are used in presentations.

As an example, let's try to build graphs for an exponent and some equation. The instructions will be as follows:

1. Let's create a table. In the first column we will have the initial number “X”, in the second - the “EXP” function, in the third - the specified ratio. It would be possible to make a quadratic expression, but then the resulting value would practically disappear against the background of the exponential on the graph.

As we said earlier, the growth of the exponent occurs much faster than that of the ordinary cubic equation.

Any function or mathematical expression can be represented graphically in this way.

Everything described above is suitable for modern programs of 2007, 2010, 2013 and 2016. The old Excel editor is significantly inferior in terms of capabilities, number of functions and tools. If you open the official help from Microsoft, you will see that they additionally indicate in which version of the program this function appeared.

In all other respects, everything looks almost exactly the same. As an example, let's calculate the sum of several cells. To do this you need:

1. Provide some data for calculation. Click on any cell. Click on the "Fx" icon.

1. Select the “Mathematical” category. Find the “SUM” function and click on “OK”.
1. You can try to recalculate in any other editor. The process will happen exactly the same.

Conclusion

In this tutorial, we talked about everything related to formulas in the Excel editor, from the simplest to the very complex. Each section was accompanied by detailed examples and explanations. This is done to ensure that the information is accessible even to complete dummies.

If something doesn’t work out for you, it means you’re making a mistake somewhere. You may have misspelled expressions or incorrect cell references. The main thing is to understand that everything needs to be driven in very carefully and carefully. Moreover, all functions are not in English, but in Russian.

In addition, it is important to remember that formulas must begin with the “=” (equals) symbol. Many novice users forget about this.

Examples file

To make it easier for you to understand the previously described formulas, we have prepared a special demo file in which all the above examples were compiled. You can do it from our website completely free of charge. If during training you use a ready-made table with formulas based on the completed data, you will achieve results much faster.

Video instruction

If our description did not help you, try watching the video attached below, which explains the main points in more detail. You may be doing everything right, but you're missing something. With the help of this video you should understand all the problems. We hope that lessons like this have helped you. Check us out more often.

Grouping data

When you are preparing a product catalog with prices, it would be a good idea to worry about its ease of use. A large number of positions on one sheet forces you to use a search, but what if the user is just making a selection and has no idea about the name? In Internet catalogs, the problem is solved by creating product groups. So why not do the same in an Excel workbook?

Organizing a group is quite simple. Select several lines and click the button Group on the tab Data(see Fig. 1).

Figure 1 – Group button

Then specify the grouping type - line by line(see Fig. 2).

Figure 2 – Selecting a grouping type

As a result, we get... not what we need. The product lines were combined into a group indicated below them (see Fig. 3). In directories, the title usually comes first, and then the content.

Figure 3 – Grouping rows “down”

This is not a program error at all. Apparently, the developers considered that the grouping of rows is mainly done by the preparers of financial statements, where the final result is displayed at the end of the block.

To group rows “up” you need to change one setting. On the tab Data click on the small arrow in the lower right corner of the section Structure(see Fig. 4).

Figure 4 – Button responsible for displaying the structure settings window

In the settings window that opens, uncheck the item Totals in the rows below the data(see Fig. 5) and press the button OK.

Figure 5 – Structure settings window

All groups that you have created will automatically change to the “top” type. Of course, the set parameter will also affect the further behavior of the program. However, you will have to uncheck this box to everyone a new sheet and each new Excel workbook, because The developers did not provide for a “global” setting of the grouping type. Likewise, you cannot use different types of groups within the same page.

Once you have categorized your products, you can organize the categories into larger sections. There are up to nine grouping levels in total.

The inconvenience when using this function is that you have to press a button OK in the pop-up window, and it will not be possible to collect unrelated ranges in one go.

Figure 6 – Multi-level directory structure in Excel

Now you can open and close parts of the catalog by clicking on the pluses and minuses in the left column (see Figure 6). To expand the entire level, click on one of the numbers at the top.

To move rows to a higher level of the hierarchy, use the button Ungroup tabs Data. You can completely get rid of grouping using the menu item Delete structure(see Fig. 7). Be careful, it is impossible to cancel the action!

Figure 7 – Ungrouping rows

Freeze areas of the sheet

Quite often when working with Excel tables, it becomes necessary to freeze some areas of the sheet. There may be, for example, row/column headings, a company logo or other information.

If you freeze the first row or the first column, then everything is very simple. Open the tab View and in the drop down menu To fix areas select items accordingly Freeze top row or Freeze first column(see Fig. 8). However, it will not be possible to “freeze” both a row and a column at the same time.

Figure 8 – Freeze a row or column

To unpin, select the item in the same menu Unlock areas(the item replaces the line To fix areas, if “freezing” is applied on the page).

But pinning several rows or an area of ​​rows and columns is not so transparent. You select three lines, click on the item To fix areas, and... Excel “freezes” only two. Why is that? An even worse scenario is possible, when areas are fixed in an unpredictable way (for example, you select two lines, and the program sets the boundaries after the fifteenth). But let’s not attribute this to the developers’ oversight, because the only correct way to use this function looks different.

You need to click on the cell below the rows that you want to freeze, and, accordingly, to the right of the columns to be docked, and only then select the item To fix areas. Example: in Figure 9 the cell is highlighted B 4. This means that three rows and the first column will be fixed, which will remain in place when scrolling the sheet both horizontally and vertically.

Figure 9 – Freeze an area of ​​rows and columns

You can apply a background fill to pinned areas to indicate to the user that those cells behave differently.

Rotate the sheet (replacing rows with columns and vice versa)

Imagine this situation: you worked for several hours on typing a table in Excel and suddenly realized that you had designed the structure incorrectly - the column headings should have been written by rows or rows by columns (it doesn’t matter). Do I have to re-type everything manually? Never! Excel provides a function that allows you to “rotate” a sheet 90 degrees, thus moving the contents of the rows into columns.

Figure 10 – Source table

So, we have some table that needs to be “rotated” (see Fig. 10).

1. Select cells with data. It is the cells that are selected, not the rows and columns, otherwise nothing will work.
2. Copy them to the clipboard using a keyboard shortcut or in any other way.
3. Move to an empty sheet or free space of the current sheet. Important Note: You cannot paste over current data!
4. Inserting data using a key combination and in the insert options menu select the option Transpose(see Fig. 11). Alternatively, you can use the menu Insert from tab home(see Fig. 12).

Figure 11 – Insert with transposition

Figure 12 – Transpose from the main menu

That's all, the table has been rotated (see Fig. 13). In this case, the formatting is preserved, and the formulas are changed in accordance with the new position of the cells - no routine work is required.

Figure 13 – Result after rotation

Showing formulas

Sometimes a situation arises when you cannot find the desired formula among a large number of cells, or you simply do not know what and where to look. In this case, you will need the ability to display on a sheet not the result of calculations, but the original formulas.

Click the button Show formulas on the tab Formulas(see Figure 14) to change the presentation of the data on the worksheet (see Figure 15).

Figure 14 – “Show formulas” button

Figure 15 - Now the formulas are visible on the sheet, not the calculation results

If you have difficulty navigating the cell addresses displayed in the formula bar, click Influential cells from tab Formulas(see Fig. 14). The dependencies will be shown by arrows (see Fig. 16). To use this function, you must first highlight one cell.

Figure 16 – Cell dependencies are shown by arrows

Hides dependencies at the touch of a button Remove arrows.

Wrapping lines in cells

Quite often in Excel workbooks there are long inscriptions that do not fit into the width of the cell (see Fig. 17). You can, of course, expand the column, but this option is not always acceptable.

Figure 17 – Labels do not fit into cells

Select cells with long labels and click the button Wrap text on Home tab (see Fig. 18) to go to the multi-line display (see Fig. 19).

Figure 18 – “Wrap text” button

Figure 19 – Multi-line text display

Rotate text in a cell

Surely you have encountered a situation where text in cells needed to be placed not horizontally, but vertically. For example, to label a group of rows or narrow columns. Excel 2010 includes tools that let you rotate text in cells.

Depending on your preferences, you can go two ways:

1. First create the inscription, and then rotate it.
2. Adjust the rotation of the text in the cell, and then enter the text.

The options differ slightly, so we will consider only one of them. To begin with, I combined six lines into one using the button Combine and place in the center on Home tab (see Fig. 20) and entered a general inscription (see Fig. 21).

Figure 20 – Button for merging cells

Figure 21 – First we create a horizontal signature

Figure 22 – Text rotation button

You can further reduce the column width (see Figure 23). Ready!

Figure 23 – Vertical cell text

If you wish, you can set the text rotation angle manually. In the same list (see Fig. 22) select the item Cell alignment format and in the window that opens, set an arbitrary angle and alignment (see Fig. 24).

Figure 24 – Setting an arbitrary text rotation angle

Formatting cells by condition

Conditional formatting features have been around for a long time in Excel, but have been significantly improved by the 2010 version. You may not even have to understand the intricacies of creating rules, because... the developers have provided many preparations. Let's see how to use conditional formatting in Excel 2010.

The first thing to do is select the cells. Next, on Home tab click button Conditional Formatting and select one of the blanks (see Fig. 25). The result will be displayed on the sheet immediately, so you won’t have to go through the options for a long time.

Figure 25 – Selecting a conditional formatting template

Histograms look quite interesting and well reflect the essence of information about the price - the higher it is, the longer the segment.

Color scales and sets of icons can be used to indicate different states, such as transitions from critical to acceptable costs (see Figure 26).

Figure 26 – Color scale from red to green with intermediate yellow

You can combine histograms, scales, and icons in a single range of cells. For example, the histograms and icons in Figure 27 show acceptable and excessively poor device performance.

Figure 27 – A histogram and a set of icons reflect the performance of some conditional devices

To remove conditional formatting from cells, select them and select Conditional Formatting from the menu. Remove rules from selected cells(see Fig. 28).

Figure 28 – Removing conditional formatting rules

Excel 2010 uses presets to quickly access conditional formatting capabilities because... Setting up your own rules is far from obvious for most people. However, if the templates provided by the developers do not suit you, you can create your own rules for the design of cells according to various conditions. A full description of this functionality is beyond the scope of this article.

Using filters

Filters allow you to quickly find the information you need in a large table and present it in a compact form. For example, you can choose works by Gogol from a long list of books, and Intel processors from the price list of a computer store.

Like most operations, a filter requires selecting cells. However, you do not need to select the entire table with data; just mark the rows above the required data columns. This significantly increases the convenience of using filters.

Once the cells are selected, on the tab home click the button Sorting and Filter and select Filter(see Fig. 29).

Figure 29 – Creating filters

The cells will now transform into drop-down lists where you can set selection options. For example, we are looking for all mentions of Intel in the column Name of product. To do this, select the text filter Contains(see Fig. 30).

Figure 30 – Creating a text filter

Figure 31 – Create a filter by word

However, it is much faster to achieve the same effect by entering the word in the field Search context menu shown in Figure 30. Why then call an additional window? This is useful if you want to specify multiple selection conditions or select other filtering options ( does not contain, starts with..., ends with...).

For numeric data, other options are available (see Figure 32). For example, you can select the 10 largest or 7 smallest values ​​(the number is customizable).

Figure 32 – Numeric filters

Excel filters provide quite rich capabilities comparable to selection with a SELECT query in database management systems (DBMS).

Displaying information curves

Information curves (infocurves) are an innovation in Excel 2010. This function allows you to display the dynamics of changes in numerical parameters directly in a cell, without resorting to building a chart. Changes in numbers will be immediately shown on the micrograph.

Figure 33 – Excel 2010 info curve

To create an info curve, click on one of the buttons in the block Infocurves on the tab Insert(see Figure 34), and then specify the range of cells to plot.

Figure 34 – Inserting an info curve

Like charts, information curves have many options for customization. A more detailed guide to using this functionality is described in the article.

Conclusion

The article discussed some useful features of Excel 2010 that speed up work, improve the appearance of tables or ease of use. It doesn't matter whether you create the file yourself or use someone else's - Excel 2010 has functions for all users.

Most users of Windows-based computer systems with Microsoft Office installed have certainly encountered the MS Excel application. For novice users, the program causes some difficulties in mastering, however, working in Excel with formulas and tables is not as difficult as it might seem at first glance, if you know the basic principles embedded in the application.

What is Excel?

At its core, Excel is a full-fledged mathematical machine for performing many arithmetic, algebraic, trigonometric and other more complex operations, operating on several basic types of data, not always related specifically to mathematics.

Working with Excel tables involves using broader capabilities by combining calculations, plain text, and multimedia. But in its original form, the program was created precisely as a powerful mathematical editor. Some, however, at first mistake the application for some kind of calculator with advanced capabilities. The deepest delusion!

Working in Excel with tables for beginners: first acquaintance with the interface

First of all, after opening the program, the user sees the main window, which contains the main controls and tools for work. In later versions, when the application starts, a window appears asking you to create a new file, called “Book 1” by default, or select a template for further actions.

Working with Excel tables for beginners at the first stage of getting to know the program should come down to creating an empty table. Let's look at the main elements for now.

The main field is occupied by the table itself, which is divided into cells. Each is numbered, thanks to two-dimensional coordinates - the row number and the letter designation of the column (for example, we take Excel 2016). This numbering is necessary so that in the dependency formula it is possible to clearly identify exactly the data cell on which the operation will be performed.

At the top, as in other office applications, is the main menu bar, and just below is the toolkit. Below it there is a special line in which formulas are entered, and a little to the left you can see a window with the coordinates of the currently active cell (on which the rectangle is located). At the bottom there is a sheet panel and a horizontal movement slider, and below it there are buttons for switching views and scaling. On the right is a vertical bar for moving up/down across the sheet.

Basic types of data entry and simple operations

At first, it is assumed that a novice user will master working in Excel with tables using operations familiar to him, for example, in the same text editor Word.

As usual, you can copy, cut or paste data in the table, enter text or numeric data.

But the input is somewhat different from that produced in text editors. The fact is that the program is initially configured to automatically recognize what the user writes in the active cell. For example, if you enter the line 1/2/2016, the data will be recognized as a date, and the simplified date - 02/01/2016 - will appear in the cell instead of the entered numbers. You can change the display format quite simply (we'll get into that a little later).

The same is true with numbers. You can enter any numeric data, even with an arbitrary number of decimal places, and they will be displayed in the form in which everyone is accustomed to seeing them. But, if an integer is entered, it will be presented without the mantissa (decimal places in the form of zeros). You can change this too.

But after finishing data entry, many novice users try to move to the next cell using the keyboard arrows (similar to how you can do this in Word tables). And it doesn't work. Why? Yes, only because working with Excel tables differs quite significantly from the Word text editor. The transition can be accomplished by pressing the Enter key or by placing the active rectangle on another cell using the left mouse click. If you press the Esc key after writing something in the active cell, the entry will be canceled.

Actions with sheets

Working with sheets should not cause any difficulties at first. On the panel below there is a special button for adding sheets, after clicking on which a new table will appear with automatic transition to it and setting a name (“Sheet 1”, “Sheet 2”, etc.).

By double clicking you can activate the renaming of any of them. You can also use the right-click menu to bring up an additional menu that has several basic commands.

Cell formats

Now the most important thing is the cell format - one of the basic concepts that determines the data type that will be used to recognize its contents. You can edit the format through the right-click menu, where the corresponding line is selected, or by pressing the F2 key.

The window on the left shows all available formats, and on the right shows options for displaying data. If you look at the date example shown above, "Date" is selected as the format and the right side is set to the desired appearance (for example, February 1, 2016).

To carry out mathematical operations, you can use several formats, but in the simplest case we will choose numeric. On the right there are several input types, an indicator for the number of decimal places in the mantissa, and a field for setting the thousand separator. Using other number formats (exponential, fractional, monetary, etc.), you can also set the desired parameters.

By default, automatic data recognition is set to the general format. But when you enter text or several characters, the program can spontaneously convert it into something else. Therefore, to enter text for the active cell, you need to set the appropriate option.

Working in Excel with formulas (tables): example

Finally, a few words about formulas. And first, let's look at an example of the sum of two numbers located in cells A1 and A2. The application has an automatic summation button with some additional functions (calculation of arithmetic average, maximum, minimum, etc.). It is enough to set the active cell located in the same column below, and when you select an amount, it will be calculated automatically. The same works for horizontally located values, but the active cell for the amount must be set to the right.

But you can enter the formula manually (working with Excel tables also implies this possibility when automatic action is not provided). For the same amount, you should put an equal sign in the formula bar and write the operation in the form A1+A2 or SUM(A1;A2), and if you need to specify a range of cells, use this form after the equal sign: (A1:A20), after which it will be The sum of all numbers located in cells from the first to the twentieth inclusive is calculated.

Building graphs and diagrams

Working with Excel tables is also interesting because it involves the use of a special automated tool for constructing dependency graphs and diagrams based on selected ranges.

For this purpose, there is a special button on the panel, after clicking on which you can select any parameters or the desired view. After this, the chart or graph will be displayed on the sheet as a picture.

Cross-links, data import and export

The program also allows you to establish links between data located on different sheets, use it on files of a different format or objects located on servers on the Internet, and many other add-ons.

In addition, Excel files can be exported to other formats (for example, PDF), copied data from them, etc. But the program itself can open files created in other applications (text formats, databases, web pages, XML- documents, etc.).

As you can see, the editor's capabilities are almost unlimited. And, of course, there is simply not enough time to describe them all. Only the basics are given here, but the interested user will have to read the background information to master the program at the highest level.

Anyone who uses a computer in their daily work has, in one way or another, encountered the Excel office application, which is part of the standard Microsoft Office package. It is available in any version of the package. And quite often, when starting to get acquainted with the program, many users wonder whether they can use Excel on their own?

What is Excel?

First, let's define what Excel is and what this application is needed for. Many people have probably heard that the program is a spreadsheet editor, but the principles of its operation are fundamentally different from the same tables created in Word.

If in Word a table is more of an element in which a text or table is displayed, then a sheet with an Excel table is, in fact, a unified mathematical machine that is capable of performing a wide variety of calculations based on specified data types and formulas by which this or that mathematical or algebraic operation.

How to learn to work in Excel on your own and is it possible to do it?

As the heroine of the film “Office Romance” said, you can teach a hare to smoke. In principle, nothing is impossible. Let's try to understand the basic principles of the application's functioning and focus on understanding its main capabilities.

Of course, reviews from people who understand the specifics of the application say that you can, say, download some tutorial on how to work in Excel, however, as practice shows, and especially the comments of novice users, such materials are very often presented in a too abstruse form, and It can be quite difficult to figure out.

It seems that the best training option would be to study the basic capabilities of the program, and then apply them, so to speak, “by scientific poking.” It goes without saying that you first need to consider the basic functional elements of Microsoft Excel (the program lessons indicate exactly this) in order to get a complete picture of the principles of operation.

Key elements to pay attention to

The very first thing the user pays attention to when launching the application is a sheet in the form of a table, in which cells are located, numbered in different ways, depending on the version of the application itself. In earlier versions, columns were designated by letters, and rows by numbers and numbers. In other releases, all markings are presented exclusively in digital form.

What is it for? Yes, only so that it is always possible to determine the cell number for specifying a certain calculation operation, similar to how coordinates are specified in a two-dimensional system for a point. Later it will be clear how to work with them.

Another important component is the formula bar - a special field with an “f x” icon on the left. This is where all operations are specified. At the same time, the mathematical operations themselves are designated in exactly the same way as is customary in the international classification (equal sign “=”, multiplication “*” division “/”, etc.). Trigonometric quantities also correspond to international notations (sin, cos, tg, etc.). But this is the simplest thing. More complex operations will have to be mastered with the help of the help system or specific examples, since some formulas may look quite specific (exponential, logarithmic, tensor, matrix, etc.).

At the top, as in other office programs, there is the main panel and the main menu sections with the main operation items and quick access buttons to a particular function.

and simple operations with them

Consideration of the question is impossible without a key understanding of the types of data entered in table cells. Let us immediately note that after entering some information, you can press the enter button, the Esc key, or simply move the rectangle from the desired cell to another - the data will be saved. Editing a cell is done by double-clicking or pressing the F2 key, and upon completion of data entry, saving occurs only by pressing the Enter key.

Now a few words about what can be entered in each cell. The format menu is called up by right-clicking on the active cell. On the left there is a special column indicating the data type (general, numeric, text, percentage, date, etc.). If the general format is selected, the program, roughly speaking, itself determines what exactly the entered value looks like (for example, if you enter 01/01/16, the date January 1, 2016 will be recognized).

When entering a number, you can also use an indication of the number of decimal places (by default, one character is displayed, although when entering two, the program simply rounds the visible value, although the true value does not change).

When using, say, a text data type, whatever the user types will be displayed exactly as typed on the keyboard, without modification.

Here's what's interesting: if you hover the cursor over the selected cell, a cross will appear in the lower right corner, by pulling it while holding down the left mouse button, you can copy the data to the cells following the desired one in order. But the data will change. If we take the same date example, the next value would be January 2, and so on. This type of copying can be useful when specifying the same formula for different cells (sometimes even with cross calculations).

When it comes to formulas, for the simplest operations you can use a two-pronged approach. For example, for the sum of cells A1 and B1, which must be calculated in cell C1, you need to place the rectangle in the C1 field and specify the calculation using the formula “=A1+B1”. You can do it differently by setting the equality “=SUM(A1:B1)” (this method is more used for large gaps between cells, although you can use the automatic sum function, as well as the English version of the SUM command).

Excel program: how to work with Excel sheets

When working with sheets, you can perform many actions: add sheets, change their names, delete unnecessary ones, etc. But the most important thing is that any cells located on different sheets can be interconnected by certain formulas (especially when large amounts of information of different types are entered).

How to learn to work in Excel on your own in terms of use and calculations? It's not that simple here. As reviews from users who have mastered this spreadsheet editor show, it will be quite difficult to do this without outside help. You should at least read the help system of the program itself. The simplest way is to enter cells in the same formula by selecting them (this can be done both on one sheet and on different ones. Again, if you enter the sum of several fields, you can enter “=SUM”, and then simply select one by one while holding down the Ctrl key the necessary cells.But this is the most primitive example.

Additional features

But in the program you can not only create tables with various types of data. Based on them, in a couple of seconds you can build all kinds of graphs and diagrams by specifying either a selected range of cells for automatic construction, or specifying it manually when entering the corresponding menu.

In addition, the program has the ability to use special add-ons and executable scripts based on Visual Basic. You can insert any objects in the form of graphics, video, audio or anything else. In general, there are enough opportunities. And here only a small fraction of everything that this unique program is capable of is touched upon.

What can I say, with the right approach, it can calculate matrices, solve all kinds of equations of any complexity, find, create databases and connect them with other applications like Microsoft Access and much more - you just can’t list it all.

Bottom line

Now, it’s probably already clear that the question of how to learn to work in Excel on your own is not so easy to consider. Of course, if you master the basic principles of working in the editor, setting the simplest operations will not be difficult. User reviews indicate that you can learn this in a maximum of a week. But if you need to use more complex calculations, and even more so, work with reference to databases, no matter how much anyone wants it, you simply cannot do without special literature or courses. Moreover, it is very likely that you will even have to improve your knowledge of algebra and geometry from the school course. Without this, you can’t even dream of fully using the spreadsheet editor.

In the second part of the Excel 2010 series for beginners, you will learn how to link table cells with mathematical formulas, add rows and columns to a ready-made table, learn about the AutoFill function, and much more.

Introduction

In the first part of the “Excel 2010 for Beginners” series, we got acquainted with the very basics of Excel, learning how to create regular tables in it. Strictly speaking, this is a simple matter and, of course, the capabilities of this program are much wider.

The main advantage of spreadsheets is that individual data cells can be linked together by mathematical formulas. That is, if the value of one of the interconnected cells changes, the data of the others will be recalculated automatically.

In this part, we will figure out what benefits such opportunities can bring using the example of the table of budget expenses that we have already created, for which we will have to learn how to create simple formulas. We will also get acquainted with the cell autofill function and learn how you can insert additional rows and columns into the table, as well as merge cells in it.

Perform basic arithmetic operations

In addition to creating regular tables, Excel can be used to perform arithmetic operations in them, such as addition, subtraction, multiplication and division.

To perform calculations in any table cell, you need to create inside it the simplest formula, which must always begin with an equal sign (=). To specify mathematical operations within a formula, ordinary arithmetic operators are used:

For example, let's imagine that we need to add two numbers - “12” and “7”. Place the mouse cursor in any cell and type the following expression: “=12+7”. When you have finished entering, press the “Enter” key and the cell will display the calculation result - “19”.

To find out what a cell actually contains - a formula or a number - you need to select it and look at the formula bar - the area located immediately above the column names. In our case, it just displays the formula that we just entered.

After carrying out all the operations, pay attention to the result of dividing the numbers 12 by 7, which is not an integer (1.714286) and contains quite a lot of digits after the decimal point. In most cases, such precision is not required, and such long numbers will only clutter the table.

To fix this, select the cell with the number for which you want to change the number of decimal places after the decimal point and on the tab home in Group Number select team Decrease bit depth. Each click on this button removes one character.

To the left of the team Decrease bit depth There is a button that performs the opposite operation - it increases the number of decimal places to display more accurate values.

Drawing up formulas

Now let's return to the budget table we created in the first part of this series.

At the moment, it records monthly personal expenses for specific items. For example, you can find out how much was spent on food in February or on car maintenance in March. But the total monthly expenses are not indicated here, although these indicators are the most important for many. Let's correct this situation by adding the line “Monthly expenses” at the bottom of the table and calculate its values.

To calculate the total expense for January in cell B7, you can write the following expression: “=18250+5100+6250+2500+3300” and press Enter, after which you will see the result of the calculation. This is an example of using a simple formula, the compilation of which is no different from calculations on a calculator. Unless the equal sign is placed at the beginning of the expression, and not at the end.

Now imagine that you made a mistake when indicating the values ​​of one or more expense items. In this case, you will have to adjust not only the data in the cells indicating expenses, but also the formula for calculating total expenses. Of course, this is very inconvenient and therefore in Excel, when creating formulas, not specific numerical values ​​are often used, but cell addresses and ranges.

With this in mind, let's change our formula for calculating total monthly expenses.

In cell B7, enter an equal sign (=) and... Instead of manually entering the value of cell B2, left-click on it. After this, a dotted highlight frame will appear around the cell, which indicates that its value is included in the formula. Now enter the “+” sign and click on cell B3. Next, do the same with cells B4, B5 and B6, and then press the ENTER key, after which the same amount value will appear as in the first case.

Select cell B7 again and look at the formula bar. It can be seen that instead of numbers - cell values, the formula contains their addresses. This is a very important point, since we just built a formula not from specific numbers, but from cell values ​​that can change over time. For example, if you now change the amount of expenses for purchasing things in January, then the entire monthly total expense will be recalculated automatically. Give it a try.

Now let's assume that you need to sum not five values, as in our example, but one hundred or two hundred. As you understand, using the above method of constructing formulas in this case is very inconvenient. In this case, it is better to use the special “AutoSum” button, which allows you to calculate the sum of several cells within one column or row. In Excel, you can calculate not only the sums of columns, but also rows, so we use it to calculate, for example, total food expenses for six months.

Place the cursor on an empty cell on the side of the desired line (in our case it is H2). Then click the button Sum on the bookmark home in Group Editing. Now, let's go back to the table and see what happened.

In the cell we selected, a formula appears with an interval of cells whose values ​​need to be summed. At the same time, the dotted highlight frame appeared again. Only this time it frames not just one cell, but the entire range of cells, the sum of which needs to be calculated.

Now let's look at the formula itself. As before, the equals sign comes first, but this time it is followed by function“SUM” is a predefined formula that will add the values ​​of the specified cells. Immediately after the function there are brackets located around the addresses of the cells whose values ​​​​need to be summed, called formula argument. Please note that the formula does not indicate all the addresses of the cells being summed, but only the first and last ones. The colon between them indicates that range cells from B2 to G2.

After pressing Enter, the result will appear in the selected cell, but that’s all the button can do Sum don't end. Click on the arrow next to it and a list will open containing functions for calculating average values ​​(Average), the number of data entered (Number), maximum (Maximum) and minimum (Minimum) values.

So, in our table we calculated the total expenses for January and the total expenses on food for six months. At the same time, they did this in two different ways - first using cell addresses in the formula, and then using functions and ranges. Now, it's time to finish the calculations for the remaining cells, calculating the total costs for the remaining months and expense items.

Autocomplete

To calculate the remaining amounts, we will use one remarkable feature of Excel, which is the ability to automate the process of filling cells with systematic data.

Sometimes in Excel you have to enter similar data of the same type in a certain sequence, for example, days of the week, dates, or row numbers. Remember, in the first part of this series, in the table header, we entered the name of the month in each column separately? In fact, it was completely unnecessary to enter this entire list manually, since the application can do it for you in many cases.

Let's erase all the month names in the header of our table, except for the first one. Now select the cell labeled “January” and move the mouse pointer to its lower right corner so that it takes the form of a cross called fill marker. Hold down the left mouse button and drag it to the right.

A tooltip will appear on the screen, telling you the value the program is about to insert into the next cell. In our case, this is “February”. As you move the marker down, it will change to the names of other months, which will help you figure out where to stop. Once the button is released, the list will populate automatically.

Of course, Excel does not always correctly “understand” how to fill in subsequent cells, since the sequences can be quite diverse. Let's imagine that we need to fill a line with even numeric values: 2, 4, 6, 8 and so on. If we enter the number “2” and try to move the autofill marker to the right, it turns out that the program offers to insert the value “2” again both in the next and in other cells.

In this case, the application needs to provide a little more data. To do this, in the next cell on the right, enter the number “4”. Now select both filled cells and again move the cursor to the lower right corner of the selection area so that it takes the form of a selection marker. Moving the marker down, we see that the program has now understood our sequence and is showing the required values ​​in the tooltips.

Thus, for complex sequences, before using autofill, you need to fill in several cells yourself so that Excel can correctly determine the general algorithm for calculating their values.

Now let's apply this useful program feature to our table, so that we can enter formulas manually for the remaining cells. First, select the cell with the amount already calculated (B7).

Now “hook” the cursor on the lower right corner of the square and drag the marker to the right to cell G7. After you release the key, the application itself will copy the formula into the marked cells, while automatically changing the addresses of the cells contained in the expression, substituting the correct values.

Moreover, if the marker is moved to the right, as in our case, or down, then the cells will be filled in ascending order, and to the left or up - in descending order.

There is also a way to fill a row using tape. Let's use it to calculate the cost amounts for all expense items (column H).

We select the range that should be filled, starting from the cell with the data already entered. Then on the tab home in Group Editing press the button Fill and select the filling direction.

Add rows, columns, and merge cells

To get more practice in writing formulas, let's expand our table and at the same time learn a few basic formatting operations. For example, let’s add income items to the expenditure side, and then calculate possible budget savings.

Let's assume that the revenue part of the table will be located on top of the expenditure part. To do this we will have to insert a few extra lines. As always, this can be done in two ways: using commands on the ribbon or in the context menu, which is faster and easier.

Right-click in any cell of the second row and select the command from the menu that opens Insert…, and then in the window - Add line.

After inserting a row, pay attention to the fact that by default it is inserted above the selected row and has the format (cell background color, size settings, text color, etc.) of the row located above it.

If you need to change the default formatting, immediately after pasting, click the button Add Options icon that automatically appears near the lower right corner of the selected cell and select the option you want.

Using a similar method, you can insert columns into the table that will be placed to the left of the selected one and individual cells.

By the way, if a row or column ends up in the wrong place after insertion, you can easily delete it. Right-click on any cell belonging to the object to be deleted and select the command from the menu that opens Delete. Finally, indicate what exactly you want to delete: a row, a column, or an individual cell.

On the ribbon, you can use the button for adding operations Insert located in the group Cells on the bookmark home, and to delete, the command of the same name in the same group.

In our case, we need to insert five new rows at the top of the table immediately after the header. To do this, you can repeat the adding operation several times, or you can, having completed it once, use the “F4” key, which repeats the most recent operation.

As a result, after inserting five horizontal rows into the top part of the table, we bring it to the following form:

We left the white unformatted rows in the table on purpose to separate the income, expenditure and total parts from each other by writing appropriate headings in them. But before we do that, we will learn one more operation in Excel - merging cells.

When several adjacent cells are combined, one is formed, which can occupy several columns or rows at once. In this case, the name of the merged cell becomes the address of the uppermost cell of the merged range. At any time, you can split a merged cell again, but you cannot split a cell that has never been merged.

When merging cells, only the data in the top left is saved, but the data in all other merged cells will be deleted. Remember this and do the merging first, and only then enter the information.

Let's return to our table. In order to write headings in white lines, we need only one cell, while now they consist of eight. Let's fix this. Select all eight cells of the second row of the table and on the tab home in Group Alignment click on the button Combine and place in the center.

After executing the command, all selected cells in the row will be combined into one large cell.

Next to the merge button there is an arrow, clicking on which will bring up a menu with additional commands that allow you to: merge cells without central alignment, merge entire groups of cells horizontally and vertically, and also cancel the merge.

After adding headers, as well as filling out the lines: salary, bonuses and monthly income, our table began to look like this:

Conclusion

In conclusion, let's calculate the last line of our table, using the knowledge gained in this article, the cell values ​​of which will be calculated using the following formula. In the first month, the balance will be the normal difference between the income received for the month and the total expenses in it. But in the second month we will add the balance of the first to this difference, since we are calculating savings. Calculations for subsequent months will be carried out according to the same scheme - savings for the previous period will be added to the current monthly balance.

Now let's translate these calculations into formulas that Excel can understand. For January (cell B14) the formula is very simple and will look like this: “=B5-B12”. But for cell C14 (February), the expression can be written in two different ways: “=(B5-B12)+(C5-C12)” or “=B14+C5-C12”. In the first case, we again calculate the balance of the previous month and then add the balance of the current month to it, and in the second, the already calculated result for the previous month is included in the formula. Of course, using the second option to construct the formula in our case is much preferable. After all, if you follow the logic of the first option, then in the expression for the March calculation there will already be 6 cell addresses, in April - 8, in May - 10, and so on, and when using the second option there will always be three of them.

To fill the remaining cells from D14 to G14, we will use the ability to fill them automatically, just as we did in the case of amounts.

By the way, to check the value of the final savings for June, located in cell G14, in cell H14 you can display the difference between the total amount of monthly income (H5) and monthly expenses (H12). As you understand, they should be equal.

As can be seen from the latest calculations, in formulas you can use not only the addresses of adjacent cells, but also any others, regardless of their location in the document or belonging to a particular table. Moreover, you have the right to link cells located on different sheets of the document and even in different books, but we will talk about this in the next publication.

And here is our final table with the calculations performed:

Now, if you wish, you can continue filling it out yourself, inserting both additional items of expenses or income (rows) and adding new months (columns).

In the next article we will talk in more detail about functions, understand the concept of relative and absolute links, be sure to master several more useful elements of table editing, and much more.