Download Practice Book
Download the Excel workbook that we’ve used to prepare this article.
3 Easy Methods to Multiply a Column by a Number in Excel
Let’s get introduced to the dataset first. In the following table, I 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. So, our aim is to fill Column F by multiplying Column C with the value “3”.
In this section, you will get introduced to 3 quick and easy methods to multiply a column by a number in Excel.
Method 1: Utilizing Paste Special Option
According to the Paste Special command, I’ll multiply Column E with the number 3. Follow the steps below.
➤ First of all, copy the values of Column C (i.e. Target 2019) into Column F (i.e. Target 2022).
➤ Then, select Cell G5 where the number 3 is located and press CTRL+C to copy the cell value.
➤ After that, select the cells in Column F and right-click on the mouse.
➤ Here, the options will appear in the Context Menu. From the menu, select the Paste Special option.
➤ A dialog box will open up. From the box, mark the Multiply option.
➤ Press OK.
➤ Now see that the full column has been multiplied by the number 3.
Method 2: Applying Product Function
What is the formula for multiplication in Excel for multiple cells? Well… applying functions is always interesting in Excel. Here, we’ll learn to do the operation with the PRODUCT function. I want to apply the function in Cell F5. I have put number 3 in the H5 Cell which will be used to multiply the data of Column C.
➤ In Cell F5, type the following formula:
- E5 = the First number to multiply
- H5 = the Second number to multiply
➤ Now push the ENTER button and get the result.
➤ After that, copy the formula for every cell of that column by double-pressing the Fill Handle tool of that cell or by dragging the mouse with left-click.
- 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
Applying VBA code can also be a possible solution in this regard. This code we will use will act similarly to the Paste Special option.
➤ First, First of all, copy the values of Column C (i.e. Target 2019) into Column F (i.e. Target 2022).
➤ Now, press ALT+F11 to open the VBA window. Here, click Insert from the window and select Module.
➤ Next, assign the following code in the Module window.
Sub Multiply() Range("H5").Copy Range("F5:F14").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply End Sub
➤ After that, Run the code and you will see the result.
How to Multiply a Cell by a Number in Excel
This method is a manual approach. We have to multiply the number with the data of the column manually here.
➤ First, double-click on cell F5 to activate it.
➤ Then, type the following formula in that cell.
- E5 = cell which we need to multiply
- 3 = multiplication factor
➤ Next, press ENTER, and the cell will multiply the number.
➤ Hence, all the cells in column F will be multiplied by the number-> 3.
How to Multiply Each Cell Entry by a Number Automatically
In this method, we’ll use cell references to multiply each cell entry of a column in an Excel sheet using Cell Reference. I want to multiply Column E with Cell G5 which has the value of 3. Let’s see.
➤ Activate the Cell F5 and type the following formula:
- E5 = cell which we need to multiply
- H5 = multiplication factor
Press the F4 key to lock the reference H5. We have used Absolute Reference because we don’t want to change the value.
➤ Next hit the ENTER button, and you will get the consequence.
➤ After that, copy the formula for every cell of that column by double-pressing the Fill Handle tool or by dragging the mouse with left-click.
I hope all of the methods described above will be effective to multiply a column by a number in Excel. Feel free to ask if you have any queries and don’t forget to share your experience. We are always responsive to your feedback.