Formula Errors

Fix Excel #DIV/0! Errors When Dragging a Division Formula Down

Natalie TaylorNatalie Taylor Nov 20, 2026 869 views

Question details

The user needs a division formula to work when dragged down a spreadsheet.

Fix Excel #DIV/0! Errors When Dragging a Division Formula Down
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.
Before you start

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.

Solution 1Recommended

Lock the divisor with an absolute reference

Use this method when every value in column A should be divided by the constant in B1.

1
Check the divisor

Select cell B1 and enter a nonzero divisor if it is blank or zero.

2
Enter the formula

In the first result cell, such as C1, enter =A1/$B$1 and press Enter.

3
Fill the formula down

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.

Lock the divisor with an absolute reference
Lock the correct cell: The dollar signs keep both the column and row of B1 fixed. An absolute reference will not prevent #DIV/0! if B1 is blank or zero.
Work with formulas in WPS Spreadsheet

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. 1. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Check the divisor: Select B1 and enter a nonzero value if all rows should use the same divisor.
  3. 3. Enter an absolute reference: In C1, enter =A1/$B$1 and press Enter to divide A1 by the fixed value in B1.
  4. 4. Fill down and save: Drag the fill handle from C1 down the result column, then select Save to keep your changes.
Lock a divisor while filling division formulas downHandle blank or zero divisors with spreadsheet functionsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.