Excel drag formula not working: 6 fixes

Fix 1: Every row shows the same answer (calculation is Manual)

You drag =B2*C2*(1-$H$1) from D2 down to D9, and every row says 117, the first row's answer. Click D5 and the formula bar shows =B5*C5*(1-$H$1), so the formula did copy correctly. Excel just hasn't calculated it.

That happens when calculation is set to Manual. It often gets switched without anyone noticing: Excel takes the calculation mode from the first workbook you open, so if that file was saved in Manual, every workbook you open after it in the same session is Manual too. The status bar shows "Calculate" while there are formulas waiting.

1. Go to Formulas > Calculation Options.
2. Choose Automatic.
3. The dragged cells update straight away. Press F9 if you need to stay on Manual for a large file and just want to recalculate now.

Fix 2: The results are wrong after row 2 (lock the reference with $)

Here every line gets the 10% discount in H1. If D2 says =B2*C2*(1-H1) and you drag it down, Excel moves every reference one row per row. D3 becomes =B3*C3*(1-H2), and H2 is empty, so the monitor stand gets no discount: $96 instead of $86.40. Nothing shows an error; the totals are just wrong.

Put $ signs on any cell every row should share: =B2*C2*(1-$H$1). Click inside the reference in the formula bar and press F4 to add them. The absolute reference guide covers mixed references like $H1 and H$1 for formulas you drag both down and across.

Fix 3: The fill handle is missing or won't drag

The fill handle is the small square at the bottom-right corner of the selected cell. If it isn't there, or dragging moves nothing, the option is off.

If the sheet is protected, Excel won't let you fill into locked cells either. Go to Review > Unprotect Sheet first, if you have the password.

1. Go to File > Options > Advanced.
2. Under Editing options, tick Enable fill handle and cell drag-and-drop.
3. Click OK and try dragging again.

Fix 4: Double-clicking fills too few rows (or none)

Double-clicking the fill handle fills down as far as the data in the next column goes, and it stops at the first empty cell there. If one item has no price yet, the fill stops above it. If the columns on both sides of the formula are empty, nothing happens.

For a long range, select it first: click D2, type D2:D500 in the Name Box (left of the formula bar), press Enter, then Ctrl+D to fill down. Or type the formula into the whole selection at once with Ctrl+Enter. Both fill every selected cell, gaps or not.

1. Select D2 down to the last row you need.
2. Press Ctrl+D to copy the top cell's formula into the rest.
3. Check the last row's formula in the formula bar to make sure it reached the end.

Fix 5: It counts up when you wanted a copy (or the other way round)

Formulas always adjust their references when you drag. Plain values follow different rules: dragging one number copies it (4, 4, 4), while two numbers in a row like 1 and 2 continue the pattern. Dates and text with a number, like "Week 1", count up.

After any drag, a small Auto Fill Options button appears at the bottom-right corner of the filled range. Click it and choose Copy Cells or Fill Series to switch. Holding Ctrl while you drag also switches between copying and counting.

If a cell shows the formula text itself instead of a result, the cell was formatted as Text before you typed it. That one has its own guide.

Fix 6: Skip the dragging (tables and Excel 365)

Two ways to avoid the problem altogether. Turn the list into a table with Ctrl+T: type the formula once in the first row and Excel fills the whole column, and fills new rows as you add them. If that doesn't happen, check File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type > Fill formulas in tables to create calculated columns.

In Excel 365 or 2021, one formula can work on the whole column: =B2:B9*C2:C9*(1-H1) in D2 spills eight line totals with nothing to drag. H1 can stay relative here because the formula is never copied. Delete the old formulas in D3:D9 first, or the spill hits them and shows #SPILL!.