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.
Watch Video – 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.
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. Creating an Excel Formula 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.
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.
2. Using 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*3See the image below. Cell G4 has the product of F4 and C13, $30000.00.
- 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.
Similar Readings
- How to Multiply by Percentage in Excel
- How to Multiply Two Columns and Then Sum in Excel
- How to Multiply from Different Sheets in Excel
- How to Divide and Multiply in One Excel Formula
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:
- 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)
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.
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.
Here I have made a copy of Column F in Column G.
Steps:
- Now write the constant number that 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.
- 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.
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 select the output column, click the General option, and choose Currency($) from there.
I will find the output changed in Currency($) format, which I want.
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.
