How to multiply numbers in Excel. Formulas in Excel

Sometimes you may need to apply one formula to an entire column or row in Excel, for example 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 complex ways to apply one formula to quick input column or row.

Exact/static copying of 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.

Dragging an 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 and drop method automatically requires formula calculation. You can enable it by clicking Formula s> Calculation parameters> automatically. See screenshot below:

Apply a formula to an entire column or row without dragging with keyboard shortcuts

Sometimes the column you want to apply the formula may contain hundreds of rows, and dragging the fill handle can be awkward. In fact, you are using the keyboard. quick access to easily archive it in Excel.

First, select the entire 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 using the Fill function

Actually exists 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 into cell C1 (the first cell of the column where you will enter the same formula), secondly, select the entire column C and then click home > Fill > down

If you want to apply a formula to an entire row, simply enter the formula in the first cell of the entire row, then select the entire row and click the button Home> Fill > Right.

Apply formula to entire column or row without dragging with Kutools for Excel

All the above methods are to apply formulas to an empty column or row. Sometimes you may need to apply the same formula to an entire column or row of data. How to process? You can try Kutools for Excel Operating tools.

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 tools in the dialog box select To order in operation enter, enter (* 3 + 8) / 5 in an empty field in To order and press OK button. See screenshot:

And then the entire column is filled with the formula = (? * 3 + 8) / 5, eh? refers to the value in the corresponding cell. See screenshots below:

Notes:

(1) If you have checked Creating 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 formula cells in the selected range.

Excel is essential when you need to organize, process, and save a lot of information. It will help automate calculations, making 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, there are a few basic principles to understand.

1. Each one begins with an “=” sign.
2. Cell values ​​and functions can participate in calculations.
3. Operators are used as familiar mathematical symbols for operations.
4. When you insert a record, the cell displays the result of the calculation by default.
5. You can view the design in the line above the table.

Each cell in Excel is an indivisible unit with its own identifier (address), which is indicated by a letter (column number) and a number (row number). The address is displayed in the field above the table.

So how do you create and paste a formula in Excel? Proceed according to the following algorithm:

Designation Meaning

Addition
- Subtraction
/ Division
* Multiplication

If you need to specify a number rather than a cell address, enter it from the keyboard. To specify a negative sign in an Excel formula, click "-".

How to enter and copy formulas in Excel

They are always entered after clicking on “=”. But what to do if there are many similar calculations? In this case, you can specify one and then simply copy it. To do this, enter the formula and then “stretch” it in the desired direction to multiply it.
Place the pointer on the cell to be copied and move the mouse pointer to the lower right corner (over the square). It should take the form of a simple cross with equal sides.


Press the left button and drag.


Release when you need to stop copying. At this moment, the calculation results will appear.


You can also stretch it to the right.
Move the pointer to an adjacent cell. You will see the same entry, but with different addresses.


When copying in this way, the row numbers increase if the shift occurs downward, or the column numbers increase if it is shifted to the right. This is called relative addressing.
Let's enter the VAT value into the table and calculate the price including tax.


The price including VAT is calculated as price*(1+VAT). Let's enter the sequence into the first cell.


Let's try to copy the entry.


The result was strange.


Let's check the contents in the second cell.


As you can see, when copying, not only the price, but also the VAT shifted. And we need this cell to remain fixed. Let's anchor it with an absolute link. To do this, move the pointer to the first cell and click on address B2 in the formula bar.


Press F4. The address will be appended with a “$” sign. This is the sign of an absolute cell.


Now after copying the B2 address will remain unchanged.
If you accidentally entered data into the wrong cell, simply move 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. At the desired location, simply release the manipulator.

Using functions for calculations

Excel offers a large number of functions, which are divided into categories. You can view the full list by clicking on the Fx button next to the formula line or opening the “Formulas” section on the toolbar.


Let's talk about some functions.

How to Set "If" Formulas in Excel

This function allows you to set a condition and perform a calculation depending on its truth or falsity. For example, if the quantity of goods sold is more than 4 packs, you should purchase more.
To insert the result depending on the condition, we will add another column to the table.


In the first cell under the heading of this column, set the pointer and click the “Boolean” item on the toolbar. Let's select the "If" function.


As with inserting any function, a window will open for you to fill in the arguments.


Let's indicate the 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 message will appear for those items that have been sold out. Last line can be left empty since we have no action if the condition is false.


Click OK and copy the entry for the entire column.


To prevent “FALSE” from being displayed in the cell, let’s open the function again and correct 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 quotes.


Then OK and copy again.


Now we see which product should be purchased.

Formula text in Excel

This function allows you to apply a format to the contents of a cell. In this case, any type of data is converted to text, which means it 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, we will indicate a link to the total amount cell and set the format to “#rub.”


Click OK and copy.


