Mahbubur Rahman
https://www.exceldemy.com/divide-columns-in-excel/The absolute reference comes very handy when we want to fix a cell. An absolute reference contains a dollar sign ($).In this article, we will see different methods of Division Formula in Excel with Absolute Reference. We will use a sample dataset for your better understanding.
Download Practice Workbook
4 Types of Division Formula with Absolute Reference in Excel
Here, we will see how to divide numbers or values by a constant number using two traditional methods, the QUOTIENT function, and the MOD function.
Formula 1: Divide Column Numbers by a Constant Number
So, we have sales amounts for different products and a constant quantity. From here, we will see how to use an absolute reference to lock quantity value and calculate the price of each product.
Steps:
- First, type the following formula in cell F5.
=D5/$E$5
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
- If we click on a random cell in the Price column we will see that Divisor is constant as we used at the beginning of the calculation, which is $E$5.
Read More: How to divide columns in Excel (Top 8 easy & quick ways)
Formula 2: Divide a Constant Number by Column Numbers
This time, we will divide a constant number by different column numbers. We have got a fixed amount of sales value, but our quantity is different for each product. Let’s calculate the price for each product.
Steps:
- First, type the following formula in cell F5.
=$D$5/E5
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
That’s it. Click on any cell in the price column and you will see the cell reference $D$5 is fixed in every calculation.
Read More: Division formula in Excel for multiple cells
Similar Readings
- How to Divide Without Using a Function in Excel (with Quick Steps)
- Divide for Entire Row in Excel (6 Simple Methods)
- How to Divide with Decimals in Excel (5 Suitable Examples)
Formula 3: Divide Number with Absolute Reference Using QUOTIENT Function
Another simple method to do division is the QUOTIENT function. Let’s see how this works.
Steps:
- First, type the following formula in cell F5.
=QUOTIENT(D5,$E$5)
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
That’s it. We’ve divided the Sales column values by the fixed Quantity value.
Read More: How to Divide in Excel for Entire Column (7 Quick Tricks)
Formula 4: MOD Function to Divide Number with Absolute Reference
Division formulas do not always return integer results. Occasionally, we must also locate the remains. The remaining value in integer format is returned by the MOD function. Assume we have some fruits and want to share them with a few people. We also want to know how much is left.
Steps:
- First, type the following formula in cell F5.
=MOD(C5,$D$5)
- Now, press the ENTER key.
- Finally, drag down to AutoFill rest of the series.
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
That’s all for the article. These are 4 different methods for Division Formula in Excel with Absolute Reference. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- How to Divide a Value to Get a Percentage in Excel (5 Suitable Examples)
- [Fixed] Division Formula Not Working in Excel (6 Possible Solutions)
- How to Divide a Number by a Percentage in Excel (3 Suitable Examples)
- Add and Then Divide in Excel (5 Suitable Examples)
- How to Divide One Column by Another in Excel (7 Quick Ways)
