How to multiply formula in Excel. Multiplication formula in excel

Facebook

Multiplying and dividing in Excel is easy: just create a simple formula. Don't forget that all formulas in Excel begin with an equal sign (=), and you can use the Formula Bar to create them.

Multiplying numbers

Let's say you want to determine the number of bottles of water needed for a customer conference (total number of participants × 4 days × 3 bottles per day) or the amount of travel reimbursement for a business trip (total distance × 0.46). There are several ways to multiply numbers.

Multiplying numbers in a cell

* (star).

For example, when you enter a formula in a cell =5*10 the cell will display the result 50 .

Multiplying a column of numbers by a constant

Suppose you want to multiply the number in each of the seven cells in a column by the number contained in another cell. In this example, the multiplier is the number 3, located in cell C2.

Enter = a2 * $B $2 in a new spreadsheet column (the example above uses column d). Be sure to add a $ symbol before the B and before the 2 in the formula, and then press Enter.

Note: Using $Symbols tells Excel that the reference to B2 is absolute, meaning that when you copy the formula to another cell, it will always be in cell B2. If you didn't use $ symbols in the formula and didn't move the formula to cell B3, it will change to =a3*C3, which didn't work because there is no value in cell B3.

Drag the formula down to other cells in the column.

Note: In Excel 2016 for Windows, the cells will be filled in automatically.

Multiplying numbers in different cells using a formula

You can use the product function to multiply numbers, cells, and ranges.

Function PRODUCT can contain up to 255 numbers or cell references in any combination. For example, the formula = product (a2; A4: A15; 12; E3: range, 150, G4, H4: J6) multiplies two separate cells (a2 and G4), two numbers (12 and 150), and three ranges (A4:A15, E3:down, and H4:J6).

Dividing numbers

Let's say you want to know the number of man hours spent completing a project (total number of employees in Project), or the actual mpg rates for your last intercompany break (total miles gallons). There are several ways to divide numbers.

Dividing numbers in a cell

To accomplish this task, use the arithmetic operator / (slash).

For example, if you enter in a cell = 10/5 , the cell will display 2 .

Important: Make sure to enter an equal sign ( () before entering numbers and operator () in a cell. = ); otherwise Excel will interpret the entered date. For example, if you enter 7/30, Excel might display the 30th in the cell. If you enter 12/36, Excel first converts the value to 12/1/1936 and displays 1-Dec in the cell.

Note: Excel is missing a function divisions .

Dividing numbers using cell references

Instead of typing numbers directly into the formula, you can use cell references (such as a2 and A3) to refer to the numbers you want to divide by division and division.

Example.

To make this example easier to understand, copy it onto a blank sheet of paper.

Copying an example

Dividing a column of numbers by a constant by number

Suppose you want to divide each cell in a column of seven numbers by the number contained in the other cell. In this example, the number you want to divide by is 3, contained in cell C2.

Enter = a2/$C $2 to cell B2. Don't forget to add the $ symbol before the C and before the 2 in the formula.

Drag the formula in cell B2 down to the other cells in column B.

Note: Using $Symbols tells Excel that the reference to cell C2 is absolute, which means that when you copy a formula to another cell, it will always be in cell C2. If you did not use $ symbols in the formula and did not move the formula to cell B3, Excel will change the formula to =a3/C3, which will not work because there is no value in C3.

Excel is a popular program for working with basic accounting, reports and other work files. Despite the fact that courses on mastering this software are taught both at school and in higher education institutions, many people forget how to use the application’s functions. In this article, we will analyze the program options in detail and learn how to multiply a column by a column and a number in Excel.

Multiply by Column

You need to quickly multiply one column by another when calculating the total costs of certain goods. For example, the first column may indicate the quantity of building materials, and the second column may indicate the price per unit of goods. The resulting numbers will help you find out the costs for all types of products.

How to multiply column by column in Excel

If you follow this guide, you will be able to calculate the total accurately.

We create a third column where the product of numbers will be displayed. You can give it an appropriate name - “Work”.
Select the top (empty) line of the third column. Enter the “=” sign, then write “PRODUCT”. This is a multiplication command.
After “PRODUCT”, brackets will open in the third column line. They will display which cells are multiplied with each other. To insert their numbers into brackets, you need to highlight them with the cursor. Press "Enter".
The third column will display the product of the numbers from the selected cells.

