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

We’ll use a sample dataset as an example to help you understand how to multiply columns.
We are given Names of some Items in column B, Prices in column C, Quantities in column D, and Discount percentages in column E. We want to calculate the total Sales.

Method 1 – Using the Asterisk Symbol to Multiply Two Columns in Excel

Suppose we want to know how much Sales is generated for a particular product.

Click on cell E5.
Go to the formula bar and insert the following formula:

=C5*D5

Press Enter.

Here, we are multiplying two cells C5 with Cell D5 using the Asterisk (*) symbol. We got the result of $100 as the Sales value.

Click on the bottom-right corner of the cell and drag it down. This is using the AutoFill feature to fill other cells automatically.

Method 2 – Multiplying Two Columns with the PRODUCT Formula

In our data set, we want Sales values by multiplying Price and Quantity.

Insert the following formula in E5:

=PRODUCT(C5:D5)

Press the Enter key. It will show you the result.

We have the result of $100 as Sales value in cell E5. Excel is simply multiplying cells C5 and cell D5.

With 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)

Use AutoFill. Click the bottom-right corner of the cell and drag it down.

Method 3 – Multiplying Two Columns by a Constant Number

In our data set, we can see that there is a 5% Discount. We want to calculate sales value after the discount.

Click on cell F5 and use the following formula.

=C5*D5*(1-$E$7)

Press the Enter key.

We are multiplying cells C5, D5, and E7. We also used the 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. When the formula is copied, this reference changes to the appropriate row and column. 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.