How to Multiply a Column by a Number in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

We often use multiplication in Excel. There are several ways for performing this task. In this article, I’ll show 3 easy methods to multiply a column by a number in Excel.


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.

Dataset to Multiply a Column by a Number in Excel

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.

Steps:

➤ 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.

How to Multiply a Column by a Number in Excel

➤ 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.

How to Multiply a Column by a Number in Excel

Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)


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.

Steps:

➤ In Cell F5, type the following formula:

=PRODUCT(E5,$H$5)

Here.

  • E5 = the First number to multiply
  • H5 = the Second number to multiply

Note: Absolute Reference has been used for cell H5 as it won’t change further.

➤ 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 a Column by a Number in Excel

Read More: How to Multiply a Column in Excel by a Constant (4 Easy Ways)


Similar Readings


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.

Steps:

➤ 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.

Code:

Sub Multiply()

Range("H5").Copy

    Range("F5:F14").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

End Sub

How to Multiply a Column by a Number in Excel

➤ 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.

Steps: 

➤ First, double-click on cell  F5 to activate it.

➤ Then, type the following formula in that cell.

=E5*3

Here.

  • E5 = cell which we need to multiply
  • 3 = multiplication factor

➤ Next, press ENTER, and the cell will multiply the number.

➤ Then, hold the Fill Handle tool located at the right-end corner of cell F5. Now, Drag or just double-click the cell to Autofill the formula down.

➤ Hence, all the cells in column F will be multiplied by the number-> 3.

How to Multiply a Column by a Number in Excel

Read More: How to Multiply One Cell by Multiple Cells in Excel (4 Ways)


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.

Steps:

➤ Activate the Cell F5 and type the following formula:

=E5*$H$5

Here,

  • 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.

How to Multiply a Column by a Number in Excel

➤ 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.

Read More: How to Multiply Columns in Excel (9 Useful and Easy Ways)


Conclusion

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo