Microsoft Excel is primarily designed to manipulate numbers, so it provides a handful of different ways to perform basic math operations as well as more complex calculations. In our last tutorial, we discussed how to multiply cells in Excel. In this tutorial, we will take a step further and look at how you can quickly multiply entire columns.
As is the case with all basic math operations, there is more than one way to multiply columns in Excel. Below, we will show you three possible solutions so you can choose the one that works best for you.
The easiest way to multiply 2 columns in Excel is by making a simple formula with the multiplication symbol (*). Here's how:
Supposing, your data begins in row 2, with B and C being the columns to be multiplied. The multiplication formula you put in D2 is as plain as this:
Since you use relative cell references (without the $ sign) in the formula, the references will change based on a relative position of the row where the formula is copied. For example, the formula in D3 changes to
=B3*C3, the formula in D3 becomes
=B4*C4, and so on.
If you prefer working with Excel functions rather than expressions, you can multiply 2 columns by using the PRODUCT function, which is a specially designed to do multiplication in Excel.
For our sample data set, the formula goes as follows:
As with the multiply symbol, the key point is using relative cell references, so that the formula can adjust properly for each row.
You enter the formula in the first cell, and then copy it down the column as explained in the above example:
One more way to multiply entire columns in Excel is by using an array formula. Please don't feel discouraged or intimidated by the words "array formula". This one is very straightforward and easy to use. You simply write down the ranges you want to multiply separated by the multiplication sign, like this:
To insert this multiplication formula in your worksheets, perform these steps:
As the result, Excel will multiply a value in column B by a value in column C in each row, without you having to copy the formula down.
This approach might be useful if you want to prevent accidental deletion or alteration of the formula in individual cells. When such an attempt is made, Excel will show a warning that you cannot change part of an array.
To multiply more than two columns in Excel, you can use the multiplication formulas similar to the ones discussed above, but include several cells or ranges.
For example, to multiply values in columns B, C and D, use one of the following formulas:
As shown in the screenshot below, the formulas multiply numbers and percentages equally well.
In situations when you want to multiply all values in a column by the same number, proceed in one of the following ways.
As it happens, the fastest way to do multiplication in Excel is by using the multiply symbol (*), and this task is no exception. Here's what you do:
In this example, we are going to multiply a column of numbers by percentage. Since in the internal Excel system percentages are stored as decimal numbers, we can insert either 11% or 0.11 in B1.
In our sample table, the numbers to be multiplied are in column B beginning in row 4, so the formula goes as follows:
You use an absolute cell reference (like $B$1) to fix the column and row coordinates of the cell with the number to multiply by, so that this reference doesn't change when copying the formula to other cells.
You use a relative cell reference (like B4) for the topmost cell in the column, so that this reference changes based on the relative position of a cell where the formula is copied.
As the result, the formula in C5 changes to
=B5*$B$1, the formula in C6 changes to
=B6*$B$1, and so on.
Tip. If you are multiplying a column by a constant number that is unlikely to change in the future, you can supply that number directly in the formula, for example:
If you want to get the result as values, not formulas, then do a multiplication by using the Paste Special > Multiply feature.
Or, right-click the selection, choose Paste Special... in the context menu, select Multiply under Operations, and click OK.
Either way, Excel will multiply each number in the range C4:C7 by the value in B1 and return the results as values, not formulas:
Note. In some situations, you may need to reformat the Paste Special results. In the above example, we multiplied a column of numbers by percentage, and Excel formatted the results as percentages, while they should be numbers. To fix this, select the resulting cells and apply the desired Number Format to them, Currency in this case.
Like Paste Special, this multiplication method returns values rather than formulas. Unlike Paste Special, Ultimate Suite for Excel is user-friendly and intuitive. Here's how you can multiply a column of numbers by another number in a couple of clicks:
As an example, let's calculate the 5% bonus on our sales. For this, we copy the sales values from column B to column C, and then either:
Both methods do multiplication right and produce identical results:
Unlike Excel's Paste Special feature, the Ultimate Suite retains the original Currency format, so no further adjustments to the results are required. If you are curious to try Ultimate Suite's calculation options in your worksheets, you are welcome to download an evaluation version by using the link below.
I thank you for reading and hope to see you on our blog next week!
Table of contents