Entering in Excel. Complete information about formulas in Excel. Sum formula in Excel

Begin any formula with an equal sign (=). The equal sign tells Excel that the set of characters you enter into a cell is a mathematical formula. If you forget the equal sign, Excel will treat your input as a set of characters.

Use coordinate references to the cells that contain the values ​​used in the formula. Although you can enter numeric constants in your formulas, in most cases you will need to use the values ​​in other cells (or the results of other formulas displayed in those cells) in the formulas. You access those cells using the row and column coordinate reference where the cell is located. There are several formats:

• The most common coordinate reference is to use a letter or letters representing the column followed by the row number the cell is in: for example, A1 points to the cell in column A and row 1. If you add rows above the cell, the cell reference changes to display its new position; adding a row above cell A1 and a column to the left of it will change its reference to B2 in all formulas that use it.
• A variation of this formula is to make row or column references absolute by adding a dollar sign ($) in front of them. Although the reference to cell A1 will change if a row is added above it or a column to the left of it, the reference $A$1 will always point to the top left cell on the worksheet; Thus, in a formula, cell $A$1 may have a different or even invalid value in the formula if rows or columns are inserted into the worksheet. (If desired, you can use an absolute reference for the column or row separately, for example $A1 or A$1).
• Another way to reference a cell is by the numeric method, in the format RxCy, where "R" indicates "row," "C" indicates "column," and "x" and "y" are the row and column numbers, respectively. For example, reference R5C4 in this format points to the same location as reference $D$5. A reference of type RxCy points to a cell relative to the upper left corner of the sheet, that is, if you insert a row above the cell or a column to the left of the cell, the reference to it will change.
• If you use only an equal sign and a single cell reference in a formula, then you are essentially copying the value from another cell to a new cell. For example, entering "=A2" in cell B3 will copy the value entered in cell A2 to cell B3. To copy a value from a cell on another sheet, add the sheet name followed by an exclamation point (!). Entering "=Sheet1!B6" in Cell F7 on Sheet2 will display the value of cell B6 on Sheet1 in Cell F7 on Sheet2.

1. Use arithmetic operators for basic operations. Microsoft Excel can perform all basic arithmetic operations: addition, subtraction, multiplication and division, and exponentiation. Some operations require different characters than those we use when writing them by hand. The list of operators is given below, in order of precedence (that is, the order in which Excel processes arithmetic operations):

   • Negation: Minus sign (-). This operation returns the opposite sign of a number or cell reference (this is equivalent to multiplying by -1). This operator must be placed before the number.
   • Percent: Percent sign (%). This operation will return the decimal equivalent of a percentage of a numeric constant. This operator must be placed after the number.
   • Exponentiation: Caret (^). This operation raises the number (or reference value) before the caret to a power equal to the number (or reference value) after the caret. For example, "=3^2" is 9.
   • Multiplication: Asterisk (*). The asterisk is used for multiplication so that multiplication is not confused with the letter "x."
   • Division: Slash (/). Multiplication and division have the same priority and are performed from left to right.
   • Addition: Plus sign (+).
   • Subtraction: Minus sign (-). Addition and subtraction have the same priority and are performed from left to right.

2. Use comparison operators to compare values ​​in cells. Most often, you will use comparison operators with the IF function. You put a cell reference, a numeric constant, or a function that returns a numeric value on either side of the comparison operator. The comparison operators are listed below:

   • Equal: The equal sign (=).
   • Not equal (<>).
   • Less (<).
   • Less or equal (<=).
   • More (>).
   • Greater than or equal to (>=).

3. Use ampersand (&) to connect text strings. Combining text strings into one is called concatenation, and the ampersand is the operator that does concatenation in Excel. You can use the ampersand with strings or string references; for example, entering "=A1&B2" in cell C3 will display "AUTO FACTORY" if "AUTO" is entered in cell A1 and "FACTORY" is entered in cell B2.

