Abrar
While working with Excel, we often need to multiply a column by a constant. Although this is a straightforward approach, there isn’t only one way of doing this. In this article, we will be discussing different approaches to multiply a column by a Constant in Excel.
📁 Download Practice Workbook
Download the practice workbook below.
Learn to Multiply a Column in Excel by a Constant with These 5 Approaches
To demonstrate how we can multiply a column in Excel by a constant, we consider a dataset where the present prices of different fruits are given in one column. In another column, we have the new price that is supposed to have a 20% increment over the previous price. So, essentially, we have to multiply the data by 1.2 to get the new price. We can multiply a column by the constant 1.2 in these 5 different ways. The dataset is shown as follows:
1. Using PRODUCT Function
Excel has a built-in PRODUCT function that multiplies the numbers in arguments and returns the product. The basic syntax of the PRODUCT Function is as follows:
PRODUCT(number1, [number2], ...)Where, number1 (Required)= The first number or range of numbers that we want to multiply.
number2,…(Optional)= Additional number or number ranges that we want to multiply, up to 255 arguments.
To use the PRODUCT Function in our dataset to multiply the present prices with 1.2, we need to follow these steps:
⬇️⬇️ STEPS ⬇️⬇️
- First, we select cell D6.
- After that, we go to the formula tab and write the following formula:
=PRODUCT(C6,1.2)
- Thus, we will see the new price in cell D6 where 1.2 is multiplied by the previous price in cell C6.
- Now, we go to the bottom right of cell D6 and drag the Fill Handle icon up to cell D12.
- The new price results will look like the image below:
2. Inserting Formula in Formula Bar
Alternatively, we can write the formula directly in the formula bar to get similar results. The steps in this approach are as follows:
⬇️⬇️ STEPS ⬇️⬇️
- We select cell D6 at first.
- We move over to the formula bar, place our cursor there and write the following formula
=C6*1.2
- After that, we press Enter.
- Cell D6 will show the increased new price. Now, we drag the Fill Handle icon down to cell D12.
- The results will look like the following image:
3. Utilizing Paste Special Feature
In this method, we use the Paste Special feature to multiply a column in Excel by a constant. Here, we use the 1.2 value as a constant to multiply a certain column.
⬇️⬇️ STEPS ⬇️⬇️
- We write the constant value 1.2 in C14.
- First, we select the cells C6:C12 and then copy the values to the cells D6:D12.
- Now we select the cell C14 and copy the value.
- After that we select D6:D12, right click and select Paste Special option.
- A new pop-up box will appear named Paste Special.
- We select Values from the Paste section Multiply from the Operation section and press OK.
- The results will look like the image below:
📕 Read More: How to Multiply Two Columns and Then Sum in Excel
4. Applying Absolute Cell Reference
In this method, we apply the absolute cell reference.
⬇️⬇️ STEPS ⬇️⬇️
- First, we select cell D6.
- After that, we go to the formula tab and write the following formula:
=C6*$C$14
- Here, the value in cell C14 is absolutely referenced with the $ sign.
- Thus, we will see the new price in cell D6.
- Now, we go to the bottom right of cell D6 and drag the Fill Handle icon up to cell D12.
- The new price results will look like the image below:
5. Using Define Name Option
We can use the Named Range option in Excel to create a constant 1.2 by naming the constant. We need to follow these steps for this approach:
⬇️⬇️ STEPS ⬇️⬇️
- First, we go to the Formulas Tab.
- Then, we go to Define Name.
- After that, a New Name box will appear. We fill the Name field by writing Constant and we input in the box Refers to 1.2 and press OK.
- Then, we move to the cell D6 and write the following formula by calling the Named Range Constant and therefore writing the following formula:
=C6*Constant
- Therefore, cell C6 will change its value as multiplied by 1.2 as shown below
- After that, we drag the fill handle across cell D12.
- The final results will be like the following image
📄 Important Notes
🖊️ In the Named Range option, if we specify an entry in the Define Name option, we can’t specify another constant in the same name.
🖊️ The PRODUCT Function can multiply two numbers or two number ranges.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 A column can be multiplied in Excel by writing formulas in the formula tab in different ways.
📌 Similarly, the PRODUCT Function can also be used to call an inbuilt function in Excel to multiply a column with any value.
📌 The Define Name option is suitable to create a constant that can be called in formulas later.
Conclusion
To sum up, these are the ways in which we can multiply a column in Excel by a constant. If you have any questions, feel free to ask them in the comment section. If you want to know more, follow Excelden for more Excel-related articles.
