Soumik Dutta
Multiplying multiple cells in Excel is a familiar task for every Excel user. We do it to serve the different purposes of our daily life. In this article, we will show 4 different methods to multiple one cell by multiple cells. If you don’t know how to multiply one cell by multiple cells in Excel, I am confident that this context might be helpful for you.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
4 Easy Methods to Multiply One Cell by Multiple Cells in Excel
To demonstrate these processes, we considered a dataset of 10 employees of a company. Our dataset is in the range of cells B4:G5. The Name of the employees is in column B, their Income in column C, the rate of salary Increment in column D, and the amount to Pay will be in column E. Besides it, they have a Tax cut of 5 percent of their salary, the rate is in cell G5.
1. Manual Process to Multiply Cell in Excel
In this process, we will multiply the Income cell with the rate of Increment and the rate of Tax manually. The steps of this procedure are given below:
📌 Steps:
- First of all, select cell B5.
- Now, write down the following formula into the cell. To get the ‘$” sign, press the F4 key on your keyboard. Otherwise, you can simply click before G by your mouse and press ‘Shift+4’. Similarly, do the same task to place the ‘$’ sign before 5.
=C5*D5*$G$5- Press the Enter key on your keyboard to get the result.
- Then, double-click on the Fill Handle icon to copy the formula up to cell E14. Or you can simply drag the Fill handle icon by clicking on your mouse up to cell E14.
- You will get the final payment amount at the desired cells.
Thus, we can say that our method worked perfectly.
🔍 Things You Should Know
We are using the ‘$’ before the cell reference G5 to fix the cell. So that, when we copy the formula by the Fill Handle icon the cell reference of the Tax rate will not change. This is called Absolute Cell References. If you click on cell E6 or E7, you will find in all the cells the tax rate remains the same cell. However, the cells refer to the Income and the Increment is changed in every row.
Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)
2. Using PRODUCT Function to Multiply Multiple Cells
In this method, we are going to use the PRODUCT function to multiply the Income cell with the rate of Increment and the rate of Tax. The steps of this method are given as follows:
📌 Steps:
- First, select cell B5.
- After that, write down the following formula into the cell. To put the ‘$” sign, press the F4 key on your keyboard. Or, you can simply click before G and 5 with your mouse and press ‘Shift+4’.
=PRODUCT(C5,D5,$G$5)
- Now, press the Enter.
- Double-click on the Fill Handle icon to copy the formula up to cell E14. Or you can simply drag the Fill handle icon by clicking on your mouse up to cell E14.
- You will get the final payment amount at the desired cells.
So, we can say that our formula worked perfectly and we get our desired result.
🔍 Things You Should Know
The advantage of using the ‘$’ before the cell reference G5 is to fix the cell. As a result, when we copy the formula by the Fill Handle icon the cell reference of the Tax rate will not change. This is called Absolute Cell References. If you click on cell E6 or E7, you will find in all the cells the tax rate remains the same cell.
Read More: Multiplication Formula in Excel (6 Quick Approaches)
Similar Readings
- How to Multiply Columns in Excel (9 Useful and Easy Ways)
- Multiply Two Columns in Excel (5 Easiest Methods)
- How to Use Multiply Sign in Excel (With 3 Alternative Methods)
- Multiply a Column in Excel by a Constant (4 Easy Ways)
- How to Multiply Two Columns and then Sum in Excel
3. Applying SUMPRODUCT Function
In this procedure, we are using the SUMPRODUCT function to multiply the cells. We are using the same dataset which we have already used in our previous methods. After completing the process, we will get the result in column E. The process describes below step by step:
📌 Steps:
- At the beginning of this process, select cell B5.
- Now, write down the following formula into the cell. To place the ‘$” sign, press F4 on your keyboard. Or, you can simply click before G and 5 by your mouse and press ‘Shift+4’.
=SUMPRODUCT(C5,D5,$G$5)- Press the Enter.
- Then, double-click on the Fill Handle icon to copy the formula up to cell E14. Or you can simply drag the Fill handle icon by clicking on your mouse up to cell E14.
- The result will be at our desired cells.
Finally, we can say that our formula worked perfectly and we get our desired result.
🔍 Things You Should Know
The ‘$’ sign before the cell reference G5 uses to fix the cell. As a result, when we copy the formula by the Fill Handle icon the cell reference of the Tax rate will not change. This is called Absolute Cell References. If you click on cell E6 or E7, you will find in all the cells the tax rate remains the same cell. But the cells refer to the Income and the Increment is changed in every row.
Read More: How to Multiply in Excel: Columns, Cells, Rows, & Numbers
4. Multiply Range of Cells Using Paste Special Command
This procedure is quite different from the other approaches. Here, we will use a special feature of Excel to multiple the cells. As a result, our dataset also gets some modifications. In this method, we will use the same salary Increment rate for all the employees and it is shown in cell F8. The Tax rate is in cell F5. The process of this method is shown as follows:
📌 Steps:
- Select the range of cells C5:C14 and press ‘Ctrl+C’ to copy the data.
- Now, paste the value in the range of cells D5:D14.
- Or you can simply select the range of cells C5:C14 and simply drag the Fill Handle icon to column E to copy the data.
- After that, select cell F8 and press ‘Ctrl+C’ to copy.
- Select the range of cells D5:D14.
- Then, right-click on your mouse and select the Paste Special command.
- A small dialog box entitled Paste Special will appear,
- Now, change the Operation option from None to Multiply.
- Click OK.
- You will get the multiplication result of Income and Increment.
- Now, select cell F5 and press ‘Ctrl+C’.
- Again, select the range of cells D4:D14 and right-click on the mouse.
- Select the Paste Special command and the Paste Special dialog box will appear.
- Then, change the Operation option from None to Multiply.
- Click OK and you will get the result.
Finally, we can say that our approach worked successfully and we can multiply one cell by multiple cells in Excel.
🔍 Things You Should Know
In this case, you cannot multiply all the cells at the same time like the other methods. You have to multiply one by one cell criteria. So, if you have a large amount of dataset to handle, we recommend you avoid this process. Sometimes, it may manipulate your original data.
Conclusion
That’s the end of this article. I hope that this will be helpful for you and you will be able to multiply one cell by multiple cells in your Excel worksheet. If you have any further queries or recommendations, please share them with us in the comments section below.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
