# Division Formula with Absolute Reference in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. ## 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. ### 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. ## 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 #### Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  