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.

**Table of Contents**Expand

## 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**.

**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`

- 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: **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)`

- 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**

## 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 Add and Then Divide in Excel
- How to Divide with Decimals in Excel
- How to Divide a Number by a Percentage in Excel
- Formula for Addition Subtraction Multiplication and Division in Excel
- [Fixed] Division Formula Not Working in Excel

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