Moving from the first line of the third column to the second, third and repeating steps 2-3 is too long. To avoid wasting time and multiplying each row separately, follow these steps:

Select the product of the first line (which was obtained as a result of steps 2-3 from the instructions above). A green frame will appear around the cell with the work, and a square will appear in the lower right corner of this frame.
Hold the square with the left mouse button, and then move the cursor down, all the way to the bottom cell of the third column. When the green frame highlights the rest of the third column, release the left button.
The third column will contain the multiplied numbers from the second and third columns.

This is the first method, which is suitable for beginners. An important nuance: if, after multiplication, “###” signs appear instead of numbers in the last column, this indicates that the numbers are too long for the cell. To display them correctly, the column is stretched.

There is a second way. Let's assume that you need to multiply one column by another and find out the sum of the resulting products. This is logical if, for example, you are making a shopping list. How to do it?

Create a separate cell where the sum of products will be displayed.
Go to the “Toolbar” at the top of the screen, select “Functions”.
In the pop-up menu, click on the line “Other functions”, select “Mathematical” from the list that appears.
From the list of mathematical functions, click on the “SUMPRODUCT” option. The sum of the columns multiplied by each other will appear in the previously selected cell.

Next, you need to select the range of cells that are multiplied among themselves. They can be entered manually: array 1, that is, the first column is B2:B11, array 2, that is, the second column is C2:C11. You can also select cells using the cursor, as in the algorithm above.

Those who have learned Excel functions do not use the panel, but immediately put the “=” sign in the desired cell, and then the “SUMPRODUCT” command.

Multiplying by a number

Even a novice user can figure out how to multiply a column by a number in Excel. This product can be calculated in a few clicks.

The main way to multiply an entire column by one number in Excel.

In a free cell, enter the number by which you want to multiply the column. For example, it is necessary to reduce the prices of goods taking into account a 10% discount. Then you need to enter the number 0.9 into the cell.
Copy a cell with a number using the keyboard shortcut “Ctrl and C”.
Select the column with the values that need to be multiplied by the number.
Right-click on the selected column.
Select Paste Special from the pop-up menu.
From the list of functions, click on the “Multiply” command. Click "OK" at the bottom of the pop-up menu.
The result of the operation will be displayed.

There is another method for multiplying a column by a number in Excel. It is suitable for professionals who constantly work with numbers. To carry out the operation, download a special add-on “Arithmetic Operations”. It is easy to find in the public domain for any version of the program.

After setting the setting, multiplication by a number is carried out using a simplified algorithm.

Use the cursor to highlight the cells that need to be multiplied by the number.
Click on add-on.
A pop-up window will appear. In it you need to select an action, in this case “*” (the “Multiply” command), and after it specify a number.
There is no need to click on “OK” or other buttons to receive the work. The result will appear in the columns automatically.

After mastering these instructions, even a new user will learn to confidently multiply numbers in Excel.

As you know, Microsoft Office Excel is an excellent program for analyzing information and creating databases. Due to its powerful mathematical apparatus, the editor allows you to solve complex problems in a short period of time. A library of engineering, statistical, financial and other functions expands the program's capabilities. Inexperienced users use only the simplest mathematical operators of addition, subtraction, multiplication and division. Today we will tell you how the multiplication formula works in Excel.

To multiply numbers together, just put an asterisk * between two values.

In this case, excel works like a regular calculator. But in practice it is convenient to use table addressing. The formula for multiplying cells is as follows:

Important! This recording principle is basic, and the presence of an asterisk is a prerequisite, since the editor does not recognize the multiplication operation and generates an error.

Consider some examples of multiplying different elements.

There is a column with employee salaries and the same bonus amount for everyone; you need to calculate the final payment.

Here we multiply the column by the percentage and add the original salary to it. The formula will look like this:

Please note that there is an absolute link inside that fixes the bonus percentage for calculation. To make such a link, you need to put a dollar sign or use the F4 button on your keyboard. Let's use the autocomplete handle to apply the formula to the entire column.

