Division Formula with Absolute Reference in Excel

Get FREE Advanced Excel Exercises with Solutions!

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 in Excel with Absolute Reference


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.

Division Formula in Excel with Absolute Reference by constant number

  • Finally, drag down to AutoFill the 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 Create a Division Formula in Excel


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: How to Divide Without Using a Function in Excel


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.


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


Download Practice Workbook


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


<< Go Back to Divide in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo