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

We often have to multiply a column in Excel by a constant while working. We have to perform various types of multiplication like multiplying cells between two columns, or more columns, or multiplying the cells of a column by a constant, etc. Here in this article, I am showing four easy ways to multiply the cells of a column by a constant in Excel.


Download Practice Workbook


4 Ways to Multiply a Column by a Constant in Excel

Let us use the following dataset. We have the employee records of a company named Sunflower Group.

We have the First Names of the employees, the Last Names, their Starting Dates, work Hours Per Day, and Salaries. Look at the dataset first.

How to Multiply a Column in Excel by a Constant

Now for some reason, the chief of the company wants to increase the salary of each employee three times.

That means, all the cells of Column E are to be multiplied by a constant number, 3.

How can we do that? Here I am showing four easy ways to do that.


1. Insert Formula in the Formula Bar to Multiply a Column by a Constant 

This is the easiest method.

Steps:

  • First of all, select the first cell of a different column where you want to write down the multiplied numbers.
  • Here I have selected the first cell of Column G, G4. It is called Increased Salary.  
  • Then directly write the multiplication formula that you want to execute in the formula bar:
=F5*3
  • Then press Enter.

We see the multiplication product has been written down in Cell G4, $30000.

Insert Formula in the Formula Bar to Multiply a Column in Excel by a Constant

Now we want all the cells of Column G to have the three times product of adjacent cells of the Column F.

  • This is easy. Move your cursor over the rightmost bottom corner of the first Cell F4 and you will find the Fill Handle (a small plus(+) sign). Double-click it. Or drag it through the columns.

You will see all the cells have been filled with the product. Thus you have multiplied the whole column by 3.

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


2. Use Absolute Cell Reference to Multiply a Column by a Constant 

You can also perform the multiplication operation by a constant by using an Absolute Cell Reference.

Now, what is an Absolute Cell Reference?

Absolute Cell Reference: An Absolute Cell Reference is a cell reference having a Dollar sign ($) before the column number and row number of it.

When you use a cell reference in a formula in another cell, and then drag the formula in the cell through row or column, the cell reference automatically increases through row or column.

But if we use Absolute Cell Reference, it will remain fixed. It will not increase row or column-wise.

Steps:

  • First of all, select a new cell that you want to use as an Absolute Cell.
  • And then put the constant that you want to multiply there. Here I am selecting Cell C13, and putting 3 there. The Absolute Cell Reference of Cell C13 is $C$13.
  • Now go to the first cell of the column where you want to write down the multiplication product. Then enter the multiplication formula there, using the Absolute Cell Reference. Then click Enter. Here I am going to Cell G4 and writing:
=F5*3

See the image below. Cell G4 has the product of F4 and C13, $30000.00.

Use Absolute Cell Reference to Multiply a Column in Excel by a Constant

  • Now move your mouse cursor to the rightmost bottom corner of the first cell and double-click on the Fill Handle (The small plus(+) sign). Or drag the Fill Handle through the column. All the cells of the column will be filled in this way.

Use Fill Handle Tool to Multiply a Column in Excel by a Constant

Thus you have multiplied the whole column by 3.

Read More: How to Round a Multiplication Formula in Excel (5 Easy Methods)


Similar Readings


3. Use PRODUCT Function to Multiply a Column by a Constant 

Excel provides a built-in function called PRODUCT.

It takes two or more numbers or cell references as arguments and gives its product as output.

For example, PRODUCT(2,3)=6.

Steps:

  • First of all, we select the first cell of the column where we want to put the product.
  • And then write down the formula there with the required cell references and numbers. Then click Enter. Here I am going to Cell G4 and inserting the formula:
=PRODUCT(F5,3)

Use the PRODUCT Function to Multiply a Column in Excel by a Constant

Then you have to copy this formula through the whole column.

  • Move your mouse cursor to the rightmost bottom corner of the first cell and double-click on the Fill Handle (the small plus(+) sign). Or drag the Fill Handle through the column.

Use Fill Handle Tool to Multiply a Column in Excel by a Constant

The formula will be copied to all the cells and they will also be filled with the products.

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


4. Run the Paste Special Menu to Multiply a Column by a Constant 

Up until now, we have multiplied a column with a constant in a different column.

For example, we have multiplied Column F with 3 in Column G.

But in this method, the original column is multiplied and changed.

So, if you do not want to change the original column, select it, press Ctrl + C, and make a copy of it in another column.

Here I have made a copy of Column F in Column G.

Run the Paste Special Menu to Multiply a Column in Excel by a Constant

Steps:

  • Now write the constant number which you want to multiply in another cell.
  • Then select it and press Ctrl + C to copy it. Here I am writing 3 in Cell C13 and copying it.
  • Now select the column on which you want to apply the multiplication operation. Here I am selecting Column G.
  • Now right-click on your mouse. Choose Paste Special.

Select Paste Special Menu to Multiply a Column in Excel by a Constant

  • You will have a dialogue box like this. From the Paste menu, check All. From the Operation menu, check Multiply. Then click OK. See the image below.

Select Multiply Option from the Paste Special Menu to Multiply a Column in Excel by a Constant

You will see all the cells of your selected column have been multiplied by the copied number.

Here all the cells of Column G have been multiplied by 3.

But there is a limitation here.

By default, Excel produces output as General Text format by this operation.

  • If you want to change the format of the output, you have to manually do this by going to the General option of the Excel Toolbar under the Home tab. Home>General.

See the following image.

Here I want to get the output in Currency($) format.

  • So I am selecting the output column, clicking the General option, and choosing Currency($) from there.

I will find the output changed in Currency($) format, which I want.

Read More: How to Multiply a Column by a Number in Excel (4 Easy Methods)


Conclusion

Using these methods, you can easily multiply any column in Excel with a constant. This is really very easy, isn’t it? Do you know any other option? Let us know in the comment section.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo