How does multiplication of numbers and cells work in Excel. multiplication formula in excel how to multiply in excel

Multiplication and division in Excel is easy: just create a simple formula. Keep in mind that all formulas in Excel begin with an equal sign (=), and you can use the formula bar to create them.

Number multiplication

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

Multiply numbers in a cell

* (star).

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

Multiply 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 in the other cell. In this example, the multiplier is the number 3 located in cell C2.

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

Note: The use of $Symbols tells Excel that the reference to B2 is absolute, which means 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.

Multiply numbers in different cells using a formula

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

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

Number division

Let's say you want to know the number of man-hours it took to complete a project (total number of employees in the Project) or the actual mpg rates for your last intercompany break (total mpg). There are several ways to separate numbers.

Dividing numbers in a cell

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

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

Important: Make sure you enter an equals sign () before entering numbers and operator () into the 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 that value to 12/1/1936 and displays 1-Dec in the cell.

Note: Function missing in Excel division .

Dividing numbers using cell references

Instead of entering numbers directly in a formula, you can use cell references (such as a2 and A3) to refer to numbers to be divided into divisions and divisions.

Example.

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

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 another 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 C and before 2 in the formula.

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

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

Today, one of the most popular tools for complex calculations is the program Microsoft Excel. Its wide functionality allows you to simplify the solution of many tasks that previously had to spend a lot of time. Today we will look at how to multiply cells in Excel with each other in large quantities.

Multiplication in Excel

Before you figure out the numbers, it is worth noting the wide functionality of the program, which allows you to work both with the explicit assignment of numbers, and using cells that contain both the numbers themselves and the formulas that determine them. In order to multiply one or more cells by a number or other cells, you must specify these sheet elements in the formula bar after the equal sign or write the value yourself.

Multiply cell by cell

Next, it is worth understanding the principle, like cell by cell, and what problems can be encountered in the process. To multiply the values of two cells, it is necessary to write the following construction in the formula bar: "=A * B", where "A" and "B" are links to the corresponding elements of the Excel sheet, that is, cells.

Having received the result, it may be necessary to create a copy of it, and often users, without hesitation, simply copy the value of this cell, pasting it into another. However, with this approach, there will also be a change in the corresponding links pointing to multipliers. In order to avoid such an error, either in the cell where you want to copy the result, enter a link to the copied cell, or "fix" the address of its multipliers using the "$" sign. The dollar sign stores the value of the reference by the parameter following it - that is, the reference $A4 will always refer to column A, A$4 to the fourth row, and $A$4 to only cell A4. In other words, fixing a cell is creating an absolute reference to it.

Multiply columns and rows

Using the cell address fixing algorithm, you can go directly to how to multiply a column by column or row by row in Excel. In order not to waste time writing a huge formula, you can simply use the property of changing the cell reference when moving to a new pointer address. That is, if you need to multiply the values in two columns in pairs, it is enough to write down the multiplication formula for the first pair of cells, after which, holding the "black plus" sign that appears in the lower right corner, stretch the result down along all the values of the multiplier columns.

In order to achieve a similar result for rows, it is only necessary to stretch the result along the corresponding rows. It is worth noting: in order to avoid shifting during further copying of the results, you need to fix the specified column or row in the formula - then you will be able to avoid errors in the calculation.

Multiply a row or column by a cell

Having understood how to multiply a column by a column or a row by a row in Excel, it is worth stopping at one specific operation - multiplying a cell by a row or column. If, when multiplying, we use the same algorithm of actions as in the case of multiplying columns, then as a result only the value of the first row of this column will be multiplied, since the cell address will change with each cell descent by one position.

To avoid this, it is necessary to fix either the row number of the multiplied cell, or its entirety - in this case it will be possible to use its value for several columns. In the case of multiplying a string by a certain cell, it is enough to fix the letter of the column in which it is located, or to make both parameters of the cell address constant.

How to multiply all numbers in a row or column

Sometimes in Excel you need to perform an operation similar to the total amount, only by performing a multiplication operation. However, there is no special automatic procedure for this, so many, not knowing how to multiply the numbers of one column or row in Excel, do this explicitly, writing down the action for each cell. In fact, there is an operation in the program that can help solve the question of how to multiply numbers in Excel with each other several times faster.

In order to multiply a certain range of cells among themselves, it is enough to select the operation with the name "PRODUCT" in the "Insert function" menu. As arguments to this function, you must specify the cells that should be multiplied by each other, it can be either columns, rows, cells, or entire arrays. It should be noted that if there is an empty cell in the specified range, the multiplication result will not be equal to zero - in this case, the program will ignore this cell, that is, it will equate the empty value to one.

Summary

Having figured out how to multiply in Excel, you can implement quite complex documents, including economic reports and estimates. Most arithmetic operations can be performed in a similar way, but for some it is worth considering the specifics of their implementation, as well as the format of the cells. So, for example, when dividing a cell by cell, if there is no value in one of them, the result will be a division by zero error.

