How to change an Excel spreadsheet. How to work in Excel

• “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.

If you have read the previous ones, you should be aware that the formula begins with the “Equals” sign. When it becomes necessary to write this sign in a cell without a formula, the program persistently continues to consider such an entry as the beginning of the formula. When you click on another cell, the cell address is written after the sign. In this case, there are several ways to outsmart Excel.

Solution:
Place a space or an apostrophe before writing an equal sign, plus (addition), minus (subtraction), slash (division) or asterisk (multiplication).

Why doesn't the formula calculate in Excel?

If you have to work on different computers, you may have to face the fact that the Excel files needed for your work do not calculate using formulas.

Incorrect cell format or incorrect cell range settings

In Excel, various hashtag (#) errors occur, such as #VALUE!, #REF!, #NUMBER!, #N/A, #DIV/0!, #NAME? and #EMPTY!. They indicate that something in the formula is not working correctly. There may be several reasons.

Instead of the result it is given #VALUE!(in version 2010) or displays the formula in text format (in version 2016).

In this example, you can see that the contents of cells with different data types are multiplied =C4*D4.

Bug fix: indicating the correct address =C4*E4 and copying the formula for the entire range.

• Error #LINK! Occurs when a formula references cells that have been deleted or replaced with other data.
• Error #NUMBER! Occurs when a formula or function contains an invalid numeric value.
• Error #N/A usually means that the formula does not find the requested value.
• Error #DIV/0! occurs when a number is divisible by zero (0).
• Error #NAME? occurs due to a typo in the formula name, that is, the formula contains a reference to a name that is not defined in Excel.
• Error #EMPTY! Occurs when an intersection is specified between two areas that do not actually intersect, or an incorrect separator is used between references when specifying a range.

Note:#### does not indicate a formula error, but rather that the column is not wide enough to display the contents of the cells. Simply drag the column border to expand it, or use the option Home - Format - Automatic column width selection.

Errors in formulas

Green triangles in the corner of a cell may indicate an error: numbers are written as text. Numbers stored as text may produce unexpected results.

Correction: Select a cell or range of cells. Click the "Error" sign (see picture) and select the desired action.

Formula display mode enabled

Since in normal mode the calculated values ​​are displayed in the cells, in order to directly see the calculated formulas in Excel there is a mode for displaying all formulas on the sheet. You can enable or disable this mode using the command Show formulas from tab Formulas In chapter Formula dependencies.

Automatic calculation using formulas is disabled

This is possible in files with a large amount of calculations. To prevent a weak computer from slowing down, the author of the file can disable automatic calculation in the file properties.

Correction: after changing the data, press the F9 button to update the results or enable automatic calculation. File – Options – Formulas – Calculation options – Calculations in the workbook: automatically.

Addition formula in Excel

Adding in spreadsheets is fairly easy. You need to write a formula that lists all the cells containing the data to be added. Of course, we put a plus between the cell addresses. For example, =C6+C7+C8+C9+C10+C11.

But if there are too many cells, then it is better to use the built-in function Autosum. To do this, click the cell in which the result will be displayed, and then click the button Autosum on the tab Formulas(highlighted with a red frame).

The range of cells to be summed will be highlighted. If the range was selected incorrectly, for example, vertical cells are selected, but horizontal ones are needed, then select a new range. To do this, left-click on the outermost cell of the new range and, without releasing the button, drag the pointer through all cells of the range to the final one. Complete the formula by pressing the key Enter on keyboard.

Formula for rounding to a whole number in Excel

Beginner users use formatting that some try to round a number with. However, this does not affect the contents of the cell in any way, as indicated in the tooltip. When you click on the button (see figure), the format of the number will change, that is, its visible part will change, but the contents of the cell will remain unchanged. This can be seen in the formula bar.

To round a number according to mathematical rules, you must use the built-in function =ROUND(number,number_of_digits).

You can write it manually or use the function wizard on the tab Formulas in Group Mathematical(see picture).

This function can round not only the fractional part of a number, but also whole numbers to the desired digit. To do this, when writing the formula, indicate the number of digits with a minus sign.

How to calculate percentages of a number

To calculate percentages in a spreadsheet, select the cell to enter the calculation formula. Place the equal sign, then write the address of the cell (use the English layout) that contains the number from which you will calculate the percentage. You can simply click on this cell and the address will be inserted automatically. Next, put the multiplication sign and enter the number of percentages that need to be calculated. Look at an example of calculating a discount when purchasing a product.
Formula =C4*(1-D4)

IN C4 the price of the vacuum cleaner is written down, and in D4– discount in %. It is necessary to calculate the cost of the product minus the discount; for this, our formula uses the construction (1-D4). Here the percentage value by which the price of the product is multiplied is calculated. For Excel, a record of the form 15% means the number 0.15, so it is subtracted from one. As a result, we get a residual value of the goods of 85% of the original.

In this simple way, using spreadsheets, you can quickly calculate percentages of any number.

Excel formula cheat sheet

The cheat sheet is made in the form of a PDF file. It includes the most popular formulas from the following categories: mathematical, text, logical, statistical. To get the cheat sheet, click the link below.

PS: Interesting facts about the real cost of popular products

Dear reader! You have watched the article to the end.
Have you received an answer to your question? Write a few words in the comments.
If you haven't found the answer, indicate what you were looking for.

The most interesting:

Label each polygon and measure the lengths of the sides of each

Connecting YouTube to TV

Names of large numbers Solving a compound problem