If we try to use this amount in calculations, we will receive an error message.

"VALUE" indicates that the calculation cannot be performed.
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 construct a date from three numbers. This is convenient 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 up a large number of data, Excel provides the SUM function. For example, let's calculate the amount for goods sold.
Place the pointer in cell F12. It will calculate the total.


Go to the Formulas panel and click AutoSum.


Excel will automatically highlight the closest number range.


You can select a different range. IN 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, the first cell address, a colon, and the last cell address are specified. ":" 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 in Excel with formulas: example

We told you how to make a formula in Excel. This is 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 sheets on one. To do this, simply click on the “+” at the bottom of the window.

To rename a sheet, double-click it and enter a name.

The table can be made even more detailed.
Excel is very useful program, and calculations in it provide almost unlimited possibilities.

Have a great day!

The formula prescribes Excel program the order of actions with numbers, values ​​in a cell or group of cells. Without formulas, spreadsheets are not needed in principle.

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

Formulas in Excel for dummies

To set a formula for a cell, you need to 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 required when multiplying. It is unacceptable to omit it, as is customary during written arithmetic calculations. That is, Excel will not understand the entry (2+3)5.

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

But more often cell addresses are entered. That is, the user enters a link to the cell whose value the formula will operate on.

When values ​​in cells change, 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:

1. Place the cursor in cell B3 and enter =.
2. We clicked on cell B2 - Excel “labeled” it (the cell name appeared in the formula, a “flickering” rectangle formed around the cell).
3. Enter the sign *, the value 0.5 from the keyboard and press 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 parentheses.

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 links behave differently: relative ones change, absolute ones remain constant.

Find the autofill 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 to the selected cells with relative links. That is, each cell will have its own formula with its own arguments.

Let's look at several ways to stretch (copy) formulas in the rows and columns of Excel.

The first method is to drag a cell in a column or row.

To stretch (distribute) a formula from one cell to several cells in a column or row, you must perform a number 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 cell value should be propagated.

The second method: quickly drag out the formula in one mouse click.

To quickly fill a column with a formula or value from a cell, just follow these steps:
1. Write a function (formula) into a cell and click “ ENTER".
2. Place the cursor over the lower right corner of the cell so that it takes the shape of a thin black cross.
3. Double-click on the lower right corner of the cell.

The formula will automatically extend 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 multiplying function.

The third way: stretch the formula without changing (shifting) the original cells.

To extend a function without changing it, you should perform the same operations as in the first and second cases. Just before pulling, you need to fix the addresses of immutable cells.
You can fix cell addresses by adding a sign before the column address value or before the row address value «$» dollar.

Now that the address is pinned, it will not change when pulled.

Fourth method: extend the formula by copying.

Suitable for working under filters.
This is done as follows:
1. Write a function (formula) into a cell and press ENTER.
2. Copy a cell value using the drop-down context menu with the right mouse button or the key combination “ctrl” + “c”.

3. Select the desired range.
To quickly select down, just press the key combination:
“Ctrl” + “shift” + down arrow
To quickly select to the side, just press the key combination:
“Ctrl”+”shift”+ arrow to the side

1. After selecting the range, you should paste the copied formula using the drop-down context menu with the right mouse button or the key combination “ctrl” + “v”.

To the top thematic table of contents
Thematic table of contents (Educational and methodological (what is inside the computer and how to deal with it))
previous on topic………………………………… next on topic
previous on other topics………… next on other topics

MMA UC (Practical) 9.1.14

Use of electronic Excel tables for carrying out calculations and drawing graphs
Copying formulas with reproduction

Let's look at a typical example. Suppose we need to draw up a purchase order. Let's start with preparation appearance leaf. Let's separately format the first line, where the headings will be.
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 a 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 changing the initial data, but also because such a calculation can be used as a preparation 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 via the clipboard, but also to immediately receive many copies in the area. To do this, before pasting from the clipboard, you need to select the area of ​​the cells in which the copies will be placed.
In this case, select the second line by clicking on its number and copy it to the clipboard. Then select several lower lines and paste from the clipboard.

Thus, when you copy a formula down in references to other cells, the row numbers change. Similarly, when copying sideways, the column names change.
Another method of copying a cell or set of cells (with or without multiplication) can be used if the copying occurs in an adjacent area in one direction. In this case, you can not use operations with the clipboard explicitly, but “drag out” the cells by dragging the mouse with the left key pressed. In this case, towing must begin strictly from the lower right corner of the cell. When positioned correctly, the mouse pointer shape should turn into a small black cross.

When copying formulas, sometimes some cell references need to be shifted, but others do not. If you fix a reference to a cell using a dollar sign when writing its name, as indicated above, then this reference will not move when copying. You can also fix just one row or column by entering one dollar sign instead of two.
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 through, then the fixation will be removed.