4. Use reference operators when working with a region of cells. You'll most often use a cell region with Excel functions such as SUM, which finds the sum of a cell region's values. Excel uses 3 reference operators:

   • Area operator: colon (:). The region operator refers to all cells in a region that begins with the cell before the colon and ends with the cell after the colon. Typically, all cells are in the same row or column; "=SUM(B6:B12)" will display the result of adding the values ​​of cells B6, B7, B8, B9, B10, B11, B12, while "=AVERAGE(B6:F6)" will display the arithmetic average of the values ​​of cells B6 to F6 .
   • Concatenation operator: comma (,). The union operator includes all cells or regions of cells before and after it; "=SUM(B6:B12, C6:C12)" sums the values ​​of cells B6 to B12 and C6 to C12.
   • Intersection operator: space(). The intersection operator looks for cells that are common to 2 or more regions; for example, "=B5:D5 C4:C6" is only the value of cell C5, since it appears in both the first and second regions.

5. Use parentheses to specify function arguments and override the order in which operators are evaluated. Parentheses in Excel are used in two cases: to define function arguments and to indicate a different calculation order.

   • Functions are predefined formulas. Such as SIN, COS or TAN require one argument, while IF, SUM or AVERAGE can take many arguments. Arguments within a function are separated by a comma, for example, "=IF (A4 >=0, "POSITIVE," "NEGATIVE")" for the IF function. Functions can be nested within other functions, up to 64 levels.
   • In formulas with mathematical operations, operations inside parentheses are performed before those outside them; for example, in "=A4+B4*C4," B4 is multiplied by C4 and the result is added to A4, and in "=(A4+B4)*C4," A4 and B4 are first added, and then the result is multiplied by C4. Brackets in operations can be nested within each other; the operation inside the innermost pair of brackets will be executed first.
   • It doesn't matter whether nested parentheses occur in mathematical operations or in nested parentheses, always make sure that the number of opening parentheses equals the number of closing ones, otherwise you will receive an error message.

You must enter the formula starting with the equal sign. This is necessary so that Excel understands that it is a formula and not data that is being entered into the cell.

Select an arbitrary cell, for example A1. In the formula bar, enter =2+3 and press Enter. The result (5) appears in the cell. And the formula itself will remain in the formula bar.

Experiment with different arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/). To use them correctly, you need to clearly understand their priority.

1. The expressions inside the parentheses are executed first.
2. Multiplication and division have higher priority than addition and subtraction.
3. Operators with the same precedence are executed from left to right.

My advice to you is to USE BRACKETS. In this case, you will protect yourself from accidental errors in calculations, on the one hand, and on the other hand, brackets make reading and analyzing formulas much easier. If the number of closing and opening parentheses in a formula does not match, Excel will display an error message and offer an option to correct it. Immediately after you enter a closing parenthesis, Excel displays the last pair of parentheses in bold (or a different color), which is very useful if you have a lot of parentheses in your formula.

Now let's try to work with using references to other cells in formulas.

Enter the number 10 in cell A1, and the number 15 in cell A2. In cell A3, enter the formula =A1+A2. In cell A3 the sum of cells A1 and A2 will appear - 25. Change the values ​​of cells A1 and A2 (but not A3!). After changing the values ​​in cells A1 and A2, the value of cell A3 is automatically recalculated (according to the formula).

To avoid mistakes when entering cell addresses, you can use the mouse when entering links. In our case, we need to do the following:

1. Select cell A3 and enter an equal sign in the formula bar.
2. Click cell A1 and enter the plus sign.
3. Click cell A2 and press Enter.

The result will be similar.

Relative, absolute and mixed references

To better understand the differences between links, let's experiment.

• A1 - 20 B1 - 200
• A2 - 30 B2 - 300

In cell A3, enter the formula =A1+A2 and press Enter.

Now place the cursor on the lower right corner of cell A3, press the right mouse button and drag over cell B3 and release the mouse button. A context menu will appear in which you need to select “Copy Cells”.

After this, the formula value from cell A3 will be copied to cell B3. Activate cell B3 and see what formula you get - B1+B2. Why did this happen? When we wrote the formula A1+A2 in cell A3, Excel interpreted this entry as follows: “Take the values ​​​​from the cell located two rows higher in the current column and add the value of the cell located one row higher in the current column.” Those. by copying the formula from cell A3, for example, to cell C43, we get - C41 + C42. This is the beauty of relative links; the formula itself seems to adapt to our tasks.

