Download the Practice Book
3 Easy Methods to Multiply a Column by a Number in Excel
In the following table, we have used 5 columns and 11 rows to present some salesperson’s targets for four consecutive years. The target in 2022 is 3 times the target that was in 2019. We’ll fill Column F by multiplying Column C with the value “3”.
Method 1 – Utilizing the Paste Special Option
Steps:
- Copy the values of Column C (i.e. Target 2019) into Column F (i.e. Target 2022).
- Select Cell G5 where the number 3 is located and press Ctrl + C to copy the cell value.
- Select the cells in Column F and right-click on them.
- Select the Paste Special option.
- From the dialog box, mark the Multiply option.
- Press OK.
The entire column has been multiplied by 3.
Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)
Method 2 – Applying the Product Function
We have put number 3 in the H5 Cell which will be used to multiply the data of Column C.
Steps:
- In Cell F5, apply the following formula:
=PRODUCT(E5,$H$5)
- E5 = the First number to multiply
- H5 = the Second number to multiply
- Hit Enter.
- Copy the formula for every cell of that column by double-clicking the Fill Handle tool of that cell or by dragging the icon.
Read More: How to Multiply a Column in Excel by a Constant (4 Easy Ways)
Similar Readings
- How to Multiply from Different Sheets in Excel (5 Methods)
- If Cell Contains Value Then Multiply Using Excel Formula (3 Examples)
- How to Multiply Multiple Cells in Excel (4 Methods)
- Round a Multiplication Formula in Excel (5 Easy Methods)
Method 3 – Using VBA Code
Steps:
- Copy the values of Column C (i.e. Target 2019) into Column F (i.e. Target 2022).
- Press Alt + F11 to open the VBA window.
- Click Insert from the window and select Module.
- Assign the following code in the Module window.
Code:
Sub Multiply()
Range("H5").Copy
Range("F5:F14").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
End Sub
- Run the code and you will see the result.
How to Multiply a Cell by a Number in Excel
Steps:
- Double-click on cell F5 to activate it.
- Apply the following formula in that cell.
=E5*3- E5 = cell which we need to multiply
- 3 = multiplication factor
- Press Enter, and the cell will multiply the number.
- Drag or double-click the Fill Handle from F5 to Autofill the formula down.
All the cells in column F will be multiplied by 3.
Read More: How to Multiply One Cell by Multiple Cells in Excel (4 Ways)
How to Multiply Each Cell Entry by a Number Automatically
Steps:
- Select the Cell F5 and insert the following formula:
=E5*$H$5- E5 = cell which we need to multiply
- H5 = multiplication factor
We have used an Absolute Reference because we don’t want to change the value.
- Hit the Enter button, and you will get the consequence.
- Copy the formula for every cell of that column by double-clicking the Fill Handle tool or by clicking and dragging.
Read More: How to Multiply Columns in Excel (9 Useful and Easy Ways)
Related Articles
- How to Make Multiplication Table in Excel (4 Methods)
- Multiply Rows in Excel (4 Easiest Ways)
- How to Use Multiply Sign in Excel (With 3 Alternative Methods)
- Multiply by Percentage in Excel (4 Easy Ways)
- How to Multiply Two Columns and Then Sum in Excel (3 Examples)
