How to Multiply Two Columns in Excel (5 Easiest Methods)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has several functions that may be used to do simple Arithmetic Operations as well as more complex calculations. Multiplication is one of the most used operations in Excel, and it may be done in a variety of ways. We’ll go through several quick and simple methods to see how to multiply two columns in Excel.
We’ll use a sample dataset as an example to help you understand the concept better.
Here, we are given name of the Items in column B, Price in column C, Quantity in column D, and Discount percentage in column E. We want to count the Sales amounts using this dataset.
multiply two columns in excel


1. Multiplying Two Columns Using the Asterisk Symbol in Excel

The easiest way to multiply two columns in an Excel sheet is using an asterisk (*) symbol.
Suppose we want to know how much Sales is generated for a particular product. So, we have to multiply the values of the Price in the column with the values of the Quantity column. Let’s see, how to do it.
First, click on cell E5. Now we can select any cell or go to the formula bar and type the following formula.

=C5*D5
Multiply two columns by asteriskPress ENTER key, we will get the following result which will represent Sales of Shirt.

Here, we are multiplying two cells C5 with Cell D5 using the Asterisk (*) symbol. We got the result of $100 as Sales value.
Now, we can simply click on the right button of the mouse then drag it down. Here we are using the AutoFill feature to fill other cells automatically. Now, our cells will look like the following image.
multiply columns


2. Multiplying Two Columns with Excel Product Formula

Using an asterisk symbol can be time-consuming if we have to deal with a huge number of data. In Excel, one of the quickest ways to multiply columns or ranges is to use the PRODUCT function.
In our data set, we want Sales values by multiplying Price and Quantity.
To do it, first, click in cell E5, then type the following formula.

=PRODUCT(C5:D5)
how to multiply columns by product functionPress the ENTER key. It will show you the result.
multiply columns by product formulaSee, we have the result of $100 as Sales value in cell E5. Here Excel is simply multiplying cells C5 and cell D5.
Note: During the use of the PRODUCT function we can select desired cells using a colon (:) or comma (,). In this scenario, we can also use the formula as
=PRODUCT(C5,D5)
Now, we will use AutoFill.Click the right button on the mouse and drag it down to the rest of the column where we want our data.

Similar Readings


3. Multiplying Two Columns by a Constant Number

In our data set, we can see that there is a 5% Discount. So if we want to calculate sales value after discount how we would do it. Let’s get into this,
So, we have to calculate the Sales value after discount by multiplying price, quantity, and discount. And most of all a 5% discount is applicable for all the items.
So, we can say that we need to multiply columns with a constant number.
Now, click on cell F5 and type the following formula.

=C5*D5*(1-$E$7)
how to multiply by constant valuePress the ENTER key.
What is happening here?
We are multiplying cells C5, D5, and E7. Also used  Absolute Reference for cell E7. Here, we subtracted the discount value to get the total Sales after the discount.
You use an Absolute Cell Reference (like $E$7) to ensure that the column and row coordinates of the cell containing the number to multiply by do not change while copying the formula to other cells.
You use a Relative Cell Reference (like C4) for the topmost cell in the column, as a result of the relative location of a cell where the formula is copied, this reference changes.
Therefore, the formula in F6 changes to =C6*D6*(1-$E$7) the formula in F7 changes to =C7*D7*(1-$E$7), and so on.
Now, right-click the mouse button and drag it down to the end of the column to get the results.
how to multiply coluns by a constant value

4. Multiplying Two Columns Using Excel Paste Special

Paste Special function gives us the option to get values instead of formulas.
First, we have copied values from columns D5 to D9 into columns E5 to E9.
Now, select all the values in column C
➤ I selected the range C5:C9.
how to multiply two columns by paste specialNow, right-click on the mouse and select Copy from the context menu.
multiply columns by paste specialNow, select cell E5:E9, and again right click on the mouse button a dialog box will appear.
From there, select Multiply then clicks on OK.
multiply two column with paste specialHere, you will get Sales for all the selected Items.


5. Multiplying Two Columns in Excel with Array Formula

Another way to multiply two columns in excel is the Array formula. It’s a simple and easy method.
First, select cells from E5 to E9 (E5:E9).
Now, we can type =C5:C9 or simply select all the required cells by dragging the mouse.
multiply two columns by Array formulaNow, type the asterisk symbol and select cells D5:D9.
The formula will be as given.

=C5:C9*D5:D9
multiply columns with arrayNow, press CTRL +  SHIFT + ENTER altogether as it’s an array formula.
Here we go, we get the results we wanted. Here we are multiplying C5 with D5, C6 with D6, and so on till C9 with D9 where we used the cell values as an array.

Read More: How to Multiply Multiple Cells in Excel


Things to Remember

Note: If you are using an upgrade version of Microsoft Excel 2013 then you won’t need to use CTRL + SHIFT + ENTER for any Array formula.


Download Practice Workbook


Conclusion

These are 5 different techniques to multiply two columns in Excel. You can select the best option based on your preferences. Please let me know if you have any questions or feedback in the comments section. You may also go through our other Excel-related articles on this site.


Related Articles

Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo