[ad_1]
To multiply columns in Excel, use a formula that includes two cell references separated by the multiplication operator (asterisk). Then use the fill handle to copy the formula to all the other cells in the column. You can also use the PRODUCT function, an array formula, or the Paste Special function.
Microsoft Excel is packed with useful functions, including those for performing calculations. If there comes a time when you need to multiply two columns in Excel, there are several methods to do it. Here we will show you how to multiply columns.
How to multiply a column in Excel
To multiply columns in Excel, you can use an operator, function, formula, or feature to tackle the task. Let’s jump.
Use the multiplication operator
Just like multiplying a single set of numbers with 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.
RELATED: How to multiply numbers in Microsoft Excel
Select the cell at the top of the output range, which is the area where you want the results. Then, enter the multiplication formula using the cell references and the asterisk. For example, we’ll multiply two columns starting with 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 automatically adjusted. You can also drag the handle down to fill the remaining cells instead of double-clicking it.
Note: If you use absolute cell references instead of relative ones, they won’t update automatically when you copy and paste the formula.
Another good way to multiply columns in Excel is to use the PRODUCT function. This function works the same as the multiplication operator. It’s most beneficial when you need to multiply a lot of values, but it works fine in Excel for multiplying columns.
The function syntax is PRODUCT(value1, value2,...) where you can use cell references or numbers for the arguments. To multiply columns, you’ll use the first one.
Using the same example above, start by entering the formula, and then copy it to the remaining cells. So, to multiply the values in cells B2 and C2, you would use this formula:
=PRODUCT(B2,C2)
Once you receive your result, double-click the fill handle or drag it down to fill the rest of the column with your formula. Again, you’ll see the relative cell references adjust automatically.
Create an array formula
If you want to remove the “fill formula” step from the process, consider using an array formula to multiply your columns. With this type of formula, you can perform your calculation for all values in one go.
There is a slight difference in the way you apply array formulas in Excel 365 compared to other versions of Excel.
For Excel 365, enter the array formula in the top left cell of your output range. The formula begins with an equal sign (=) and includes the first range of cells, an asterisk for multiplication, and the second range of cells. Once you enter the formula, press Enter to apply it.
Using our example, we will multiply the range of cells B2 to B8 by C2 to C8 using this formula:
=B2:B8*C2:C8
As you will see, this fills your column with the results of the multiplication all at once.
For other versions of Excel, it uses the same formula but applies it a little differently. Select the output range, enter the array formula in the top left cell of that range, and then press Ctrl+Shift+Enter.
You’ll notice that the square brackets surround the array formula in this case; however, the results are the same and will fill your starting area.
Use the Paste Special function
One more way to multiply columns in Excel is with the Paste Special function. While this method involves a few extra steps, it may be the one you’re most comfortable with.
RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel
You will copy and paste a range of cells into your output range. Then copy the other range of cells and use Paste Special to multiply the values.
Here, we multiply the values in column B (B2 to B8) by those in column C (C2 to C8) and put the results in column D (D2 to D8). So first we’ll copy the values in C2 through C8 and paste them into cells D2 through D8.
To quickly do this, select the cells, press Ctrl+C to copy them, go to cell D2, and press Ctrl+V to paste.
Note: Alternatively, you can right-click and use the context menu to choose Copy and Paste actions.
Next, 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 popup window, leave the default “All” checked in the Paste section and select “Multiply” in the Operation section. Click OK.”
You will then see your columns multiplied just as if you used any of the other methods above.
These different methods to multiply columns in Excel work fine. So if you’re more comfortable using the multiply operator than the PRODUCT function or prefer the array formula over Paste Special, you’ll still be able to get the job done.
For more information, see how to divide numbers in Microsoft Excel.
[ad_2]
