Multiplication Formula in Excel (6 Quick Approaches)

Excel formulas are made for us to calculate problems easily. But there is no fixed multiplication formula in Excel. Today we are going to see some methods of multiplying data in Excel.


Practice Workbook

Download the following workbook and exercise.


6 Methods to Apply Multiplication Formula in Excel

 1. Use of Asterisk (*) Symbol for Algebraic Multiplication Formula in Excel

This asterisk (*) operator is known as a multiplication symbol in Excel.

1.1 Multiply Rows by Using Asterisk

Here we have a dataset of some random numbers in two different rows. We have to multiply them and display the result in another cell.

Multiply Rows by Using Asterisk

STEPS:

  • Select Cell C6.
  • Put an equal (=) sign.
  • Now write the formula:
=C4*C5

Multiply Rows by Using Asterisk

  • Then press Enter and use Fill Handle to drag rightward to the remaining cells to see the result.

Multiply Rows by Using Asterisk

1.2 Multiply Columns by Using Asterisk

Now we have a dataset of two columns of random numbers. We have to multiply them alongside and display the results in the Product column.

Multiply Columns by Using Asterisk

STEPS:

  • Select Cell D5.
  • Put an equal (=) sign.
  • Now write the formula:
=B5*C5

Multiply Columns by Using Asterisk

  • After that, press Enter and drag down to the remaining cells to see the results.

Multiply Columns by Using Asterisk

Read more: How to Multiply in Excel: Columns, Cells, Rows, & Numbers


2. Insert PRODUCT Function as Multiplication Formula

To multiply several cells together, we can use the Product Function. For the dataset below, we are going to find out how this function works out.

Insert PRODUCT Function as Multiplication Formula

STEPS:

  • Select Cell D5.
  • Enter the following formula:
=PRODUCT(B5,C5)

Insert PRODUCT Function as Multiplication Formula

  • Hit Enter and drag down the Fill Handle icon to see the results.

Insert PRODUCT Function as Multiplication Formula


 3. Enter SUMPRODUCT Function to Multiply in Excel

To multiply the sets of cells or arrays and return their sum of products, we can use the SUMPRODUCT function. Here we have a worksheet containing the employee’s working hours per week. We are going to find out the total working hours per week of all the employees.

Enter SUMPRODUCT Function to Multiply in Excel

STEPS:

  • Select Cell C10.
  • Write the formula:
=SUMPRODUCT(C5:C9,D5:D9)

Enter SUMPRODUCT Function to Multiply in Excel

  • Press Enter to see the outputs.

Enter SUMPRODUCT Function to Multiply in Excel


Similar Readings:


 4. Multiply Column by a Constant Value in Excel

Let’s say we have a Salary worksheet. Here we are going to find out the total salary of each for three months by multiplying the salary range with the constant value 3.

Multiply Column by a Constant Value in Excel

STEPS:

  • Select Cell D5.
  • Type the formula:
=B5*$C$5

Here we have to use the ‘$’ sign by pressing the F4 key for the cell reference to make it absolute or constant.

Multiply Column by a Constant Value in Excel

  • Now hit Enter and drag down the cursor.
  • The final result is displayed.

Multiply Column by a Constant Value in Excel

Read more: How to Multiply a Column in Excel by a Constant


 5. Multiplication of Values by Using Percentages in Excel

Here is a salary sheet of the employees with their salaries and the percentages they’ll receive. Now we are going to count the extra amount by multiplying all salaries by the percentage values.

Multiplication of Values by Using Percentages in Excel

STEPS:

  • Select the Cell E5.
  • Write the formula:
=C5*D5

Multiplication of Values by Using Percentages in Excel

  • After pressing Enter, drag down the cursor to see the results for the remaining cells.

Multiplication of Values by Using Percentages in Excel

Read more: How to Multiply by Percentage in Excel


 6. Array Formula for Multiplication in Excel

To perform dynamic calculations for multiple sets of data, we use Array Formulas. Here we have a worksheet containing the employee’s working hours per week. We are going to find out who worked for a maximum of hours per week among all the employees. Also for the minimum.

Array Formula for Multiplication in Excel

STEPS:

  • Select Cell C10.
  • Write the formula:
=MAX(C5:C9*D5:D9)

Array Formula for Multiplication in Excel

  • Hit Enter.
  • Now select Cell C11.
  • Then write the formula:
=MIN(C5:C9*D5:D9)

Array Formula for Multiplication in Excel

  • Hit Ctrl+Shift+Enter.
  • Finally, we can see the results.

Array Formula for Multiplication in Excel

Read more: What is the Formula for Multiplication in Excel for Multiple Cells?


Alternative to Excel Multiplication Formula: Paste Special Option

Paste Special is another multiplying way in Excel. Here I have some salary data in column B. We multiply them with the value of month 3 from Cell D5.

Alternative to Excel Multiplication Formula: Paste Special Option

STEPS:

  • At first copy the cell D5 by pressing Ctrl+C keys.
  • Now select the cells we wanted to multiply by the value of cell D5.
  • Right-click the mouse on the area we pointed to and go for Paste Special.

Alternative to Excel Multiplication Formula: Paste Special Option

  • A dialogue box is showing.
  • From the Operation part, choose Multiply and press OK.

Alternative to Excel Multiplication Formula: Paste Special Option

  • Now we can see that all the selected cells are multiplied by the value of Cell D5.

Alternative to Excel Multiplication Formula: Paste Special Option


Conclusion

By using these methods, we can easily multiply values in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo