How to Multiply Column by a Constant in Excel

This guide will discuss how to multiply a column by a constant in Excel using three easy and simple ways.

Since it has many tools and functions we can utilize for several calculations, Excel is an excellent tool to use for different calculations. So we can perform the most difficult calculation efficiently in Excel with the help of the different built-in functions. 

But, we can also simply use Excel to do basic calculations, making the process go by faster. In this case, we will discuss how to multiply a column by a constant in Excel. 

So there are three ways we can go about doing this task. Firstly, we can perform the formula in Excel. Secondly, we can utilize absolute reference when doing the calculations. Thirdly, we can also use the PRODUCT function to multiply the column by a constant. 

Additionally, we can also use a method that will preserve the original values by copying and pasting the column. But, this entails copying and pasting the column before doing the calculations. 

Let’s take a sample scenario wherein we must multiply a column by a constant in Excel. 

Suppose you have a data set containing the employee’s name and each of their salary. And you were asked by your boss to calculate each employee’s salary if it was to be multiplied by 2. So you opted to do this task in Excel to make the process faster and more efficient. 

Great! Before we move on and learn the three methods we can use to multiply a column by a constant in Excel, let’s first explain how to write the PRODUCT function in Excel.

The Anatomy of the PRODUCT Function

The syntax or the way we write the PRODUCT function is as follows:

=PRODUCT(number1, [number2])

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • PRODUCT() refers to our PRODUCT function. And this function is used to multiply all numbers we have given as arguments.
  • number1 is a required argument. And this refers to 1 to 255 numbers, logical values, or a text representation of numbers we want to multiply.
  • number2 is an optional argument. So this refers to 1 to 255 numbers, logical values, or text representations of numbers we want to multiply. 

Amazing! Now we can dive into the three easy and simple methods we can use to multiply a column by a constant in Excel.

How to Multiply Column by a Constant in Excel using Basic Formula

Firstly, we can perform the formula of multiplying a column by a constant in Excel. So this is the easiest and simplest method to utilize. And we need to insert the formula in the formula bar to perform the calculation. So the formula to multiply a column by a constant in Excel is =cell*constant.

For example, we have a data set containing the employee name and salary of each employee. Since we want to increase the salary by 2, our constant value in this situation is 2. Basically, we only need to multiply the salary by 2.

To use this method, we can follow the steps discussed below:

1. Firstly, we will select a new column to input the results. Then, we will type an equal sign to start the formula. Next, we need to select the cell containing the salary and multiply it by the constant 2. So our entire formula would be “=C3*2”. Lastly, we will press the Enter key to return the result.

Multiply Column by a Constant in Excel

 

2. Secondly, we will drag down the Fill Handle tool to copy the formula and apply it to the other cells.

Fill handle tool

 

3. And tada! We have successfully multiplied a column by a constant in Excel.

Final output

 

 

How to Multiply Column by a Constant in Excel using Absolute Reference

Secondly, we can utilize the absolute reference in our basic formula. So an absolute cell reference is inserting a dollar sign before the column number and row number to lock the cell. And this ensures that the selected value will not change when we drag down the formula to copy.

So this method uses the same basic formula. But, the only difference is we insert an absolute reference on our selected constant. Thus, our formula would be =$cell$*constant.

For instance, our constant of 2 is inputted in a different cell. So we will use that cell reference instead of inputting the value 2 in our formula.

To learn how to use this method, let’s follow the step-by-step process below. 

1. Firstly, we will input the constant we will use in a new cell. In this case, we will input “2” in cell F2. Then, we can type the formula “=C3*$F$2”. Lastly, press the Enter key to return the result.

Multiply Column by a Constant in Excel

 

2. Secondly, we can drag down the Fill Handle tool to copy the formula and apply it to the other cells.

Copy the formula

 

3. And tada! We have successfully multiplied a column by a constant in Excel.

Final data set

 

 

How to Multiply Column by a Constant in Excel using the PRODUCT Function

Thirdly, we can use the PRODUCT function to multiply a column by a constant in Excel. So the PRODUCT function is used to multiply all the numbers we have selected.

Essentially, we only need to select the cell references containing the numbers we want to multiply and input the constant.

To learn how to use the PRODUCT function, we can follow the steps below:

1. Firstly, we will input the function in a new column. So type in the formula “=PRODUCT(C4,2)”. Then, we will press the Enter key to return the result.

Multiply Column by a Constant in Excel

 

2. Secondly, we will drag down the formula to copy and apply it to the other cells.

Multiply Column by a Constant in Excel

 

3. And tada! We have successfully used the PRODUCT function to multiply a column by a constant in Excel.

Multiply Column by a Constant in Excel

 

You can make your own copy of the spreadsheet above using the link attached below. 

And that’s pretty much it! We have discussed how to multiply a column by a constant in Excel using three easy and simple methods. Now you can choose any of the three methods to apply to your own work whenever you need to.

Are you interested in learning more about what Excel can do? You can now use the PRODUCT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like