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 File


4 Ways to Multiply a Column in Excel by a Constant

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.

A Data Set 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 in Excel by a Constant 

This is the easiest method.

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 F, F4. It is called Increased Salary.  

First Cell Selected to Multiply a Column in Excel by a Constant

Then directly write the multiplication formula that you want to execute in the formula bar.

Here I have written =E4*3.

Then press Enter.

We see the multiplication product has been written down in cell F4, $30000.00

Now we want all the cells of column F to have the three times product of adjacent cells of column E.

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.

Dragging Formula to Multiply a Column in Excel by a Constant

Thus you have multiplied the whole column by 3.


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

You can also perform the multiplication operation by a constant by using 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.

First of all, select a new cell that you want to use as an Absolute Cell.

And then put the constant which you want to multiply there. Here I am selecting cell A12, and putting 3 there.

The Absolute Cell Reference of cell A12 is $A$12.

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 F4 and write =E4*$A$12.

See the image below. Cell F4 has the product of E4 and A12, $30000.00.

Making an 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.

Thus you have multiplied the whole column by 3.


3.  Use the PRODUCT Function to Multiply a Column in Excel 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.

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 F4 and inserting there =PRODUCT(E4,3)

Using 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. The formula will be copied to all the cells and they will also be filled with the products.

Dragging the Fill Handle to Multiply a Column in Excel by a Constant


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

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

For example, we have multiplied column E with 3 in column F.

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 E in column F.

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 A12 and copying it.

Copy a Cell Content to Multiply a Column in Excel by a Constant

Now select the column on which you want to apply the multiplication operation. Here I am selecting column F.

Selecting a Column to Multiply a Column in Excel by a Constant

Now right-click on your mouse. Choose Paste Special>Paste Special.

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.

Paste Dialogue Box 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 F have been multiplied by 3.

All the Cells to Multiply a Column in Excel by a Constant

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.

Number Format of a Column is Changed to Multiply the Column in Excel by a Constant


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

How to Multiply Two Columns and then Sum in Excel

Multiply by Percentage in Excel

How to Multiply a Column by a Number in Excel

Multiplication Formula in Excel

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

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