Enter the following values ​​in the cells:

• A1 - 20 B1 - 200
• A2 - 30 B2 - 300
• Enter the number 5 in cell C1.

In cell A3, enter the following formula =A1+A2+$C$1. Similarly, copy the formula from A3 to B3. Look what happened. Relative links “adjusted” to the new values, but the absolute link remained unchanged.

Now try experimenting with mixed links yourself and see how they work. You can reference other sheets in the same workbook in the same way that you can reference cells in the current sheet. You can even refer to sheets from other books. In this case, the link will be called an external link.

For example, to write a link to cell A5 (Sheet2) in cell A1 (Sheet 1), you need to do the following:

• select cell A1 and enter an equal sign;
• click on the "Sheet 2" label;
• Click on cell A5 and press enter;
• After this, Sheet 1 will be activated again and the following formula will appear in cell A1 = Sheet2! A5.

Editing formulas is similar to editing text values ​​in cells. Those. you need to activate the cell with the formula by highlighting or double-clicking the mouse, and then edit it using, if necessary, the Del and Backspace keys. Changes are committed by pressing the Enter key.

Using text in formulas

You can perform mathematical operations on text values ​​if the text values ​​contain only the following characters:

Numbers from 0 to 9, + - e E /

You can also use five numeric formatting characters:

$%() space

The text must be enclosed in double quotes.

Wrong: =$55+$33

Right: ="$55"+$"33"

When performing calculations, Excel converts numeric text into numeric values, so the result of the above formula is 88.

To combine text values, use the text operator & (ampersand). For example, if cell A1 contains the text value “Ivan”, and cell A2 contains the text value “Petrov”, then entering the following formula in cell A3 =A1&A2, we get "IvanPetrov".

To insert a space between the first and last name, write this: =A1&" "&A2.

The ampersand can be used to combine cells with different data types. So, if in cell A1 there is the number 10, and in cell A2 there is the text “bags”, then as a result of the formula =A1&A2, we will get "10 bags". Moreover, the result of such a union will be a text value.

How to create a formula in Excel, step by step steps.

Do you want to know how to consistently earn money online from 500 rubles a day?
Download my free book
=>>

A program like Excel is designed to create various tables. In particular, it is possible to perform computational operations using special formulas.

In this case, formulas used in Excel may contain addition, subtraction, multiplication or division. Each action has its own equation operators.

Thus, the “*” sign is used for multiplication, “/” for division, “-” for subtraction, “+” for addition, and the special insertion sign that could be raised to a power is indicated as “^”.

However, the most important point when using formulas in Excel is that any formula must begin with an “=” sign.
By the way, to make calculations easier, you can use cell addresses, providing the values ​​​​available in them.

How to create a formula in Excel, instructions

In this article you can take a closer look at creating formulas in Excel.

Create a simple formula

To create a simple formula, first put an equal sign and then write down its values. For example, you have the value 14 in column “A” in the first line, in column “B” in the same line there is “38” and you need to calculate their sum in column “E” also in the first line.

To do this, put an equal sign in the last column and indicate A1+B1, then press the “Enter” button on the keyboard and get the answer. That is, first in E1 the entry “=A1+B1” is obtained. As soon as you press “Enter” the amount appears equal to 52.

By the way, if you click on the final value, the line called f(x) located above the table will display the formula used for the calculation.

It is here that, if necessary, it is best to edit formulas. If you do not need to make changes, on the left side of f(x), click on the cross.

If the cell displays “#######”, there is no need to be alarmed. This means that there is simply not enough space in the column to display the full contents of the cell. To fix this, increase its width.

Using cell references

The simplest option for creating formulas is to work with cell references. How to work with this? Very simple.

So, first put an equal sign in the place where the final value should be, and then click on the cells one by one with the indicated numerical values ​​and, placing signs between them, mark everything necessary.

Finally, press “Enter”.

For example, let’s say you have 120 in B2, 900 in B3, and the result of the subtraction in B4. Thus, when using cell references, you first put the “=” sign on B4, then click on B2, the cell should light up in a new color.

Then put the “*” sign and click on B3. That is, in B4 you will get the formula: = B2*B3. After pressing “Enter” you will get the result.

