Nuraida Kashmin
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.
STEPS:
- Select Cell C6.
- Put an equal (=) sign.
- Now write the formula:
=C4*C5
- Then press Enter and use Fill Handle to drag rightward to the remaining cells to see the result.
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.
STEPS:
- Select Cell D5.
- Put an equal (=) sign.
- Now write the formula:
=B5*C5
- After that, press Enter and drag down to the remaining cells to see the results.
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.
STEPS:
- Select Cell D5.
- Enter the following formula:
=PRODUCT(B5,C5)
- Hit Enter and drag down the Fill Handle icon to see the results.
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.
STEPS:
- Select Cell C10.
- Write the formula:
=SUMPRODUCT(C5:C9,D5:D9)
- Press Enter to see the outputs.
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.
STEPS:
- Select Cell D5.
- Type the formula:
=B5*$C$5Here we have to use the ‘$’ sign by pressing the F4 key for the cell reference to make it absolute or constant.
- Now hit Enter and drag down the cursor.
- The final result is displayed.
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.
STEPS:
- Select the Cell E5.
- Write the formula:
=C5*D5
- After pressing Enter, drag down the cursor to see the results for the remaining cells.
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.
STEPS:
- Select Cell C10.
- Write the formula:
=MAX(C5:C9*D5:D9)
- Hit Enter.
- Now select Cell C11.
- Then write the formula:
=MIN(C5:C9*D5:D9)
- Hit Ctrl+Shift+Enter.
- Finally, we can see the results.
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.
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.
- A dialogue box is showing.
- From the Operation part, choose Multiply and press OK.
- Now we can see that all the selected cells are multiplied by the value of Cell D5.
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.
