To multiply a column by a number in Excel, you can use a simple formula that references the column and the number.
Using a Formula
Here’s how it works, including information from our reference:
- Identify the Column and Number: Determine which column contains the numbers you want to multiply and the cell containing the multiplier.
- Enter the Formula: In the first cell of the column where you want the results, enter a formula like this:
=A2*C2.A2is the first cell in the column you want to multiply (in our reference example).C2is the cell containing the number you want to multiply by (in our reference example it's 3).- The
*symbol is the multiplication operator in Excel.
Example:
| Column A | Column B (Result) | Column C |
|---|---|---|
| 1500 | 4500 | 3 |
| 2000 | ||
| 2500 |
In this example, the formula =A2*C2 in cell B2 gives you the result of 1500 * 3 = 4500.
Applying the Formula to the Entire Column
Once you have the formula working in the first row, you can apply it to the rest of the column:
- Click and Drag: Click on the small square at the bottom-right corner of the cell with the formula (B2 in our example). Then, drag it down to fill all the cells in that column with the formula. The cell references will adjust automatically to correspond with the correct row (e.g. cell B3 would now use the formula
=A3*C2, and cell B4 would use=A4*C2). - Double-Click: Alternatively, you can double-click the small square and Excel will automatically apply the formula to all the adjacent rows containing data.
Absolute Reference:
To multiply all numbers by the same cell that is located outside the column (for example in C2), you need to use an absolute reference, using $ in the cell reference, such as in =A2*$C$2. The absolute reference will keep the cell C2 the same, while the column reference A2 changes when you drag it down.
- Why use $C$2 instead of $C2 or C$2: It is useful if you only need to keep one part of the cell reference fixed (e.g.,
C$2would keep only the row fixed so if you drag the formula sideways it will change the column, whereas$C2would keep only the column fixed). When you want to drag down the formula and keep the cell fixed, use `$C$2.
Additional Tips
- Make sure the cells containing your numerical data are formatted as 'Number'.
- If your data has different types (e.g., text) it can cause errors or unexpected results.
- You can also use the same method to divide (use
/), add (use+), or subtract (use-) a column by a given number.
