How to multiply in excel

In this article, we’ll learn how to use excel multiply formula using multiplication symbol in excel.

Do note, that there is no one single excel multiplication function which can be used. There are few different ways by which we multiply cells in excel. Let’s go through them.

Multiply in Excel using multiply symbol

The fastest way to do multiplication in excel is by using multiplication symbol *. Let’s see how to use excel function to multiply using this symbol.

In excel multiplication formula can be used by simply entering =Num1*Num2 in a cell. Num1 is the first number & num2 is the second number and by using (*) we are multiplying both numbers in excel
Excel Multiply Formula 1

  1. How to multiply cells in Excel
    To multiply multiple cells use the following excel multiplication formula.
    =A2*B2

    Excel Multiply Formula 2

  2. How to multiply columns in Excel
    Use the following formula in the top cell to multiply two column in excel.
    =A2*B2
    A2 is the value in first column and B2 is the value in second column. Once done, drag the formula to the rest of the cells.
    Due to use of relative cell references the Excel multiply formula will adjust accordingly for each row.

    Excel Multiply Formula 3

    You can use the same method to multiply multiple columns.

Multiply Function in Excel using PRODUCT Function

Let’s say we want to multiply various cells/values or ranges then to use the process we can use PRODUCT function in excel. Just type:

=PRODUCT(Num1,Num2…..)

Num1 can be a single cell as well as a range of cells that we can multiply in excel.

Excel Multiply Formula 4

How to multiply by percentage in Excel

To multiply by percentage in excel just use the multiplication symbol in excel with the percentage value. Let’s see the multiplication formula:

=A2*20%

Excel Multiply Formula 5

How to do multiplication and sum in excel using SUMPRODUCT

There are times when you would want to multiply different ranges or numbers and then add up the same. In such case you would have to first multiply and then add.

We can use Excel SUMPRODUCT function to speed up this process. Let’s say we have the following data and we want to calculate total sales of all products:

Excel Multiply Formula 6

Normally you would have to first multiply B2 and C2 then drag the formula down to D5. After which you would have to use SUM function to calculate the total sales.

You can simply use
=SUMPRODUCT(B2:B6,C2:C6)

Excel Multiply Formula 7

Things to note while using Excel function to multiply

Sometimes you would want to multiply a range of cells with a fixed number present in a given cell. In such a case when you drag down the formula you’ll start seeing wrong values. Let’s see how to avoid this:

To multiply a range of by a fixed number, use these steps:

  1. Enter the number to multiply by in cell A2
  2. Enter the data range in column C
  3. Write excel multiplication formula in D2 and then drag it down.
    Excel Multiply Formula 8
  4. Now to avoid this problem, we need to lock the column & row coordinates of A2 cell. For this in the formula use $ symbol to make an absolute reference.
    =C2*$A$2
  5. Hit enter and drag down the formula
    Excel Multiply Formula 9

You can use the multiplication symbol to multiply values in array formulas. You can use it in conjunction with different functions like MIN, MAX, AVERAGE, SUM etc.

Example:
Let’s say we want to find average sales from the given dataset.
Excel Multiply Formula 10

We can use the excel multiply symbol within AVERAGE formula like this. This will make the make the process simpler:
=AVERAGE(B2*C2,B3*C3)
Excel Multiply Formula 11

Conclusion

In excel multiplication formula is one the most basic formulas which gets used. It is widely used in business operations while calculating prices and working with sales figures.

I hope this article was helpful to you and explained you Excel multiply function, how to multiply a column in excel and much more.

If you have any questions or tips, I’d love to hear them in the comment section below.

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Ask a Question or Leave a Reply

Excel Categories

Free Excel Resources

Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.

Excel Training Courses

Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.

Learn Army Logo
Learnarmy.com Copyright © 2020 All Rights Reserved