By the way, when you enter any value in cell B2 or B3, the result written in B4 will be automatically changed.

Copy the formula

If you have several columns in your table that use the same formula, to simplify the task, calculations can be made in only one, and then copy it and apply it to the rest of the table or column.

For example, you have a product, you know its quantity and unit price, all you need to do is calculate the cost. To do this, in the cell with the cost, put an equal sign and the quantity from the first cell where the value is indicated, multiply by the price per unit of goods.

If this formula is applicable for the entire table, then to copy it, point at the lower right corner and you should see a thin black plus instead of a white plus.

It needs to be moved down the cost column. Thus, a complete automatic calculation of the cost of the goods will occur.
That is, a tool like the thin black plus icon allows you to copy data from a cell and apply it throughout the column.

Which is also much more convenient than counting the values ​​​​for each line in turn, performing the same action.

Formulas

In general, if the question arises of how to create a formula in Excel, then, as you can see, it is not difficult to do. But to obtain the required result, you need exact knowledge of the formula that should be used to solve the problem or carry out calculations.

And there are quite a lot of them and therefore it is difficult to know or remember everything. The developers took care of this issue and, to simplify working with Excel, they collected all the formulas that can be used in this program in one section.

If you look at the very top line, where an entry such as “Home” is indicated, moving a little to the right, you can find the “Formula” column.

It is here that, if necessary, you can choose one or another formula to carry out calculations. That is, in the table, put an equal sign in the desired row, find the formula you need, for example, the amount.

You will get the result one line at a time. Pull the corner with the sum result down and the formula will automatically be applied to all filled columns.

How to create a formula in Excel, summary

As you can see, creating formulas in Excel is convenient and fast. The program itself will calculate and analyze everything. Naturally, it is difficult to fit and describe all the functionality of this program in one article. But the developers tried to do everything possible for your convenience.

When a question arises, click on the line “What do you want to do?” on the top panel. and enter into the search the action you want to perform. The program will provide you with help on this section. So, learn Excel and use this program and all its functionality to the fullest. Good luck and success to you.

Good to know:

P.S. I am attaching a screenshot of my earnings in affiliate programs. And I remind you that anyone can earn money this way, even a beginner! The main thing is to do it correctly, which means learning from those who are already making money, that is, from Internet business professionals.

Get a list of proven Affiliate Programs in 2018 that pay money!


Download the checklist and valuable bonuses for free
=>> “The best affiliate programs of 2018”

By creating mathematical formulas in Excel, we indicate what actions need to be done and where the result should be displayed. Using formulas in Excel, you can perform various calculations such as addition, subtraction, multiplication, division, as well as complex combined calculations. Compiling formulas in Excel is done manually and consists of the addresses of specific cells or a series of cells or a column of cells.

Have some secrets of Excel formulas

You can write formulas in Excel directly in a cell or in the formula bar, and you can also write a formula in Excel directly in a cell using functions. You can also calculate dates, times, and percentages by entering formulas in Excel. In Excel, columns are designated by letters such as A, B. C, and so on, and rows in Excel are numbered by numbers 1, 2, 3, and so on. When a column and a row intersect, you get the cell address in Excel, which is indicated by the letter of the column and the number of the row, for example, the very first cell will be designated A1.

To work in Excel you need to know the cell names

Any formula in Excel for calculation begins with an equal sign =. The number of characters in an Excel cell, the formula can contain no more than 1024. When doing some calculation in Excel, the result will be displayed in the final cell and if you hover over this cell with the result and press the left mouse button, you will see in the formula bar the resulting formula used for the calculation in this cell.

Addition and subtraction

If you need to add cells in Excel, you need to move the cursor over an empty cell in which the result should be displayed and press the left mouse button. Then press the = key on your keyboard. Then move the cursor over the first cell that we will sum with others, press the left mouse button. On your keyboard, press the + key. Place the cursor over the second cell that you will add to the others and press the left mouse button. If you still need to add numbers from some cells in Excel, then add them one by one to the formula by clicking on +, and then on the cell that needs to be added. When all the cells in the Excel formula are fixed, you need to press the Enter key on the keyboard and the result will immediately be displayed in the cell.

