Excel is necessary in cases where you need to organize, process and save a lot of information. It will help automate calculations, make them easier and more reliable. Formulas in Excel allow you to carry out arbitrarily complex calculations and get results instantly.
How to write a formula in Excel
Before learning this, you should understand a few basic principles.
- Each starts with an "=" sign.
- Values from cells and functions can participate in calculations.
- Operators are used as the mathematical signs of operations that are familiar to us.
- When you insert an entry, the default cell reflects the result of the calculation.
- You can see the design in the line above the table.
Each cell in Excel is an indivisible unit with its own identifier (address), which is denoted by a letter (column number) and a number (row number). The address is displayed in the field above the table.
So, how to create and insert a formula in Excel? Proceed according to the following algorithm:
Designation Meaning
Addition
- Subtraction
/ Division
* Multiplication
If you need to specify a number, not a cell address, enter it from the keyboard. To enter a negative sign in an Excel formula, press "-". 
How to enter and copy formulas in Excel
They are always entered after pressing "=". But what if there are many similar calculations? In this case, you can specify one, and then just copy it. To do this, enter the formula, and then "stretch" it in the right direction to multiply.
Set the pointer to the copied cell and move the mouse pointer to the lower right corner (on the square). It should take the form of a simple cross with equal sides. 
Press the left button and drag. 
Release when you want to stop copying. At this point, the calculation results will appear. 
You can also stretch to the right.
Move the pointer to the next cell. You will see the same entry, but with different addresses. 
When copying in this way, the line numbers increase if the shift is down, or the column numbers increase if to the right. This is called relative addressing.
Let's enter the value of VAT into the table and calculate the price with tax. 
The price with VAT is calculated as the price*(1+VAT). Enter the sequence in the first cell. 
Let's try to copy the record. 
The result is strange. 
Let's check the content in the second cell. 
As you can see, when copying, not only the price shifted, but also VAT. And we need this cell to remain fixed. Fix it with an absolute link. To do this, move the pointer to the first cell and click on the address B2 in the formula bar. 
Press F4. The address will be diluted with a "$" sign. This is the sign of an absolutely cell. 
Now after copying the address B2 will remain unchanged.
If you accidentally entered data in the wrong cell, just transfer it. To do this, move the mouse pointer over any border, wait until the mouse looks like a cross with arrows, press the left button and drag. In the right place, just release the manipulator.
Using Functions for Calculations
Excel offers a large number of functions that are categorized. You can view the full list by clicking on the Fx button next to the formula bar or by opening the "Formulas" section on the toolbar. 
Let's talk about some of the features.
How to Set "If" Formulas in Excel
This function allows you to set a condition and perform a calculation depending on whether it is true or false. For example, if the quantity sold is more than 4 packs, more should be purchased.
To insert the result depending on the condition, let's add one more column to the table. 
In the first cell under the heading of this column, set the pointer and click the "Logical" item on the toolbar. Let's select the "If" function. 
As with inserting any function, a window will open to fill in the arguments. 
Let's specify a condition. To do this, click on the first row and select the first cell "Sold". Next, put the sign ">" and indicate the number 4. 
In the second line we will write "Purchase". This inscription will appear for those products that have been sold out. The last line can be left blank, since we have no action if the condition is false. 
Click OK and copy the entry for the entire column. 
So that the cell does not display "FALSE", open the function again and fix it. Place the pointer on the first cell and press Fx next to the formula bar. Insert the cursor on the third line and put a space between the quotation marks. 
Then OK and copy again. 
Now we see which product should be purchased.
formula text in excel
This feature allows you to apply a format to the contents of a cell. In this case, any data type is converted to text, and therefore cannot be used for further calculations. Let's add a column to format the total. 
In the first cell, enter a function (the "Text" button in the "Formulas" section). 
In the arguments window, specify a link to the cell of the total amount and set the format to "#RUB". 
Click OK and copy. 
If we try to use this amount in calculations, we will get an error message. 
"VALUE" means that calculations cannot be made.
You can see examples of formats in the screenshot. 
Date Formula in Excel
Excel provides many options for working with dates. One of them, DATE, allows you to build a date from three numbers. This is useful if you have three different columns - day, month, year. 
Place the pointer on the first cell of the fourth column and select a function from the "Date and Time" list. 
Arrange the cell addresses accordingly and click OK. 
Copy the entry. 
AutoSum in Excel
In case you need to add a large amount of data, Excel provides the SUM function. For example, let's calculate the amount for sold goods.
Put the pointer in cell F12. It will calculate the total. 
Go to the Formulas panel and click AutoSum. 
Excel will automatically highlight the nearest numeric range. 
You can select a different range. In this example, Excel did everything right. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically. 
When inserting a range, specify the address of the first cell, a colon, and the address of the last cell. ":" means "Take all cells between the first and last. If you need to list multiple cells, separate their addresses with a semicolon:
SUM (F5;F8;F11)
Working with formulas in Excel: an example
We told you how to make a formula in Excel. This is the kind of knowledge that can be useful even in everyday life. You can manage your personal budget and control expenses. 
The screenshot shows the formulas that are entered to calculate the amounts of income and expenses, as well as the calculation of the balance at the end of the month. Add sheets to the workbook for each month if you don't want all the tables to be on the same table. To do this, simply click on the "+" at the bottom of the window. 
To rename a sheet, double-click on it and enter a name. 
The table can be made even more detailed.
Excel is a very useful program, and calculations in it give almost unlimited possibilities.
Have a great day!
To the top thematic table of contents
Thematic table of contents (Educational and methodical (what is inside the computer and how to deal with it))
previous related………………………………… next related
previous on other topics…………… next on other topics
MMA UCH(Practical) 9.1.14
Using an Excel Spreadsheet to Perform Calculations and Graphs
Copy formulas with reproduction
Let's consider a typical example. Suppose we need to draw up a bill of sale. Let's start by preparing the appearance of the sheet. Separately, we will format the first line, where there will be headers.
ATTENTION! Next - text and animated pictures with a total weight of 850K
To calculate the cost, multiply the price (per piece) by the quantity using the formula with cell references. This method is better than mental calculation or multiplication of specific numbers, not only because it allows you to get the desired result when the source data changes, but also because such a calculation can be used as a blank for other calculations.
Indeed, for other goods, the value in the "Cost" column should be obtained by multiplying the values in two adjacent columns. In Excel, as in many modern programs, it is possible not only to double a fragment by copying it through the clipboard, but also to immediately receive many copies in the area. To do this, before the paste operation from the clipboard, you need to select the area of cells in which copies will be placed.
In this case, select the second line by clicking on its number and copy it to the clipboard. Then select a few lower rows and paste from the clipboard.
Thus, when you copy the formula down, the row numbers in the references to other cells change. Similarly, when copying sideways, the column names change.
Another method of copying a cell or a set of cells (with or without reproduction) can be used if copying occurs to an adjacent area in one direction. In this case, you can not use the operations with the clipboard explicitly, but "pull" the cells by dragging the mouse with the left button pressed. In this case, towing must begin strictly from the lower right corner of the cell. With the correct position, the shape of the mouse pointer should turn into a small black cross.
When copying formulas, sometimes some cell references need to be shifted and some don't. If you fix the link to the cell, using the dollar sign when writing its name, as mentioned above, then this link will not move when copying. You can also fix only one row or column by entering one instead of two dollar signs.
Since fixing a link is a common operation in Excel, there is a way to do it quickly. To do this, while entering the formula, you can press the F4 button on the keyboard - and the current link will be fixed. When you press F4 again, the options for separate column and row fixation will be sorted out, then the fixation will be removed.
Consider several ways to drag (copy) formulas in the rows and columns of Excel.
The first way is to drag a cell in a column or row.
To stretch (spread) a formula from one cell to several cells of a column or row, you must perform a series of actions:
3. Press and hold the left mouse button.
4. Without releasing the mouse button, drag the cross in the desired direction. In the direction in which the value of the cell should be propagated.
The second way: fast drawing of the formula in one click of the mouse.
To quickly fill a column with a formula or a value from a cell, just follow these steps:
1. Write a function (formula) in a cell and click " ENTER".
2. Place the cursor on the lower right corner of the cell so that it takes the form of a thin black cross. 
3. Double click on the bottom right corner of the cell.
The formula will automatically stretch to the first empty cell or to the full one in cases where there are empty cells in the column after the cell with the multiplied function.
The third way: stretch the formula without changing (shifting) the original cells.
To pull a function without changing it, you should perform the same operations as in the first and second cases. Only before pulling it is necessary to fix the addresses of immutable cells.
Cell addresses can be fixed by adding a sign before the column address value or before the row address value. «$»
dollar. 
Now that the address is pinned, it won't change when dragging.
The fourth way: stretch the formula by copying.
Suitable for filters.
It is performed as follows:
1. Write a function (formula) in a cell and press ENTER.
2. Copy the cell value using the drop-down context menu with the right mouse button or the “ctrl”+”c” key combination. 
3. Select the desired range.
To quickly select down, just press the key combination:
"Ctrl"+"shift"+down arrow
For quick selection to the side, just press the key combination:
"Ctrl" + "shift" + arrow to the side 
- After you have selected the range, you should paste the copied formula using the drop-down context menu with the right mouse button or the keyboard shortcut "ctrl" + "v".
A formula tells Excel what to do with numbers, values in a cell or group of cells. Without formulas, spreadsheets are not needed in principle.
The construction of a formula includes: constants, operators, references, functions, range names, parentheses containing arguments, and other formulas. Using an example, we will analyze the practical application of formulas for novice users.
Excel formulas for dummies
To set a formula for a cell, you must activate it (place the cursor) and enter equals (=). You can also enter an equal sign in the formula bar. After entering the formula, press Enter. The result of the calculation will appear in the cell.
Excel uses standard mathematical operators:
The symbol "*" is used necessarily when multiplying. Omitting it, as is customary during written arithmetic calculations, is unacceptable. That is, the record (2 + 3) 5 Excel will not understand.
Excel can be used as a calculator. That is, enter numbers and operators of mathematical calculations into the formula and immediately get the result.
But more often addresses of cells are entered. That is, the user enters a cell reference, with the value of which the formula will operate.
When you change the values in the cells, the formula automatically recalculates the result.
The operator multiplied the value of cell B2 by 0.5. To enter a cell reference into a formula, just click on that cell.
In our example:
- Put the cursor in cell B3 and enter =.
- We clicked on cell B2 - Excel “designated” it (the cell name appeared in the formula, a “flickering” rectangle formed around the cell).
- We entered the * sign, the value 0.5 from the keyboard and pressed ENTER.
If several operators are used in one formula, the program will process them in the following sequence:
- %, ^;
- *, /;
- +, -.
You can change the sequence using parentheses: Excel first calculates the value of the expression in brackets.
How to designate a constant cell in an Excel formula
There are two types of cell references: relative and absolute. When copying a formula, these references behave differently: relative ones change, absolute ones remain constant.
We find the autocomplete marker in the lower right corner of the first cell of the column. Click on this point with the left mouse button, hold it and drag it down the column.
Release the mouse button - the formula will be copied into the selected cells with relative links. That is, each cell will have its own formula with its own arguments.
Sometimes you may need to apply one formula to an entire column or row in Excel, such as C1=A1*2 , C2=A2*2 , ..., Cn = An * 2 It will be quite tedious if you enter the formula into each cell one by one. There are several tricky ways to apply a single formula to quickly enter a column or row.
Exact/static copy formulas from one column to another without changing cell references in Excel
Suppose you need to apply the formula = (A1 * 3 + 8) / 5 in column C and see the following tutorials to apply the same formula to the entire column C.
Apply formula to entire column or row with dragging AutoFill handle
Dragging the AutoFill handle is the most common way to apply the same formula to an entire column or row in Excel.
First type the formula = (A1 * 3 + 8) / 5 in cell C1, and then drag the autofill handle down to column C.
Then the formula = (A1 * 3 + 8) / 5 applies to the entire column C. If you need to apply it to the entire row, you can drag the autocomplete handle on the far right.
Attention: This AutoFill drag method automatically requires formula calculation. You can turn it on by clicking Formula s> Calculation parameters> automatically, See screenshot below:
Apply formula to entire column or row without dragging keyboard shortcuts
Sometimes the column you want to apply the formula to may contain hundreds of rows, and dragging the fill handle can be awkward. In fact, you use the keyboard shortcut to easily archive it in Excel.
First, select the whole column C, second, enter the formula = (A1 * 3 + 8) / 5, and then click ctrl + enter keys together.
If you want to apply a formula to an entire row, you can select the entire row first.
Apply a formula to an entire column or row without dragging with the Fill function
Actually there is Fill command on the Excel ribbon to help you quickly apply a formula to an entire column or row.
First enter the formula = (A1 * 3 + 8) / 5 to cell C1 (the first cell of the column where you will enter the same formula), second, select the entire column C, and then press home > Fill > down
If you want to apply a formula to a whole row, just enter the formula in the first cell of the entire row, then select the entire row and click the button Home> Fill in > Right.
Apply formula to entire column or row without drag and drop with Kutools for Excel
All of the methods above are to apply formulas to an empty column or row. Sometimes you may want to apply the same formula to an entire column or row of data. How to process? You can try Kutools for Excel Operating instruments.
1. Select the column or row you will be working with (in this case select column A) and click Kutools > More > operation, See left screenshot.
2. in Operating instruments in the dialog select To order in operation enter, enter (* 3 + 8) / 5 in the empty field To order and press OK button. See screenshot:
And then the whole column is filled with the formula = (? * 3 + 8) / 5, huh? refers to the value in the corresponding cell. See screenshots below:
Notes:
(1) If you checked Create formulas option, the results will be in the form of formulas for each cell.
(2) If Skip formula cells the option is checked, the operation skips and automatically ignores the formula cells in the selected range.
