Microsoft Excel is full of useful features, including those for performing calculations. If there comes a time when you need to multiply two columns in Excel, there are various ways to do it. We’ll show you how to multiply columns right here.
How to multiply a column in Excel
To multiply columns in Excel, you can use operator, function, formula or function to solve the problem. Let’s jump.
Use the multiplication operator
Just like multiplying a single set of numbers using the multiplication operator (asterisk), you can do the same for values in columns using cell references. Then use the fill handle to copy the formula to the rest of the column.
Select the cell at the top of the output range where you want the results. Then enter the multiplication formula using cell references and an asterisk. For example, we will multiply two columns starting from cells B2 and C2 and this formula:
=B2*C2
When you press Enter, you will see the result of your multiplication formula.
You can then copy the formula to the remaining cells in the column. Select the cell containing the formula and double-click the fill handle (green square) in the lower right corner.
The empty cells below are filled with the formula, and the cell references are set up automatically. You can also drag the marker down to fill in the remaining cells instead of double-clicking on it.
Note. If you use absolute cell references instead of relative ones, they won’t automatically update when copying and pasting a formula.
Pull in the PRODUCT function
Another good way to multiply columns in Excel is to use the PRODUCT function. This function works in the same way as the multiplication operator. This is most useful when you need to multiply many values, but in Excel it works great for column multiplications.
The syntax of the function is PRODUCT(value1, value2,...)that you can use cell references or numbers as arguments. For column multiplication, you will use the first one.
Using the same example above, you start by entering the formula and then copy it into the remaining cells. So, to multiply the values in cells B2 and C2, you have to use this formula:
=PRODUCT(B2,C2)
Once you have the result, double-click the fill handle or drag it down to fill the rest of the column with the formula. Again, you will see that relative cell references are set up automatically.
Create an array formula
If you want to remove the fill formula step from the process, consider using an array formula to multiply columns. With this type of formula, you can perform a calculation for all values in one fell swoop.
There is a slight difference in applying array formulas in Excel 365 compared to other versions of Excel.
For Excel 365, enter an array formula in the top left cell of the output range. The formula starts with an equal sign (=) and includes the first cell range, the multiplication asterisk, and the second cell range. After entering the formula, press Enter to apply it.
Using our example, we will multiply the cell range from B2 to B8 by C2 to C8 using this formula:
=B2:B8*C2:C8
As you will see, this will immediately populate your column with the results of the multiplication.
For other versions of Excel, you use the same formula but apply it in a slightly different way. Select an output range, enter an array formula in the top left cell of that range, and then press Ctrl+Shift+Enter.
You’ll notice that curly braces surround the array formula in this case; however, the results are the same and will fill your output area.
Use the special paste function
Another way to multiply columns in Excel is to use the special “Paste” function. Although this method includes a few extra steps, it may be the most convenient for you.
You will copy and paste one range of cells into the output range. Then copy another range of cells and use Paste Special to multiply the values.
Here we multiply the values in column B (B2 to B8) by the values in column C (C2 to C8) and put the results in column D (D2 to D8). So first we will copy the values from cells C2 to C8 and paste them into cells D2 to D8.
To do this quickly, select the cells, press Ctrl+C to copy them, go to cell D2 and press Ctrl+V to paste them.
Note. Alternatively, you can right-click and use the context menu to select the Copy and Paste actions.
Then copy the group of cells you want to multiply by. Here we select cells B2 to B8 and press Ctrl+C to copy them.
Then go to cell D2, right-click and choose Paste Special > Paste Special.
In the pop-up window, leave the default “All” checked in the “Insert” section and select “Multiply” in the “Operation” section. Click OK.
You will then see your columns multiply just like you would with any of the other methods above.
These different column multiplication methods in Excel work well. So if you’re more comfortable using the multiplication operator than the PRODUCT function, or prefer an array formula over special insertion, you’ll still get the job done.
Leave a Reply