Columns run vertically along with the grid layout of Excel and are denoted by letters like A, B, C, D. We may require to multiply columns in Excel. Sometimes, we need to multiply columns in Excel. Very often we require multiplying more than two columns. In this article, we have tried to demonstrate 9 very easy and super useful ways to do so. These are very easy to use and, if mastered, these can improve your speed and productivity.
Here, we have a dataset that contains the product list of a hardware store. In four columns, we have shown the Products sold, Price/ Unit, Percentage after Discount, and Total amount.
Download Practice Workbook
9 Useful and Easy Ways to Multiply Columns in Excel
Now the store owner wishes to know the total amounts gained by selling different products. He or she will need to multiply the Quantity by Price per Unit and Percentage after Discount column. The following methods below show how the shop owner can do so.
1. Multiply One Column by Another with Multiplication Symbol
We can use an asterisk (*) symbol to multiply one column by another. The asterisk symbol is also known as the multiplication symbol. Suppose, we are to find the result of the multiplication of Price/Unit and Quantity. Then we can use an asterisk (*) symbol to do so.
First, select any cell of your choice to place the resultant value.
Here, we selected the E5 cell in this case.
In the E5 cell type the following formula.
Here, to get the Total of Flashlight, we multiplied the C5 cell value of Price/Unit by the D5 cell value of the Quantity column.
Then, we use the Fill Handle to use the AutoFill feature.
Then we would double click or drag the Fill Handle to use the AutoFill feature to get all the corresponding values.
Finally, we would get all the Total values in column E.
2. Multiply Multiple Columns Using Multiplication Symbol
We may require multiplying multiple columns very often. We can use Asterisk(*) or multiplication symbol to do so.
Here, we want to multiply Price/Unit, Quantity, and Percentage after Discount in the Total column. In the F5 cell, write the following formula.
For multiplying several columns we have to put an asterisk (*) symbol between the addresses of different cells. We want to find the resultant value of multiplication of Price/Unit, Quantity, and Percentage after Discount. Here, in the F5 cell of the Total column, we multiplied the cells C5*D5*E5 where C5 corresponds to Price/ Unit, D5 corresponds to Quantity, and E5 corresponds to Percentage after Discount.
Press the ENTER key, would get the resultant value in the F5 cell.
Then, like before, we double-click or drag the Fill Handle to AutoFill the formula for the rest of the cells.
We will get the corresponding values in the Total column.
3. Multiply Two Columns with PRODUCT Function
We can multiply two columns using the PRODUCT function. The PRODUCT function is used to find the multiplication value of different cells.
We want to find the value of the Total sales of Price/ Unit and Quantity.
In this case, first, we select the cell E5 of the Total column where we want to get the resultant value of multiplication and type
Here, the C5 cell gives us the value of Price/Unit and the D5 cell gives us the value of Quantity. The PRODUCT function will return the Total sales for the selected Product.
After pressing the ENTER key, we will get the results in the Total column.
Again, using the Fill Handle we will get the product of all the corresponding rows in the Total column.
4. Multiply Multiple Columns with PRODUCT Function
We can multiply several columns using the PRODUCT function. To do so, we will have to type =PRODUCT(value 1,value 2, value 3,……).In this case, we want to get the value of multiplication of Price/ Unit, Quantity, and Percentage after Discount.
The C5 cell gives us the value of Price/Unit, the D5 cell gives us the value of Quantity, and the E5 cell gives us the value of Percentage after Discount. In this case, in cell F5, we type
Pressing the ENTER key will get the result in the F5 cell.
Now, you can use the Fill Handle to use the AutoFill feature to get all the corresponding values.
- How to Make Multiplication Table in Excel (4 Methods)
- Multiply One Cell by Multiple Cells in Excel (4 Ways)
- How to Multiply Matrices in Excel (2 Easy Methods)
5. Multiply Entire Columns with an Array Formula
We can use Array formulas to calculate the result of multiplication. To do this, first, we have to type the ranges of cells and put multiplication symbols between them. To use an array formula we have to select the whole range over which we want to put the resultant values. In this case, we select F5 to F10, where we want to put the Totals of different products. In this case, in the F5 cell, we type
Then we press CTRL + SHIFT + ENTER . This is important because an array formula will not work if we don’t press CTRL + SHIFT + ENTER together. Here, you will get your resultant value in the Total column.
6. Paste Special Multiply
We can use the Paste Special feature to multiply a range of values by a certain value. To do this, we need to select the value by which we want to multiply the whole range of values. Here we will multiply the product of Price/Unit and Quantity with the value of Percentage after Flat Discount.
Here, we want to multiply the product of column C and column D with the value of Percentage after Flat Discount. To do this we first copy the value in H7 by right-clicking and selecting COPY or using the CTRL+C. After that, we select the range over which we want to perform the multiplication operation. In this case, we select E5 to E10.
Then, we go to the Paste option in the toolbar. From the select Paste Special.
Now, a dialog box will appear. From there we would select the Multiply Then, select OK to get the value multiplied by the selected cell.
Hence, it will Multiply all the selected values with a certain cell value.
7. Using the Range-of-Cells Method
We can find the result of the multiplication of a range of cells using the PRODUCT We can do this by using the PRODUCT function and the range. Here, like before, we want to get the resultant value of Price/Unit, Quantity, and Percentage after Discount. C5 cell gives us the value of Price/Unit, D5 cell gives us the value of Quantity, and E5 cell gives us the value of Percentage after Discount. Here, we selected the F5 cell and typed
Pressing the ENTER key, we would get the multiplied values in the Total column.
Here, the C5:E5 portion of the formula denotes the range we want to multiply. Now, use AutoFill for the rest of the cells.
We will get the values in the Total column.
8. How to Multiply a Column by a Number in the Excel
We can use an Absolute Reference to multiply a column by a certain value. We can use absolute reference by putting the dollar sign ($) to the row and column numbers or using the F4 key. Here we want to multiply the product of Price/Unit and Quantity with the Percentage after a Flat Discount. C5 value gives us the Price/ Unit, D5 value gives us the Quantity, and H8 value gives us the Percentage after a Flat Discount. Percentage after Flat Discount would remain the same for all the values. In cell E5, we put
Now, press the ENTER key to get the value in the E5 cell.
Then, we drag or double-click the Fill Handle to use the AutoFill feature to get corresponding values to all the cells in the Total column.
9. Multiply a Column Using Percentage
Multiplying a column using percentages is the same as the previous method. It only requires putting a percentage value in the fixed value cell. We can also do this using the paste special method.
Things to remember
Never forget to add an equal sign (=) while typing a formula.
Be careful to put a comma(,) between different values when using the PRODUCT function.
When you are trying to run an array formula, always press CTRL + SHIFT + ENTER. We often call array formulas CSE formulas as array formulas require pressing CTRL + SHIFT + ENTER together.
When trying to insert or use absolute referencing always remember to put a dollar sign ($).
We have provided a practice section so that you can practice and sharpen yourself.
In this article, we have tried to cover all the possible ways to multiply columns in Excel. We have covered 9 different ways to multiply columns in Excel. If you have any queries about any of the methods above or know more about any different methods please feel free to reach out to us. We have a dedicated team to cater to all your needs related to Excel.