On a note! To multiply by one cell, be sure to use absolute references.

There is a column with the number of fruits and a column with their price. You need to know the lot price for each item.

To solve this problem you need to multiply column by column. In the cell, write down the formula for ordinary cell multiplication.

Then you drag the autocomplete marker down and get the result for each line.

You can multiply three, four or more columns in the same way. First you do the calculation for one position, and then apply the function for the entire column.

Function

The editor has a special function for performing multiplication.

PRODUCT is the name of the multiplication formula, which is built into the standard set of excel mathematical functions. The tool's arguments are filled with either numbers or cell references.

To insert a function, you can use several methods:

Via the Fx button. At the first stage, select a formula, and then enter the required number of arguments. In this case, the final result is immediately displayed in the argument selection window. Confirm the action by pressing the OK button.

The principle of operation of the function is exactly the same as with manual multiplication, but you do not need to put an asterisk.

As you can see, multiplication is implemented in Excel in several ways. It is necessary to have a general understanding of this mathematical operation, and the solution for each specific problem will be individual.

The Microsoft Excel table editor has a very wide range of capabilities for solving problems of varying complexity in various fields of activity. This is why Excel has become so popular among users around the world. One of the basic skills of working with the program is to carry out simple calculations and mathematical operations. In this article, we will look in detail at how to perform addition, subtraction, multiplication and division in Excel. Let's get started! Go!

Mathematical operations are performed without using a calculator

All calculations in Excel are based on the construction of simple formulas with which the program will perform calculations. First you need to create a table with the values. Please note that each table cell has its own address, which is determined by a letter and a number. Each letter corresponds to a column, and each number corresponds to a row.

Let's start with the simplest operations - addition and subtraction. To add numbers, you can use the so-called “AutoSum” function. It is convenient to use in cases where you need to calculate the sum of numbers that are in a row in one row, column, or in the area you have selected. To use this tool, go to the "Formulas" tab. There you will find the “AutoSum” button. Having selected the section of the table with the values that need to be added, click on the “AutoSum” button. After this, a separate cell will appear containing the result of the calculation. This was the first approach.

The second approach is that the calculation formula is entered manually. Let's say you are faced with the task of calculating the sum of numbers scattered across a table. To do this, make active (click on it with the left mouse button) the cell in which you want to place the calculation result. Then put the “=” sign and, in turn, enter the address of each cell whose contents need to be summed, not forgetting to put the “+” sign between them. For example, you should get: “=A1+B7+C2+B3+E5”. After the address of the last cell is entered, press “Enter” on the keyboard and you will receive the sum of all marked numbers. It is not necessary to enter each address manually. Just click on a specific cell and its address will immediately be displayed in the formula field, put “+” after it and move on to the next one.

There is another approach - using the Paste Special function. This method is convenient because it allows you to summarize data from several separate tables, provided that all their columns are the same. First, create a pivot table into which you will paste the copied data. Select the numbers in one table and paste them into the summary table, then do the same with the values in the second table, only this time right-click on the cell and select “Paste Special.” In the window that opens, in the “Insert” section, check “Values”, and in the “Operation” section, select add. As a result, all data will be summed up.

Subtraction in Excel is performed in the same way as addition. You will need to enter a formula, indicating the required cells, only instead of the “+” sign, “-” is placed between the addresses.

To multiply numbers in Excel, write a formula, marking the required data and putting an “*” between them. The formula will look like this: “=A3*A7*B2”.

Division is done in the same way, only the “/” sign is used. You can also perform several arithmetic operations at once. Formulas are built according to mathematical rules. For example: "=(B2-B4)*E8/(A1+D1)*D4". The formula you construct can be of any complexity; the main thing is not to forget the basic mathematical rules so that the calculation is performed correctly.

Having mastered the skills of simple arithmetic calculations in Microsoft Excel, you can already simplify the process of solving some problems and save time. Excel allows you to solve complex equations, perform engineering and statistical analysis. Gradually mastering the basic functions and tools of the program, you will learn to perform more and more operations in the Excel editor. Write in the comments whether the article helped you deal with your questions and share your experience with other users.

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 the 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 must 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.