In Excel, there is no single "universal multiplication formula" that covers all possible multiplication scenarios. Instead, Excel offers multiple ways to perform multiplication, each suitable for different needs and situations. Users can choose from basic operations using the asterisk (*) symbol, functions like PRODUCT and SUMPRODUCT, or features like Paste Special, depending on the complexity and nature of their data. In this guide, we'll dive into the diverse ways you can perform multiplication in Excel, tailoring each method to your specific needs, whether it's handling basic number calculations or dealing with more complex data sets.

• Multiplying individual numbers
• Multiplying cells
• Multiplying two rows or columns
• Multiplying column by a constant
• Multiplying a cell by percentage

Multiply with the Multiplication Symbol (*)

The most basic operation for performing multiplication is to use the asterisk * symbol. In this section, I will show you how to use this symbol for multiplication in different situations.

Amazing! Using Efficient Tabs in Excel Like Chrome, Edge, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Multiplying Individual Numbers

For example, to multiply the number 5 by the number 3, you can do as follows.

1. Select a cell (A2 in this case) to output the result.
2. Start by entering an equal sign “=”, and then enter the numbers with the multiplication symbol. Here I enter the following formula into cell A2.

   =5*3

   
3. Press the Enter key to get the result.
   

Multiplying Cells

If you have numbers in different cells, you can apply the * symbol to multiply these cells.

1. Select a cell to output the result.
2. Start by entering an equal sign “=”, select the first number cell, type in the * symbol, and then select the second number cell. Here the formula will display as:

   =A2*B2

   
3. Press the Enter key to get the result.
   

Multiplying Two Rows or Columns

In this example, I will show how to calculate the sales for each product by multiplying the Unit price column and the Quantity column.

1. Click on cell D2, enter the multiplication formula as follows and press Enter to get the total sale for product A.

   =B2*C2

   
2. Click on cell D2 again, and then hover your mouse over the small square at the bottom right corner of the cell until the cursor turns into a plus sign. Click and drag this down to cell D6.
   
   Now you have multiplied the two columns.

Note: To multiply two rows, use the * symbol with this formula: =B2*B3, and then drag the Fill Handle to the right over the cells to obtain the results for the remaining cells.

Multiplying a Column by a Constant

To multiply a column by a constant number, say 5, you can use the following formula and drag the fill handle down across the column.

=B2*5

Multiplying a Cell by Percentage

In Excel, performing multiplication that involves percentages is a common operation and is particularly useful when applying a discount, calculating a commission, or adjusting values based on percentage increases or decreases.

Let's say you have a product that costs $200, and you want to apply a 15% discount to this price. To get the discount amount, here’s how you would do it:

• Select a cell where you want the discount amount to be displayed.
• Enter one of the following formulas into the cell. Ensure that 15% is formatted as a percentage, or alternatively, you can enter it as 0.15.

  =200*15%

  =200*0.15

• Press Enter, and the cell will display the discount amount, which is $30 in this case.
  

Multiply Multiple Cells or Ranges with PRODUCT

The multiplication symbol (*) is a quick and direct method for multiplying a small number of figures. The PRODUCT function, on the other hand, is a more efficient and clean choice for larger sets of numbers, especially when dealing with ranges of cells.

Easily multiply all numbers in a range

If you want to multiply all numbers from A2 to A6, apply this formula.

=PRODUCT(A2:A6)

The formula =PRODUCT(A2:A6) is equivalent to =A2*A3*A4*A5*A6.

Easily multiply numbers in different ranges

For example, you have two ranges A2:A6 and B2:B6, and you want to multiply all the numbers in these ranges together, apply this formula.

=PRODUCT(A2:A6,B2:B6)

This formula takes all the numbers in the range from A2 to A6 and all the numbers in the range from B2 to B6, and multiplies them together, which is equivalent to =A2*A3*A4*A5*A6*B2*B3*B4*B5*B6.

Multiply with Paste Special Feature

This Paste Special feature is useful for applying a multiplication operation across a range without using a formula.

For example, you want to multiply a range of cells (A2:A6) by a number in cell C2. You can do as follows.

1. Select the cell with the number you want to multiply with, and then press the Ctrl + C keys to copy it. Here I copy cell C2.
   
2. Select the range A2:A6 and right click on it. In the right-clicking menu, choose Paste Special > Paste Special.
   
3. In the Paste Special dialog box, select the Multiply option and click OK.
   
4. Then all numbers in the selected cells are multiplied by the number 5. See screenshot:
   

Multiply and Sum with SUMPRODUCT

The SUMPRODUCT function in Excel is a versatile tool, especially powerful when you need to multiply corresponding elements in arrays (or ranges) and then sum up those products.

Let’s say you have a set of products, their respective prices, and sold quantities, and now, you want to calculate the total revenue.

To calculate the total revenue generated from all products, please do as follows:

1. Select a cell where you want the total revenue (say, C8), enter the SUMPRODUCT formula and press Enter.

   =SUMPRODUCT(B2:B6,C2:C6)

   
   The cell will display the total revenue generated from all products.

Multiplying in Excel is a fundamental skill that can be applied in various scenarios. From basic multiplication to more advanced techniques like SUMPRODUCT, this tutorial offers multiple ways to approach multiplication. Armed with these skills, you are now well-equipped to tackle a wide range of multiplication scenarios in Excel, enhancing both your productivity and analytical capabilities. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Related Articles