If Excel will not drag a formula, enable the fill handle in File > Options > Advanced, then drag the small square at the selected cell’s bottom-right corner down or across the cells you want to fill. If dragging is unavailable, select the destination range and use Home > Fill > Down or Ctrl+D on Windows; these methods copy the formula without needing the handle.
How formula dragging works
Dragging the fill handle copies a cell’s contents into neighboring cells. For a formula, Excel normally adjusts relative cell references to suit each new row or column. For example, if a formula refers to cells in the same row, filling it down one row usually changes those references to the next row. This is why a formula can be entered once and then extended through a list.
With quantities in A2:A3 and prices in B2:B3, put 3 in A2 and 8 in B2, then enter =A2*B2 in C2. The result in C2 is 24. Drag C2’s fill handle down to C3 and Excel copies the formula as =A3*B3; if A3 is 4 and B3 is 5, C3 returns 20. The formula changes because its references are relative.
Dragging is different from asking Excel to extend a number pattern. If you drag a cell containing the number 1, Excel may repeat 1 or infer a series depending on the selection and the fill options. A formula is generally copied with adjusted references, while a value may be copied or extended as a series. The Auto Fill Options button that appears after filling lets you choose among available behaviors.
Check whether the fill handle is enabled
In Excel for Microsoft 365 on Windows, open File > Options > Advanced. Under Editing options, select Enable fill handle and cell drag-and-drop, then choose OK. Return to the worksheet, select a cell, and look for the small square at the lower-right corner of its selection border. If the square is visible, move the pointer directly over it until the pointer changes, then hold the mouse button and drag.
On Excel for Mac, open Excel > Preferences or Settings, depending on the release, and look under Edit for the fill-handle and cell-dragging option. Enable it if it is off. The precise wording and location can vary by Mac release. In Excel for the web, dragging the fill handle is available in the grid, but desktop Options settings are not part of the web interface.
- No small square appears: check the fill-handle setting and confirm that a normal worksheet cell, rather than an object or chart, is selected.
- The square appears but nothing happens: put the pointer precisely on the square, then drag. Clicking inside the cell or on the selection border will not start a fill.
- The selection moves instead of filling: the pointer may be on the border rather than the handle. Try again at the exact lower-right corner.
Use a keyboard or ribbon command instead
You do not have to drag to copy a formula. Select the source cell and the destination cells beneath it, then choose Home > Fill > Down, or press Ctrl+D on Windows. Excel fills the selected range using the top cell’s contents, adjusting relative references in the copied formulas. For a range extending to the right, use Home > Fill > Right or Ctrl+R on Windows.
For example, select C2:C20 when C2 contains the formula you want to repeat, then choose Fill Down. This is often more precise than dragging through a long sheet, especially when the target range has a known last row. On Mac, Control+D is the common Fill Down shortcut in Excel; keyboard behavior can depend on the keyboard and system settings. You can always use the ribbon command if a shortcut is intercepted by macOS.
Copy and paste is another option: copy the formula cell, select the target range, and paste. This copies the cell’s formula and formatting. If you want formulas but not formatting, use the Paste Special options available from Home > Paste. For a one-time fill, however, the fill handle or Fill Down is usually quicker.
Check formula references before filling
A formula may fill successfully yet produce unexpected results because of its reference style. In a relative reference such as A2, both the column and row can shift when the formula is copied. An absolute reference such as $F$1 stays fixed. A mixed reference, such as $F1 or F$1, locks only the column or the row. Dollar signs are useful when every copied formula should refer to one shared input, such as a tax rate or conversion factor.
With quantities in A2:A3, prices in B2:B3, and a fixed rate of 5% in F1, enter =B2*$F$1 in C2. If B2 contains 8 and F1 contains 5%, the result is 0.4. Fill the formula down to C3: the B reference adjusts to B3, but $F$1 remains fixed. If you accidentally use F1 without dollar signs, filling down changes it to F2, which may be blank or contain a different value.
To change reference types while editing a formula, select a reference and press F4 on Windows to cycle through relative, absolute, and mixed forms. On some laptops, you may need Fn+F4. Mac keyboard behavior can differ, so adding dollar signs directly is a reliable alternative. Check one or two filled rows before filling a very large range, particularly when formulas combine several references.
When dragging copies the formula but results look wrong
First, inspect the formula in a filled cell rather than judging by the displayed value alone. Click the cell and read the formula bar. If its references have shifted as intended, the fill worked; the result may be surprising because a referenced cell is blank, contains unexpected data, or is not part of the intended calculation.
If formulas appear to remain unchanged, check whether the references are absolute. A formula containing $A$2 will continue to point to A2 when copied, by design. If a result does not update after you change an input, check Formulas > Calculation Options. When calculation is set to Manual, Excel may not recalculate automatically. Choose Automatic when appropriate, or recalculate the workbook using the available calculation commands.
Another common confusion is seeing the formula text in the cell instead of its result. The cell may be formatted as Text, the entry may have a leading apostrophe, or Show Formulas may be turned on. To correct a text-formatted formula, change the cell format to General, then re-enter the formula (press F2 and Enter in many cases). On the ribbon, check Formulas > Show Formulas if formulas are displayed throughout the worksheet. Formatting a cell as General after a formula was entered as text does not always convert the existing entry automatically; re-enter it.
Check the worksheet and range
A protected worksheet can restrict edits to locked cells, so filling into a protected destination may be blocked even when the formula cell is editable. If you are authorized to change the sheet, review the protection settings or ask the workbook owner to allow editing in the relevant cells. Do not remove protection on a shared or controlled workbook without checking why it is in place.
Merged cells can also make a fill range awkward because the cells do not form a uniform rectangular range. If the target area contains merged cells, unmerge them where appropriate and try filling again. A selection containing hidden, filtered, or irregularly arranged rows can also make it less obvious which cells are being changed. For important data, select and inspect the intended destination range before using Fill Down.
If the formula is inside an Excel table, the table may handle formulas differently from an ordinary range. Entering a formula in a calculated column can cause Excel to extend it through that column automatically. If it does not, check whether automatic calculated-column filling has been disabled or whether the column contains inconsistent entries. Tables are useful for growing datasets, but their automatic behavior can be confusing if you expected to drag one cell manually.
Fill to the end of a data list
Dragging is convenient for a few rows, but can be imprecise for hundreds or thousands. If the formula is in the first row of a continuous data block, select the source cell and the intended cells below it, then use Fill Down. Double-clicking the fill handle can also fill down alongside neighboring data in many common layouts. Excel uses adjacent data to estimate how far to fill, so the result depends on the shape and continuity of that neighboring column.
Always confirm the last filled row after a double-click. A blank cell in the adjacent column can stop the fill early, while data extending farther than expected can lead to more filled rows than intended. When the target extent matters, select the exact range yourself and use Ctrl+D or Home > Fill > Down. This gives you explicit control over which cells receive the formula.
Frequently Asked Questions
Why is there no fill handle in Excel? The fill handle may be disabled. In Windows Excel, go to File > Options > Advanced and enable Enable fill handle and cell drag-and-drop under Editing options. On Mac, look in Excel > Preferences or Settings > Edit. Also make sure a worksheet cell is selected.
Why does Excel repeat the same formula result when I drag down? The formula may use absolute references, which stay fixed, or the formulas may recalculate to the same value because the referenced cells contain equal data. Click a filled cell and inspect its formula bar to see whether references changed. Also check calculation mode if results appear stale.
How do I fill a formula down without dragging? Select the formula cell and the destination cells below it, then choose Home > Fill > Down or press Ctrl+D on Windows. On Mac, Control+D commonly performs Fill Down. The top cell supplies the content for the selected range.
Why does dragging fill only part of my column? If you double-click the fill handle, Excel estimates the destination length from adjacent data, and gaps can stop the fill early. Select the exact range and use Fill Down when you need a predictable end row.
Does dragging a formula change the original formula? No. It copies the formula into additional cells; the source cell remains in place. Relative references in the copies can adjust, while absolute references stay fixed. If you need the same reference in every copy, use dollar signs to lock it.
Related
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Left and Right Mouse Buttons Not Working?If the left or right mouse button is not working in Excel, first check whether the same button…
- Excel #NULL! Error Fix: Find and Correct Range ReferencesTo fix Excel’s #NULL! error, check for a space between cell ranges: Excel treats that space as an…
- COUNTIFS Excel Error? How to Find and Fix Common CausesA COUNTIFS error is usually caused by criteria ranges with different dimensions, invalid criteria syntax, or references that…
- SUMIF Not Working in Excel? How to Find and Fix ErrorsIf SUMIF is not working in Excel, first check that the criteria range and sum range cover the…
- Excel WEEKDAY Function Not Working? Fix Common CausesIf Excel’s WEEKDAY function is returning an error or an unexpected number, first confirm the cell contains a…
- COUNTIFS Formula Not Working? Fix Common ErrorsIf COUNTIFS is not working, first check that every criteria range is the same size and that each…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.