It is very easy to create an addition formula in Excel.

In exactly the same way, a formula is created in Excel for subtracting numbers. Just to make a subtraction in Excel, you need to press minus - on the keyboard instead of +. With this method, it does not matter in which column and in which row the cells to be added and subtracted are located.

An example of how to calculate the amount in Excel in a column

If you need to calculate the sum of a column or row in Excel, you need to move the cursor over the first cell, press the left button on the mouse and, without releasing the button, move the cursor to the last cell. Then release the mouse button and click the AutoSum button located on the Excel toolbar.

Multiplication

The multiplication formula in Excel is compiled in the following way. For example, you need to multiply cells A1 and A2 in Excel, and have the result displayed in cell A3.

It's easy to make a multiplication formula in Excel

First you need to hover over cell A3 and click the left mouse button. Press the = key on your keyboard. Place the cursor over cell A1 and press the left mouse button. Then on the keyboard, press the multiplication sign in Excel *, which is located on the left side of the keyboard or on the number 8. To display the multiplication sign in Excel *, located on the number 8, you must first press the Shift button on the keyboard and, while holding it, click on the * located on the number 8. Then hover the cursor over cell A2 and press the left mouse button. Finally, press the Enter key on your keyboard and the result will immediately be displayed.

Division

The division formula in Excel is composed as follows. For example, you need to split cell A1 into cell A2 in Excel and have the result appear in cell A3.

Easy to enter division formula in Excel

First you need to hover over cell A3 and click the left mouse button. Press the = key on your keyboard. Place the cursor over cell A1 and press the left mouse button. Then on your keyboard, press the division sign in Excel /, which is located on the left side of the keyboard or on the key with a question mark. To put a division sign in Excel that is located on a key with a question mark, you must first switch the keyboard to the English layout and only then press this key. Then move the cursor over cell A2 and press the left mouse button. Finally, press the Enter key on your keyboard and get the result of the division.

Complex formulas

Writing complex formulas in Excel needs to be exactly the same as in mathematics. For example, you have cells filled with data and you need the result to be displayed in cell A4 based on the formula =A1+A2/(A3-A2)*A1.

You can write formulas in Excel or insert ready-made ones

First of all, copy this formula to the clipboard so that you can then paste the formula into Excel. Place the cursor over cell A4 and press the left mouse button. Hover your cursor over the formula bar in Excel and press the left mouse button. Then you need to paste the formula from the clipboard into this line in Excel, and to do this, press the Ctrl key on the keyboard and while holding it, press the V key. The formula will be written in the formula bar, and pressing the Enter key will immediately display the result of the calculation using this formula.
To delete a formula from a cell in Excel, just hover the cursor over the cell, click the left mouse button and delete the formula in the formula bar.

Excel can be used to calculate numerical information. In this lesson, you'll learn how to create simple formulas in Excel to add, subtract, multiply, and divide quantities in a workbook. You'll also learn different ways to use cell references to make working with formulas easier and more efficient.

Simple formulas

Formula is an equality that does the math. As a calculator, Excel can calculate formulas involving addition, subtraction, multiplication, and division.

One of the most useful features of Excel is the ability to perform calculations using the address of a cell to represent the value in it. This method is called using cell references. To get the most out of Excel, it's important to understand how to create simple formulas and use cell references.

Creating simple formulas

Excel uses standard operators for equations, such as the plus sign for addition (+), the minus sign for subtraction (-), the asterisk for multiplication (*), the slash for division (/), and the caret sign (^) for raising. degree. A key point to remember when creating formulas in Excel is that all formulas must begin with an equal sign (=). This happens because the cell contains or is equal to the formula and its value.

To create a simple formula in Excel:

The formula result may not fit in the cell, and symbols (#######) may appear instead of the value. This means that the column is not wide enough to display the contents of the cell. Simply increase the column width to display the cell's contents.

Creating formulas with cell references

When a formula contains a cell address, it is called a cell reference. Creating formulas with cell references is a very useful thing. This gives you the ability to change the data in the worksheet without having to rewrite the values ​​in the formulas.

To create a formula with cell references:

If you change the value in cell B1 or B2, the total will be automatically recalculated.