Division Formula with Absolute Reference 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.

Division Formula in Excel with Absolute Reference


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.

Division Formula in Excel with Absolute Reference by constant number

  • Finally, drag down to AutoFill rest of the series.

Division Formula in Excel with Absolute Reference by constant number

  • 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

Division Formula in Excel with Absolute Reference by constant number

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

Division Formula in Excel with Absolute Reference by constant number

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


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)

Division Formula in Excel with Absolute Reference by QUOTUENT function

  • Now, press the ENTER key.

Division Formula in Excel with Absolute Reference by QUOTUENT function

  • 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.

Division Formula in Excel with Absolute Reference by MOD function

Steps:

  • First, type the following formula in cell F5.
=MOD(C5,$D$5)

Division Formula in Excel with Absolute Reference by MOD function

  • 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.

Division Formula in Excel with Absolute Reference


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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo