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

Watch Video – Multiply a Column by a Constant in Excel

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

We want 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.

Method 1 – Creating an Excel Formula to Multiply a Column by a Constant

Steps:

Select the first cell of a different column where you want to enter the multiplied numbers.
We have selected the first cell of Column G, G4. It is called Increased Salary.
Enter the multiplication formula that you want to execute in the formula bar:

=F5*3

Press Enter.

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

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

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

All the cells will be filled.

Method 2 – Using Absolute Cell Reference to Multiply a Column by a Constant

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 drag the formula in the cell through row or column, the cell reference automatically increases through row or column.

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

Steps:

Select a new cell that you want to use as an Absolute Cell.
Enter the constant that you want to multiply. We selected Cell C13, and put 3 there. The Absolute Cell Reference of Cell C13 is $C$13.
Go to the first cell of the column where you want to enter the multiplication product. Enter the multiplication formula there, using the Absolute Cell Reference.

=F5*3

Press Enter.

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

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.

You have multiplied the whole column by 3.

Similar Readings

Method 3 – Inserting PRODUCT Function for Multiplying a Column by a Constant in Excel

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:

Select the first cell of the column where you want to put the product. We selected Cell G5.
Enter the formula there with the required cell references and numbers.

=PRODUCT(F5,3)

Press Enter.

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.

Method 4 – Multiplying a Column by a Constant Using the Paste Special Menu

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.

We have made a copy of Column F in Column G.

Steps:

Enter the constant number that you want to multiply in another cell.
Select it and press Ctrl + C to copy it. We entered 3 in Cell C13 and copied it.
Select the column on which you want to apply the multiplication operation. We selected Column G.
Right-click on your mouse. Choose Paste Special.

A dialog box will open.

From the Paste menu, check From the Operation menu, check Multiply.
Click OK.

All the cells of your selected column will be multiplied by the copied number.

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.

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