Fix Excel #DIV/0! Errors When Dragging a Division Formula Down
Question details
The user needs a division formula to work when dragged down a spreadsheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A formula such as =A1/B1 is dragged down to calculate results for subsequent rows.
- Observed behavior
- The divisor reference changes to B2, B3, and so on. Blank divisor cells are treated as zero, producing #DIV/0! errors.
Check whether every row should be divided by the same value in B1 or by its own value in column B. Make sure the intended divisor is not blank or zero.
Lock the divisor with an absolute reference
Use this method when every value in column A should be divided by the constant in B1.
Select cell B1 and enter a nonzero divisor if it is blank or zero.
In the first result cell, such as C1, enter =A1/$B$1 and press Enter.
Select C1 and drag the fill handle at its lower-right corner down the column. The A reference changes for each row, while $B$1 stays fixed.

Use row-specific divisors and handle blank or zero cells
Use this method when each row should be divided by its corresponding value in column B.
Fix division formulas in WPS Spreadsheet
Use absolute references or check for blank and zero divisors in WPS Spreadsheet, then fill the formula down your worksheet.
- 1. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Check the divisor: Select B1 and enter a nonzero value if all rows should use the same divisor.
- 3. Enter an absolute reference: In C1, enter =A1/$B$1 and press Enter to divide A1 by the fixed value in B1.
- 4. Fill down and save: Drag the fill handle from C1 down the result column, then select Save to keep your changes.

Frequently Asked Questions
Why does dragging my formula change B1 to B2?
B1 is a relative reference, so Excel adjusts it as you fill the formula down. Change it to $B$1 to keep the divisor fixed.
What if B1 itself is blank or zero?
Enter a nonzero divisor in B1, or use a check such as =IF(OR(B1="",B1=0),"",A1/B1) when each row has its own divisor.
Can I show zero instead of #DIV/0!?
Enter =IFERROR(A1/B1,0) in the result cell and fill it down. This also replaces other formula errors with zero.
How can I keep the divisor fixed but change the numerator?
Enter =A1/$B$1 in the first result cell, then drag the fill handle down. The numerator changes to A2, A3, and so on, while the divisor remains B1.




