Here are several ways how to multiply in Excel. We are providing a guide on the best ways of multiplication in Excel. Follow and get your best appropriate way. To make the most straightforward multiplication formula in Excel, type the equals sign (=) in a cell, then type the first number you want to multiply, followed by an asterisk, accompanied by the second number, and hit the Enter key to add the formula.
For instance, to multiply 2 by 5, you write this expression in a cell (with no spaces): =2*5
Microsoft Excel allows showing different arithmetic operations within one formula. Just learn about the order of calculations (PEMDAS): parentheses, multiplication, exponentiation, or division, whichever comes first, addition, or subtraction, whichever occurs first.
If you encounter troubles while working with Excel tables, one of the common reasons is the .xlsx file corruption. The Excel repair software can deal with this sort of problem with ease and allows the user to continue working with Excel files properly.
How to Multiply Cells in MSExcel
To multiply two Excel cells, use a multiplication formula like in the above example, but supply cell references instead of numbers. For instance, to multiply the value in cell A2 by the value in B2, write this expression:
=A2*B2
To multiply multiple cells, involve more cell references in the formula, separated by the multiplication sign. For example:
=A2*B2*C2
How to Multiply Columns in Excel
To multiply two columns in MS Excel, type the multiplication formula for the topmost cell, for instance:
=A2*B2
After you’ve set the formula in the first cell (C2 in this example), double-click the small green square in the lower-right corner of the cell to copy the formula down the column, up to the end cell with data:
Due to the use of relative cell references (without the $ sign), our MS Excel multiply formula will adjust appropriately for each row.
In my view, this is the best but not the only approach to multiplying one column by another. You can learn other entrances in this tutorial: How to multiply columns in MS Excel.
How to Multiply Rows in Excel
Multiplying rows in Excel is a less familiar task, but there is a simple solution for it too. To multiply two rows in MS Excel, do the following:
- Enter a multiplication formula in the first (leftmost) cell.
- In this example, we multiply values in row 1 by the values in row 2, beginning with column B. So our formula goes as follows: =B1*B2
- Choose the formula cell, and hover the mouse cursor over a little square at the lower right-hand corner until it changes to a thick black cross.
- Pull that black cross rightward over the cells where you need to copy the formula.
As with multiplying columns, the corresponding cell references in the formula change based on the relative position of rows and columns, multiplying a value in row 1 by a value in row 2 in each column.
How to Multiply in MS Excel – Multiply Function in Excel (PRODUCT)
If you want to multiply multiple cells or ranges, the fastest method would be using the PRODUCT function:
PRODUCT(number1, [number2], …)
Where number 1, number 2, etc. are numbers, cells, or areas that you need to multiply.
For instance, to multiply values in cells A2, B2, and C2, apply this formula:
=PRODUCT(A2:C2)
To multiply the figures in cells A2 through C2, and then multiply the outcome by 3, use this one:
=PRODUCT(A2:C2,3)
Multiply Numbers in Excel by Using the Product Formula
You can use the basic formula to cross-multiply the numbers in MS Excel if you use the product formula to multiply up to 255 values at once.
- So, First, go to MS Excel and choose any blank cell. Then type an equal symbol, followed by the word PRODUCT. Next, add an opening addition.
- Multiply Individual Cells, type the names of the cells, and divide them with commas without space.
- To Multiply a Series of Cells, type a colon between two cells’ names to specify that all cells within that range should be multiplied. For instance, “=PRODUCT(A2:A5)” point out that cells A2,A3, A4, and A5 should be multiplied.
- If you want to add a number to the equation, so enter a comma and then that number.
- When you complete entering the Values of your formula, then put a closing Parenthesis at last and hit the “Enter” button on your Keyboard. The equation answer will all show in the cell.
How to Multiply by Percentage in Excel
To multiply percentages in MS Excel, do a multiplication formula in this way: type the equals sign, followed by the number or cell, followed by the multiply sign (*), followed by a percentage.
In other terms, make a formula similar to these:
- To multiply a number by percentage: =50*10%
- To multiply a cell by percentage: =A1*10%
Rather than percentages, you can multiply by a similar decimal number. For example, knowing that 10 percent is ten parts of a hundred (0.1), use the following expression to multiply 50 by 10%: =50*0.1
How to Multiply a Column by a Number in Excel
To multiply a column of numbers by the corresponding number, proceed with these steps:
- Insert the number to multiply by in some cell, say in A2.
- Type a multiplication formula for the topmost cell in the column.
- Considering the numbers to be multiplied are in column C, beginning in row 2. You set the following formula in D2:
- =C2*$A$2
- You must lock the column and row coordinates of the cell with the number to multiply by to prevent the reference from changing. When you copy the formula to other cells. For this, type the $ symbol before the column letter and row number to make an absolute reference ($A$2). Or, click on the reference and press the F4 key to change it to complete.
- Double-click enough handle in the formula cell (D2) to copy the formula down the column. Done!
C2 (relative reference) changes to C3 when the formula is copied to row 3, while $A$2 (absolute reference) continues unchanged:
If the design of your worksheet does not provide an additional cell to accommodate the number, you can supply it directly in the formula, e.g., =C2*3
You can also apply the Paste Special > multiply feature to multiply a column by a constant number and get the results as values rather than formulas. Please check out this example for detailed instructions.
How to Multiply and Sum in Excel
When you require to multiply two columns or rows of numbers, and then add up the results of different calculations, use the SUMPRODUCT function to multiply cells and sum products.
Assuming you have prices in column B, quantity in column C, and calculate the total value of sales. In your math class, you’d multiply each Price/Qty. Pair individually and add up the sub-totals.
In Microsoft Excel, all these computations can be done with a single formula:
=SUMPRODUCT(B2:B5,C2:C5)
If you want, you can check the outcome with this calculation:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)
And make assured the SUMPRODUCT formula multiplies and sums correctly:
Multiplication in Array Formulas
In case you want to multiply two columns of numbers and then perform further calculations with the results. Do multiplication within an array formula.
In the higher data set, another way to calculate the total value of sales is this:
=SUM(B2:B5*C2:C5)
This Excel Sum Multiply formula is equivalent to SUMPRODUCT and returns precisely the same result.
Taking the case further, let’s find an average sales. For this, use the AVERAGE function instead of SUM:
=AVERAGE(B2:B5*C2:C5)
To find the most extensive and smallest sale, use the MAX and MIN functions, respectively:
=MAX(B2:B5*C2:C5)
=MIN(B2:B5*C2:C5)
To complete an array formula correctly, be sure to press the Ctrl + Shift + Enter combination instead of entering a stroke. As quickly as you do this, Excel will enclose the formula in {curly braces}, indicating it’s an array formula.
Conclusion
Suppose you belong to any organization or company. In that case, this guide will be helpful for you because it includes all the multiplication methods and it means a lot to every organizational person. Multiplication in Excel is not as difficult as you think. But if you want to do it quickly, you must memorize the formula to convert the complex method into an easy method. Let us know how much the Excel sheet is helpful for you.
