Although Excel is a powerful data analysis tool, many users use it for basic arithmetic operations.
One common requirement is to multiply an entire column by a number (a constant value).
In this tutorial, I will show you two easy ways to multiple an entire column with a given number.
So let’s get to it!
Mulitply Column with a Number Using Formula (Hardcoding the Value in Formula)
Suppose I have a dataset as shown below, where I have the Sales Rep names in Column A and their current sales values in Column B.
I want to calculate their Sales target for the next year, which would be 10% higher than their current sales.
This essentially means that I want to increase all the values in column B by 10% (i.e., multiply these values by 1.1 or 110%)
Hardcoding the Value in the Formula
Below is the formula to multiply 110% with the values in the entire column B2 (use this formula in cell C2):
=B2*110%
The above formula would give you the result of the multiplication of value in cells B2 with 110%.
But we also want to get the result when all the values in column B are multiplied with the same number.
To do this, select cell C2, place the cursor at the bottom right part of the selection, hold the left mouse key and drag down. This will copy the same formula for all the cells in column C.
Alternatively, you can also copy cell C2 and paste it into the cells below it (a simple Control +C and Control + V would work).
Note that the result of the formula is dynamic. So if you change the values in column B, the result would accordingly update. If you don’t want this to be dynamic, and instead want static values, you can convert the formula to values.
Multiply Entire Column with a Value in a Cell
In the above example, I hardcoded the value 110% in the formula.
Another option is to have the value, with which I want to multiply the entire column, in a separate cell, and use the cell reference instead of hardcoding the actual value in the formula.
The benefit of this method is that in case I change the value in the cell, the formulas would automatically update.
Below I have the same dataset, and I have the new sales target percentage in cell E2.
Below is the formula that will give me the new sales target:
=B2*$E$2
To multiple the entire column, you need to copy the cell with the formula and paste it into all the cells in the column. This will copy the formula as well and give you the right result.
How does this work?
The trick in this method is in using the dollar signs in the reference of the cell that contains the number with which we want to multiply the entire column ($E$2 in this example).
When you add a dollar sign before the row number and the column alphabet, it makes sure that when that formula is copied in other cells, the reference does not change.
In our formula, the $E$2 portion of the formula would not change, while the A2 would become A3 when the formula is copied in cell C3 and it would become A4 when the formula is copied in cell C4, as so on.
Note: In case you’re using Excel for Microsoft 365, where you have access to dynamic arrays, you can simply use the formula =B2:B13*E2. You don’t need to copy for the entire column, the formula itself would spill the result for the entire range.
Also read: How to Square a Number in Excel
Mulitple Column with a Number Using Paste Special
Another method that you can use to quickly multiply an entire column with a given number is by using the Paste Special technique.
Suppose you have a data set as shown below, where I want to multiply the number in cell E2 with the entire data set in column A.
Below are the steps to do this:
- Copy all the values in column B and paste it in column C. We are doing this as the Paste Special multiplication would be applied in column C, and we would also retain the original values in column B.
- Copy cell E2 (you can select it and use Control + C, or you can right-click on it and then click on Copy.
- Select all the cell in column A with which you want to multiply the number
- Right-click on the selected cells and then click on Paste Special option
- In the Paste Special dilaog box that opens, select the Multiply option in Operations
- Click OK
That’s it!
The above steps would instantly change all the values in column A and give you the result after it has multiplied these numbers with the value in cell E2.
The result you get is static values (as compared to a formula that you get in the method before that)
Once you are done with the multiplication, you can delete the value in cell E2 (if you want).
One important thing to note about using this method is that when you multiply an entire column using the paste special method, it also copies the formatting from cell E2. So if you give a cell color to cell E2 and use this method, all the cells in column A would also have that color copied to it. To avoid this, you can also select the Value option in the Paste Special dialog box in step 4
So these are two simple methods that you can use to multiply an entire column with a number in Excel.
I hope you found this tutorial useful.
Other Excel tutorials you may also find helpful:
