How to multiply cells in Excel with formula examples

Multiply in Excel by using multiplication operator

The easiest way to do multiplication in Excel is by using the multiply symbol (*). With this approach, you can quickly multiply numbers, cells, entire columns and rows.

Multiply function in Excel (PRODUCT)

If you need to multiply multiple cells or ranges, the fastest method would be using the PRODUCT function:

Where number1, number2, etc. are numbers, cells or ranges that you want to multiply.

For example, to multiply values in cells A2, B2 and C2, use this formula:

=PRODUCT(A2:C2)

To multiply the numbers in cells A2 through C2, and then multiply the result by 3, use this one:

=PRODUCT(A2:C2,3)

The screenshot below shows these multiplication formulas 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 percentage.

In other words, make a formula similar to these:

• To multiply a number by percentage: =50*10%
• To multiply a cell by percentage: =A1*10%

Instead of percentages, you can multiply by a corresponding decimal number. For example, knowing that 10 percent is 10 parts of a hundred (0.1), use the following expression to multiply 50 by 10%: =50*0.1

As shown in the screenshot below, all three expressions yield the same result:

How to multiply a column by a number in Excel

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

1. Enter the number to multiply by in some cell, say in A2.
2. Write a multiplication formula for the topmost cell in the column.

   Assuming the numbers to be multiplied are in column C, beginning in row 2, you put the following formula in D2:

   =C2*$A$2

   It is important that you 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 absolute.

3. Double-click the fill handle in the formula cell (D2) to copy the formula down the column. Done!

As you can see in the screenshot below, C2 (relative reference) changes to C3 when the formula is copied to row 3, while $A$2 (absolute reference) remains unchanged:

If the design of your worksheet does not allow an additional cell to accommodate the number, you can supply it directly in the formula, e.g.: =C2*3

You can also use 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 the detailed instructions.

How to multiply and sum in Excel

In situations when you need to multiply two columns or rows of numbers, and then add up the results of individual calculations, use the SUMPRODUCT function to multiply cells and sum products.

Supposing you have prices in column B, quantity in column C, and you want to 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 calculations can be done with a single formula:

=SUMPRODUCT(B2:B5,C2:C5)

If you wish, you can check the result with this calculation:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)

And make sure the SUMPRODUCT formula multiplies and sums perfectly:

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 above 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 exactly the same result (please see the screenshot below).

Taking the example further, let's find an average of sales. For this, just use the AVERAGE function instead of SUM:

=AVERAGE(B2:B5*C2:C5)

To find the largest 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 properly, be sure to press the Ctrl + Shift + Enter combination instead of Enter stroke. As soon as you do this, Excel will enclose the formula in {curly braces}, indicating it's an array formula.

The results might look something similar to this:

That's how you multiply in Excel, it doesn't take a rocket scientist to figure it out :) To have a closer look at the formulas discussed in this tutorial, feel free to download our sample Excel Multiplication workbook.

How to quickly do any calculations in Excel

If you are a novice to Excel and are not comfortable with multiplication formulas yet, our Ultimate Suite will make things a lot easier for you. Among 70+ cute features, it provides the Calculation tool that can perform all basic mathematic operations, including multiplication, in a mouse click. Let me show you how.

Supposing you have a list of net prices and you want to know the corresponding VAT amount. No big deal if you know how to calculate percentages in Excel. If you don't, have the Ultimate Suite do the job for you:

1. Copy the prices to the VAT column. You need to do this because you don't want to override the original values in the Price column.
2. Select the copied prices (C2:C5 in the screenshot below).
3. Go to the Ablebits tools tab > Calculate group, and do the following:
   • Select the percentage symbol (%) in the Operation box.
   • Type the desired number in the Value box.
   • Click the Calculate button.

   

That's all there is to it! You will have the percentages calculated in a heartbeat:

In a similar manner, you can multiply and divide, add and subtract, calculate percentages, and more. All you have to do is select an appropriate operator, for example the multiply symbol (*):

To perform one of the recent calculations to another range or column, just click the Apply Recent button, and pick the operation:

The results of all calculations done with the Ultimate Suite are values, not formulas. So, you are free to move or copy them to another sheet or workbook without worrying about updating formula references. The calculated values will remain intact even if move or delete the original numbers.

If you are curious to learn more about this and many other time-saving tools included with the Ultimate Suite for Excel, you are welcome to download 15-day trial version.