Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Fill down error
Excel & Google SheetsWhat Fill down means
When you fill a formula down a column, relative references like A1 increment automatically to A2, A3, and so on. If a reference is fully locked with '$' signs or the fill does not behave as expected, the formula copies the same fixed values instead of adjusting to each row.
Common causes
- Row references are accidentally fully locked (e.g., $A$1) so every copied cell refers to the same row instead of advancing.
- The fill handle was dragged but the cells below were already populated with values, so Excel defaulted to a copy rather than a fill.
- In Google Sheets, the column used for the fill contains a mix of formula cells and blank cells, breaking the fill's detection of the intended pattern.
- The formula references a named range or table column that is already absolute by definition, so dragging produces no row shift.
- The cell format in the destination rows is set to Text, so copied formulas display as text rather than computing results.
Example fix
Broken
=VLOOKUP($A$2,$D:$E,2,0)Fixed
=VLOOKUP($A2,$D:$E,2,0)Removing the '$' before the row number '2' makes the lookup value row-relative. When the formula is filled down, $A2 becomes $A3, $A4, and so on, while the lookup table columns $D:$E remain fixed.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Inspect the formula for unnecessary '$' before row numbers. Change $A$1 to $A1 to lock only the column and allow the row to increment as you fill down.
- 2To fill correctly in Excel: select the source cell and the destination range together, then press Ctrl+D. Alternatively, double-click the fill handle to auto-fill to the last contiguous row.
- 3In Google Sheets: select the source cell, hover over the bottom-right corner to get the fill handle cursor, then drag. If the handle is not visible, use Edit > Fill > Fill Down (Ctrl+D).
- 4If destination cells are formatted as Text, select the range, change format to General (Format > Number > Automatic in Sheets, Home > General in Excel), then fill again.
- 5After filling, spot-check row 2 and the last row — click each cell and verify that the row number in the reference bar incremented correctly.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Fill down and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
How do I quickly convert all $A$1-style locks to $A1 in a large range?
Select the range, press Ctrl+H (Find & Replace), search for '$A$' and replace with '$A'. Repeat for every column letter that appears in your formulas. This is faster than editing each cell manually.
Why does double-clicking the fill handle stop before it reaches the last row?
The double-click auto-fill stops at the last row that has data in the adjacent column. If there is a blank cell in the neighboring column, the fill stops there. Manually drag the fill handle past that gap.
In Google Sheets, my formula fills down but gives the same result in every row — is this an ArrayFormula issue?
If the formula is wrapped in ARRAYFORMULA, it expands automatically and should not be dragged. Dragging an ARRAYFORMULA cell creates duplicate spill conflicts. Remove the ARRAYFORMULA wrapper if you plan to fill manually.