If an Excel formula stops working when you drag it down, first check that the fill handle is enabled and that the formula’s cell references are set up to change as intended. Then check whether the formula is stored as text, calculation is set to Automatic, or the cells are part of a table or spilled-array result; the right fix depends on what you see.
Start with the symptom
Look at the cells after dragging. Did nothing fill in, did the same formula appear in every row, did the formula text show instead of a result, or did Excel return an error? These outcomes point to different causes. Before changing the formula, click one of the filled cells and inspect the formula bar. Compare its formula with the source cell, and check the displayed result separately.
- Nothing filled: check the fill handle setting, selection, or worksheet protection.
- The formula copied but references did not adjust as expected: inspect dollar signs in the references.
- The cell shows a formula beginning with an equals sign: check text formatting and Show Formulas.
- The formula appears right but the result is old: check calculation mode.
- You see an error or a spill message: inspect the formula inputs, output range, and formula type.
Making a copy of the workbook before experimenting is sensible if it contains important data. For a quick check, try filling a short range in a blank area using a simple formula; that helps distinguish a workbook-wide setting from a problem in one particular cell or range.
Understand how references change when filled
Excel normally adjusts relative references as a formula is copied. This is often what people mean by “dragging down,” but it can look like a failure if the formula contains fixed references or if you expected a different part of it to change.
For example, with quantities in A2:A3 and prices in B2:B3, enter this in C2: =A2*B2. If A2 is 3 and B2 is 4, the result is 12. Dragging the formula to C3 changes it to use A3 and B3; if those cells contain 5 and 4, the result is 20. The formula changes because both references are relative.
A dollar sign locks a reference. With quantities in A2:A3, prices in B2:B3, and a tax rate of 10% in F1, enter this in C2: =B2*$F$1. If B2 is 10, the result is 1 when the formula represents the tax amount. Dragging down changes B2 to B3 but keeps the tax-rate reference at F1. If you lock the wrong reference, the filled formula may repeat an unintended value or calculate from the wrong cell.
References can also be partly locked. For a price list across columns B and C with a tax rate in row 1, a reference such as B$1 keeps row 1 fixed while allowing the column to change. Conversely, $A2 keeps column A fixed while allowing the row number to change. Use the F4 key while editing a reference in a formula on Windows to cycle through relative, absolute, and mixed reference styles; on many Mac keyboards, use Command+T to cycle reference types, though keyboard settings can affect function-key behavior.
Check the fill handle and fill-down method
The fill handle is the small square at the lower-right corner of a selected cell. Dragging it copies the cell’s content or extends a recognizable series, depending on the selection and Excel’s interpretation. If the handle is missing or dragging it does not fill cells, on Windows go to File > Options > Advanced, find Editing options, and make sure Enable fill handle and cell drag-and-drop is checked.
You can fill a range without dragging. Put the formula in the top cell, select that cell and the cells below it, then press Ctrl+D on Windows. On Mac, use Control+D. The top cell supplies the content for the cells underneath, and relative references adjust for each row. You can also use Home > Fill > Down. Make sure the selection starts with the cell containing the formula; otherwise Excel may fill the wrong content down.
If the worksheet is protected, editing or filling cells may be restricted depending on the protection options. Check Review > Unprotect Sheet if that command is available and you have permission to unprotect it. A selected range can also include merged cells or other unusual layouts that prevent a straightforward fill. Test in a normal, unmerged range to see whether the layout is the obstacle.
Fix formulas that are being treated as text
When a cell displays =A2*B2 instead of its calculated value, Excel may be treating the entry as text. This can happen if the cell was formatted as Text before the formula was entered, or if the entry begins with an apostrophe or an extra space. Changing the format to General alone may not convert an existing text entry into a working formula.
To correct it, select the affected cells, choose Home > Number Format > General, then re-enter the formula. For one cell, press F2 and then Enter on Windows; on Mac, the equivalent edit-and-confirm keys can depend on keyboard settings, so double-clicking the cell and pressing Return is an alternative. If many cells contain text formulas, correct a small sample first and confirm the result before applying a bulk conversion.
Another possibility is that Excel is showing formulas across the worksheet rather than calculated values. On Windows, Ctrl+` toggles Show Formulas; the grave-accent key is usually near the upper-left of the keyboard. You can also check Formulas > Show Formulas. If every formula in the sheet is visible as text, this setting is more likely than a problem with each individual formula.
Make sure Excel recalculates the results
A formula can copy correctly while its displayed result remains out of date if calculation is set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and select Automatic. With automatic calculation, Excel recalculates formulas when relevant inputs change. Manual mode can be useful in very large workbooks, but it means a changed input may not immediately update dependent results.
Pressing F9 recalculates formulas in open workbooks when calculation is manual. If the result changes after recalculation, the drag operation was probably not the issue; the workbook simply had not updated. If one formula continues to show an old value, inspect whether it refers to the cells you changed and whether any inputs are themselves stored as text. A formula that recalculates accurately can still return an unexpected answer if its source values or references are wrong.
Account for tables and spilled formulas
Excel tables behave differently from ordinary cell ranges in useful ways. When you enter a formula in a table column, Excel may fill it down as a calculated column and apply it to other rows in that column. Table formulas can use structured references such as column names, so they may not look like ordinary A1-style formulas. If a table column contains different formulas in different rows, inspect the formula in several rows before dragging; replacing the whole column may overwrite intentional exceptions.
Dynamic-array formulas can return several results from one formula cell. In that case, the output cells are a spill range, not separate formulas to drag down. If a message indicates #SPILL!, check whether cells in the intended output area are occupied, merged, or otherwise blocking the result. Clear or move the obstruction if appropriate. Dragging a formula from inside a spill range is not the same as filling an ordinary formula copied one row at a time.
Also distinguish copying a formula from extending a series. Dragging a cell with a number, date, or certain patterns may fill a sequence rather than make an exact copy. If Excel’s result is not what you intended, use the Auto Fill Options button that appears near the filled range to choose an available fill behavior, or use Ctrl+D to copy the top cell down.
Diagnose errors and unexpected results
If filled formulas show errors such as #REF!, #VALUE!, or #DIV/0!, compare the first incorrect row with the last correct row. A relative reference may have shifted onto a blank cell or a different type of value; a copied division formula may also encounter a zero denominator. The error is a clue about the calculation, not proof that dragging itself failed. Select the cell and inspect its references and inputs.
Check for blanks, hidden rows, and inconsistent data types in the source range. A number that looks numeric but is stored as text can behave differently in a calculation. If the same result repeats down the column, make sure the formula actually refers to the row-specific inputs rather than a fixed row, and confirm that the inputs differ. If the formula is identical because all its references are absolute, that may be correct when every row should use the same fixed inputs.
When typing formulas, argument separators can vary with regional settings. The examples in this article use standard Excel syntax with commas where arguments are needed; some regional configurations expect semicolons. That issue generally causes a formula-entry error rather than a drag-specific problem, but it is worth checking if you copied a formula from a different locale or source.
A reliable troubleshooting sequence
Use this order to isolate the cause without changing several settings at once:
- Click the source cell and verify that it contains a formula, not a typed value or text string.
- Inspect the references and decide which row, column, or cell should stay fixed.
- Fill down a few rows, then compare the formulas in the first and last filled cells.
- If nothing fills, check the fill-handle setting, worksheet protection, and selection.
- If formulas display instead of results, check cell formatting and Show Formulas.
- If results are stale, set calculation to Automatic or recalculate with F9.
- If the range is a table or spill range, use the behavior designed for that feature rather than treating it as a regular range.
This sequence also helps avoid a common misconception: a formula that “did not change” is not automatically broken. A fixed reference may be deliberate, and two rows may legitimately produce the same answer. Judge the formula by whether its references match the intended calculation, not just by whether the formula text looks different.
Frequently Asked Questions
Why does Excel copy the same formula when I drag down?
Some or all references may be absolute, marked with dollar signs, or the formula may contain fixed values rather than row references. Compare the formula bar in the source and destination cells. If each row should refer to its own inputs, remove the dollar signs from the references that need to move.
Why is the fill handle not showing in Excel?
On Windows, check File > Options > Advanced and enable Enable fill handle and cell drag-and-drop. If it is already enabled, check whether the sheet is protected or whether the selected cell or range has a layout, such as merged cells, that interferes with filling.
How do I copy a formula down without dragging?
Enter the formula in the first cell, select that cell and the destination cells below it, and press Ctrl+D on Windows or Control+D on Mac. You can also select Home > Fill > Down. The references adjust according to their relative or absolute settings.
Why does Excel show the formula instead of the answer?
The cell may be formatted as Text, the formula may have an apostrophe or space before the equals sign, or Show Formulas may be on. Set the cell to General and re-enter the formula; if all formulas are visible, turn off Formulas > Show Formulas.
Why does the formula copy down but the result not update?
Check Formulas > Calculation Options and select Automatic. In Manual mode, formulas can retain old displayed results until recalculated. Also confirm that the formula points to the changed input cells and that those inputs are numeric where the calculation requires numbers.
Related
- Excel EIN Number Format: Keep All 9 Digits Visible
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Cell List Drop-Down: Create and Fix OneTo create an Excel cell list drop-down in Microsoft 365 for Windows, select the cell, choose Data >…
- Excel Won’t Let Me Group Rows? Causes and FixesIf Excel will not let you group rows, first make sure you are on an unprotected worksheet with…
- Excel Dropdown Lists: Create, Edit, and Fix ThemTo make a dropdown list in Excel for Microsoft 365 on Windows, select the cells, choose Data >…
- Excel Data Analysis Not Working? Troubleshooting and FixesIf Excel’s Data Analysis command is missing or will not run, enable the Analysis ToolPak: in Windows Excel,…
- Excel FILTER #VALUE! Error: Causes and FixesIn Excel, a FILTER formula usually returns #VALUE! when its include array is not the same height or…
- Excel ROUND Function Not Working? Fixes to TryYour Excel ROUND function usually appears not to work because the value is stored as text, the formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.