Division Formula with Absolute Reference in Excel

The absolute reference comes in 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 with Absolute Reference in Excel: 4 Types

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

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.

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.

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.

Download Practice Workbook

Divison with Absolute Reference.xlsx

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.