How to Multiply in Excel (Best Ways of Multiplication in Excel)

Nov 11, 2020
How To Multiply In Excel

Here is the several ways on 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

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.

How to Multiply Cells in Excel  

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 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 Excel multiply formula will adjust appropriately for each row.

In my view, this is the best but not the only approach to multiply one column by another. You can learn other entrances in this tutorial: How to multiply columns in Excel.

How To Multiply Cells In Excel
How To Multiply Cells In 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 Excel, do the following:

  1. Enter a multiplication formula in the first (leftmost) cell.
  2. 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
  3. 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.
  4. 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 a 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 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 number1, number2, 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)

How To Multiply Columns In Excel
How To Multiply Columns In Excel

How to Multiply by Percentage in Excel

To multiply percentages in 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 the Excel

To multiply a column of numbers by the corresponding number, proceed with these steps:

  1. Insert the number to multiply by in some cell, say in A2.
  2. Type a multiplication formula for the topmost cell in the column.
  3. Considering the numbers to be multiplied are in column C, beginning in row 2, you set the following formula in D2:
  4. =C2*$A$2
  5. 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.
  6. 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 of 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.

hitech work
Follow me
Latest posts by hitech work (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top