Multiplication in Excel is done in the same way as any other operation, mainly through a formula. But besides formulas, there are other options, and we will consider how to multiply in Excel in various ways in this article.

How to multiply numbers in Excel?

To multiply numbers in Excel, after entering the equal sign, list the multiplied values in the cell, between which set the asterisk symbol «*» . At the same time, it must be remembered that the priority of performing operations is the same as in mathematics, and for the correct execution of operations with numbers, it is better to use brackets.

How to multiply cell by cell in Excel?

Multiplying a cell by a cell in Excel is done by setting the asterisk symbol between the multiplied cells «*» .

How to multiply a column by a column in Excel?

To multiply a column by a column in Excel, you need to write the correct formula, and stretch it over the entire range of multiplied values. To do this, we enter the formula for multiplying the first two values in the column, and apply the formula to all cells along the entire height of the multiplied values.

How to multiply a column in Excel by a number?

To multiply a column in Excel by a number, you can make a formula by specifying the multiplication factor in it, or you can multiply the entire selected range of values \u200b\u200bby any number without any formulas through the menu.

If the number by which the cells will be multiplied is in a table cell, then the reference to this cell in the formula will need to be made absolute so that it does not change when the formula is applied to other cells.

Also in Excel it is possible to multiply a range of values by a number using Paste Special to replace the original values. To do this, we need to copy our number coefficient to the clipboard, select the range of cells for multiplication, and on the tab "Home" on the menu "Insert" choose "Special Insert...". Or select the same item in the menu after clicking on the selected range with the right mouse button.

In the window that appears in the column "Operation" choose "Multiply".

Excel (Excel) is an application program designed to create spreadsheets (matrices) and automated processing of the entered data. Using this spreadsheet processor, you can build charts, quickly perform various calculations (using functions and formulas built into the editor), solve optimization problems, perform statistical data processing, as well as analyze and predict.

Excel is a powerful program for working with electronic matrices, knowledge of the basic formulas of which makes it possible to simplify the process of managing tabular data. One of the most useful and commonly used functions is the multiplication operation.

Quick article navigation

Multiplication of two numbers

One of the simplest operations performed in Excel is the multiplication of two numbers. To multiply prime numbers in Excel:

Using the navigation arrow keys or a computer mouse, go to an empty cell in Excel;
Press "=" to start entering the formula;
Type the required mathematical operation in the cell, using the asterisk "*" as the multiplication sign (for example, "=2*3", "=2*3*4*5*6");
When finished entering, press the "Enter" key.

The Excel program will calculate the result and display it in the same cell.

Building a Calculator Form

If necessary, the Excel spreadsheet allows the user to create a permanent form from several cells, one of which will enter the value of the multiplier, the other - the value of the multiplier, and the third will display the result of the multiplication. To create a form, you need:

Type in the first cell a number (multiplicable) and press the "Enter" key;
In the second cell, print the second number (multiplier) and also press "Enter";
In the third cell, press the “=” key and use the navigation keys to go to the first cell;
Press the asterisk "*";
Use the navigation keys or the mouse cursor to move to the second cell (for example, "=A1*A2");
Press the Enter key to finish entering the formula.

The spreadsheet will display the result of the multiplication in the third cell. With this calculator, you can change the values of the multiplier and multiplicand, and the Excel program will show their product in the third cell.

Excel is a popular program for working with basic accounting, reports and other work files. Despite the fact that courses on the development of this software are taught both at school and in higher educational 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 by a number in Excel.

Column multiplication

You need to quickly multiply one column by another when calculating the total costs for 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 to 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 accurately calculate the final amount.

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

Going from the first line of the third column to the second, third and repeating the steps from points 2-3 is too long. In order not to waste time and not to multiply each line separately, you should follow the steps.

Select the product of the first line (which was obtained as a result of points 2-3 from the instructions above). A green frame will appear around the cell with the work, 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 to the bottom cell of the third column. When the green frame highlights the remainder of the third column, the left button is released.
The third column will display the multiplied numbers from the second and third columns.

This is the first way, 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. For their correct display, the column is stretched.

There is also a second way. Suppose that you need to multiply one column by another, and find out the sum of the resulting products. This is logical if, for example, a shopping list is being compiled. How to do it?

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

Next, you need to select a range of cells that are multiplied among themselves. They can be entered manually: array 1, that is, the first column - B2:B11, array 2, that is, the second column - 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.

Multiplication by a number

How to multiply a column by a number in Excel, even a novice user will figure it out. Such a product is calculated in a few clicks.

The basic way to multiply an entire column by a single 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 drive 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 to be multiplied by the number.
Click on the selected column with the right mouse button.
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 how to multiply a column by a number in Excel. It is suitable for professionals who constantly work with numbers. To carry out the operation, download the 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 according to a simplified algorithm.

Select with the cursor the cells that need to be multiplied by a 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.
You do not 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 how to confidently multiply numbers in Excel.