How to Multiply One Cell by Multiple Cells in Excel (4 Ways)

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.

Following Manual Process to Multiply Cell

  • 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.

Following Manual Process to Multiply Cell

  • You will get the final payment amount at the desired cells.

Following Manual Process to Multiply Cell

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.

Using PRODUCT Function to Multiply Multiple Cells

  • 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.

Using PRODUCT Function to Multiply Multiple 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


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.

Applying SUMPRODUCT Function

  • 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.

Applying SUMPRODUCT Function

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.

Multiply Range of Cells Using Paste Special Option

  • 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.

Multiply Range of Cells Using Paste Special Option

  • After that, select cell F8 and press ‘Ctrl+C’ to copy.
  • Select the range of cells D5:D14.

Multiply Range of Cells Using Paste Special Option

  • Then, right-click on your mouse and select the Paste Special command.

Multiply Range of Cells Using Paste Special Option

  • A small dialog box entitled Paste Special will appear,
  • Now, change the Operation option from None to Multiply.
  • Click OK.

Multiply Range of Cells Using Paste Special Option

  • 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.

Multiply Range of Cells Using Paste Special Option

  • Select the Paste Special command and the Paste Special dialog box will appear.
  • Then, change the Operation option from None to Multiply.

Multiply Range of Cells Using Paste Special Option

  • Click OK and you will get the result.

Multiply Range of Cells Using Paste Special Option

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!


Related Articles

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo