Suppose you want to multiply a column of numbers by the same number in another cell. The trick to multiplying a column of numbers by one number is adding $ symbols to that number's cell address in the formula before copying the formula.
In our example table below, we want to multiply all the numbers in column A by the number 3 in cell C2. The formula =A2*C2 will get the correct result (4500) in cell B2. But copying the formula down column B won’t work, because the cell reference C2 changes to C3, C4, and so on. Because there’s no data in those cells, the result in cells B3 through B6 will all be zero.
To multiply all the numbers in column A by cell C2, add $ symbols to the cell reference like this: $C$2, which you can see in the example below.
Using $ symbols tells Excel that the reference to C2 is “absolute,” so when you copy the formula to another cell, the reference will always be to cell C2. To create the formula:
-
In cell B2, type an equal (=) sign.
-
Click cell A2 to enter the cell in the formula.
-
Enter an asterisk (*).
-
Click cell C2 to enter the cell in the formula.
-
Now type a $ symbol in front of C, and a $ symbol in front of 2: $C$2.
-
Press Enter.
Tip: Instead of typing the $ symbol, you can place the insertion point either before or after the cell reference that you want to make “absolute,” and press the F4 key, which adds the $ symbols.
Now we’ll step back a bit to see an easy way to copy the formula down the column after you press Enter in cell B2.
-
Select cell B2.
-
Double-click the small green square in the lower-right corner of the cell.
The formula automatically copies down through cell B6.
And with the formula copied, column B returns the correct answers.
