If an Excel formula does not update when you drag it down, first check that you are dragging the fill handle, then confirm the formula uses relative references and that calculation is set to Automatic. If the formula text changes but its result does not, check for text-formatted cells, a fixed reference, or a calculation setting that is preventing a refresh.
How dragging a formula is supposed to work
In Excel for Microsoft 365 on Windows, dragging the small square at the bottom-right corner of a selected cell copies the formula into neighboring cells. Excel adjusts relative cell references to match each new row. For example, a reference to A2 normally becomes A3 when the formula is filled down one row. This behavior is called relative referencing.
For a concrete example, assume A2 contains 4 and B2 contains 6, and the next row contains 5 and 3. With quantities in column A and prices in column B, enter this in C2:
With quantities in A2:A3 and prices in B2:B3, enter =A2*B2 in C2. It returns 24. Dragging the fill handle to C3 changes the formula to =A3*B3, which returns 15.
If the formula in the lower cell still points to row 2, Excel may have copied a fixed reference, you may have copied the cell rather than filled it, or the formula itself may not be a normal formula. If the references changed correctly but the displayed answer stayed the same, investigate calculation and cell formatting instead.
Make sure you are using the fill handle
Select the cell containing the formula. Look for the small square at the selection outline’s bottom-right corner, move the pointer over it until it becomes a thin black cross, and drag down over the destination cells. Releasing the mouse fills those cells. The Auto Fill Options button may appear near the filled range; it lets you choose how Excel fills the selection.
Dragging the border of a selected cell is different: it moves the cell or its contents rather than filling a series. If you do not see the small square, check Excel’s fill-handle setting. On Windows, go to File > Options > Advanced and, under Editing options, enable Enable fill handle and cell drag-and-drop. On Mac, Excel’s settings are under Excel > Settings or Excel > Preferences, depending on the release; look in the Edit settings for the fill-handle and drag-and-drop option. The interface can vary slightly by version.
You can fill without dragging, too. Select the formula cell and the cells below it, then choose Home > Fill > Down. The Windows shortcut is Ctrl+D. On Mac, use Command+D. This is useful when the fill handle is difficult to target or when the destination range is long.
Check whether references should move
A formula can appear not to update because its references were deliberately locked. A dollar sign makes a row, column, or both absolute. When a formula is filled, an absolute reference stays fixed; a relative reference shifts. A mixed reference locks only one part. This is useful when every row should multiply by the same rate, tax, or conversion factor.
For example, assume A2 contains 4, A3 contains 5, and F1 contains the fixed rate 2. To multiply each value in column A by that rate, enter this in B2:
With values in A2:A3 and a fixed rate of 2 in F1, enter =A2*$F$1 in B2. It returns 8. When filled to B3, the formula becomes =A3*$F$1 and returns 10; the A reference moves, while F1 remains fixed.
Inspect the formula bar for dollar signs if every filled formula keeps referring to the same row or cell. In an editable formula, selecting a reference and pressing F4 on Windows cycles through relative, absolute, and mixed reference forms. On Mac, the equivalent can depend on the keyboard and function-key settings; you can also type or remove the dollar signs directly. Do not remove dollar signs automatically: first decide whether that reference is meant to change.
Confirm Excel is calculating formulas
If the formula changes from row to row but the displayed answer stays stale, calculation may be set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and choose Automatic. The same Calculation Options control is available on the Formulas tab in desktop Excel for Mac, although ribbon layout can differ. In Excel for the web, calculation is normally automatic; if results look old, check the formula and refresh or reopen the workbook before assuming the desktop setting applies.
After switching to Automatic, Excel should recalculate formulas when their inputs change. To request a recalculation on Windows, press F9, or choose Formulas > Calculate Now. F9 recalculates formulas in open workbooks; Shift+F9 calculates the active worksheet. Keyboard behavior can differ on Mac depending on function-key settings, so the Calculate Now command is an alternative. A full recalculation can take time in a large workbook.
Manual calculation is sometimes intentional in complex workbooks, because recalculating after every edit can slow work. But it can make correct formulas look broken. Also remember that a workbook’s displayed result can depend on its inputs and calculation state, not just on whether a formula was filled. If one input is text where a number is expected, for example, the result may not match your expectation even when recalculation occurs.
Look for formulas stored as text
If a cell displays something such as =A2*B2 instead of a result, Excel may be treating the entry as text. A leading apostrophe, a cell formatted as Text before the formula was entered, or a pasted value can cause this. Selecting Home > Number Format > General does not always convert an existing text entry into a working formula by itself.
- Check whether the formula begins with an apostrophe in the formula bar, or whether the cell was set to Text.
- Change the cell’s number format to General, then edit the formula and press Enter. F2 followed by Enter is a quick Windows method; on Mac, use the equivalent edit-and-confirm keys for your keyboard.
- Check whether Show Formulas is enabled. On the Formulas tab, turn off Show Formulas if Excel is displaying formulas throughout the sheet instead of their results.
There is an important difference between seeing a formula and seeing an unchanged result. Show Formulas changes what the worksheet displays; it does not convert formulas or alter their calculations. Likewise, formatting affects how values appear, but a number format alone does not usually explain why references failed to advance when filled.
Check for tables, spilled formulas, and blocked cells
Excel tables behave differently from ordinary ranges in a helpful way. When a formula is entered in a table column, Excel often fills it down as a calculated column and applies it to the other rows. If that did not happen, check whether the data is actually a table: click inside it and look for the Table Design tab. A range that merely has colored formatting is not necessarily a table.
Dynamic-array formulas are another special case. Functions such as FILTER or UNIQUE return results that spill into neighboring cells from one formula cell. They are not normally copied down row by row with the fill handle; the formula in the starting cell controls the output range. If a spill range is blocked by existing content, Excel can show a spill error. Clear the obstructing cells or move the formula rather than dragging it into each result cell.
Other layout constraints can interfere with filling. Merged cells in the destination range, a protected worksheet, or noncontiguous selections may prevent the fill or produce an unexpected result. Try a small, unmerged, unlocked test range. If that works, inspect the original range’s protection and layout before changing formulas.
A quick diagnostic sequence
Use this order to identify the cause without changing more of the workbook than necessary:
- Click the original cell and verify that the formula begins with an equals sign and returns a sensible result.
- Drag the small fill-handle square, not the selection border. Alternatively, select the range and use Home > Fill > Down.
- Click a filled cell and inspect the formula bar. Check whether row references advanced and whether dollar signs intentionally keep references fixed.
- If references advanced but the result did not, set Formulas > Calculation Options to Automatic or recalculate the sheet.
- If the formula is visible as text, change the format to General and re-enter it. Also check Show Formulas.
- If filling is unavailable or incomplete, check for a table, a spilled formula, merged cells, or sheet protection.
Test the behavior in a blank area if you are unsure whether the problem is the formula or the worksheet. A simple formula filled down should make it clear whether Excel can adjust relative references in that workbook. Avoid replacing formulas across a large range until you have confirmed the intended reference pattern; a fixed rate or lookup cell may need to remain absolute.
When copying is better than dragging
Dragging is convenient for short ranges, while fill commands are more precise for larger selections. You can also copy the source formula, select the destination range, and paste it. Excel normally adjusts relative references during a copy in the same way it does when filling, so copying will not fix an unwanted absolute reference or a manual calculation setting.
For data maintained as a list, an Excel table can reduce repeated manual filling because calculated-column formulas are designed to propagate to rows. For one-off analysis, a regular range and the fill handle may be simpler. Choose based on how the data will grow: tables are useful when new rows are regularly added and the range should remain structured, while an ordinary range can be easier for a small, fixed layout.
Frequently Asked Questions
Why does Excel copy the same formula down?
Check whether its references contain dollar signs, which lock a row or column. Also confirm that you used the fill handle rather than moving the cell. A relative reference normally adjusts as it is filled.
Why does the formula change but the answer stay the same?
Excel may be using Manual calculation. Choose Formulas > Calculation Options > Automatic, then recalculate. If the result still seems wrong, check the referenced inputs and whether they contain numbers rather than text.
Why does Excel show the formula instead of the result?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be on. Set the cell to General and re-enter the formula, and check the Show Formulas control on the Formulas tab.
Can I fill a formula down without dragging?
Yes. Select the formula cell and the destination cells, then use Home > Fill > Down. Ctrl+D works on Windows; Command+D works on Mac.
Why does a formula not fill down in a protected sheet?
Protection can prevent editing or filling cells that are locked. If you have permission to change the sheet, use Review > Unprotect Sheet, make the change, and restore protection as needed. A password-protected sheet requires the password or help from its owner.
Related
- Change Text to Numbers in Excel: Easy, Reliable Methods
- Center Across Selection in Excel: Add It to Quick Access
- Change Block Capitals to Lowercase in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IF Function in Excel: Examples and Practical UsesIn Excel, the IF function checks whether a condition is true and returns one result if it is,…
- How to Get the Highest Number in Excel with the MAX FunctionTo get the highest number in a range, use Excel’s MAX function. With sample values in A2:A8, enter…
- Excel Joining Two Text Columns: Formulas and ExamplesTo join two text columns in Excel, enter a formula such as =A2&" "&B2 in a third column,…
- Excel Concatenate Formula Examples for Text and CellsWith a first name in A2 and a last name in B2, join them with a space using…
- Excel Standard Deviation Formula: Which Function to UseIn Excel for Microsoft 365, use STDEV.S to calculate standard deviation from a sample, or STDEV.P when your…
- How to Filter Negative Values in Excel: Filters & FormulasTo show only negative numbers in Excel, select your data range, choose Data > Filter, open the dropdown…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.