To copy a formula down a column in Excel, enter it in the first cell, select the cells below that need it, then choose Home > Editing > Fill > Down or press Ctrl+D on Windows. For a quick fill beside a continuous data list, double-click the cell’s fill handle. These methods copy the formula while normally adjusting its relative cell references for each row.
Before filling, decide which rows should receive the calculation and make sure the formula works in the first row. Excel for Microsoft 365 on Windows is used below; menu commands are similar on Mac and Excel for the web, with shortcut differences noted where useful.
What You’ll Need
- An Excel workbook with the formula already entered in the first row where you need a result.
- A clear idea of the last row to fill, such as the final row of a data list. If you plan to double-click the fill handle, keep a neighboring column populated through that row.
- Optional: a saved copy of the workbook if you are changing a large or important range. Filling formulas can overwrite existing contents in the destination cells.
Step-by-Step
- Check the formula in the first row. Make sure it refers to the intended cells and produces the expected result before copying it. For example, with quantities in B2, unit prices in C2, and the total to calculate in D2, enter
=B2*C2in D2. If B2 contains 3 and C2 contains 12, D2 returns 36. This is a relative-reference formula, so Excel can adjust the row references when it is filled downward. - Choose the destination rows. Note the first and last rows that should contain a result. If the formula is in D2 and your data ends at row 100, the destination is D2:D100. Include the original formula cell at the top of the selection when using Fill Down. Do not select the entire worksheet column unless you truly want the formula in every row; that can create a very large range and slow the workbook.
- Use the Fill Down command for a precise range. Select the formula cell and all the cells below it that should receive the formula. In this example, select D2:D100. On the Windows ribbon, choose Home > Editing > Fill > Down. Excel copies the top cell’s contents into the selected cells. You can also use the keyboard shortcut Ctrl+D on Windows. Check the first and last filled rows to confirm the range is right.
- Double-click the fill handle for a neighboring data list. Select the formula cell. Move the pointer to the small square at the bottom-right corner of its border; this is the fill handle. When the pointer becomes a small black plus sign, double-click. Excel usually fills the formula down to match the adjacent continuous data. For example, if column B contains a value on every row from 2 through 100, the formula may fill through row 100. This method is convenient, but it relies on the neighboring column to identify the end of the list.
- Drag the fill handle when you want to control the endpoint. Select the formula cell, point to the bottom-right corner until the fill handle appears, then click and drag it down to the last row you need. Release the mouse button when the highlighted outline reaches the correct row. Dragging is useful when adjacent columns contain gaps or when you want to fill a shorter range than the full list. If the destination is far below the starting cell, the selection method in Step 3 is often easier to control.
- Copy and paste if that suits your workflow. Select the formula cell and press Ctrl+C, select the destination range, and press Ctrl+V. Excel pastes the formula into each selected row and adjusts relative references as appropriate. To copy only the formula rather than formatting, use Home > Paste > Formulas if that option is available in your ribbon’s Paste menu. Standard paste can also copy formatting, which may not be wanted.
- Consider using an Excel Table for repeat work. If your data is a structured list that grows over time, select it and choose Insert > Table, confirming whether it has headers. Enter the formula in the first row of the relevant table column. Excel typically fills it through the calculated column and applies it to new table rows. This can save you from repeating a manual fill each time records are added. Check the calculated column after entering the formula, especially if Excel’s calculated-column behavior has been changed in workbook settings.
- Verify the results and save the workbook. Look at the first few results, a row near the middle, and the final row. Confirm that the formula is present where expected and that the referenced row numbers advance correctly. If you used a fixed cell reference, verify that it stayed fixed. Save after confirming the results; if a fill replaced values or formulas you needed, use Undo immediately with Ctrl+Z on Windows.
On a Mac, you can use the Home tab’s Fill command or drag the fill handle. Keyboard shortcuts can differ according to the keyboard and Excel version; use the ribbon command if the Windows Ctrl+D shortcut does not work as expected. In Excel for the web, use the Home tab’s Fill options or the fill handle, and be mindful that browser shortcuts can intercept some key combinations.
Common Mistakes
- Filling too many rows. Double-clicking the fill handle uses nearby data to estimate how far the formula should go. A blank cell in that neighboring column may cause the fill to stop early; data extending farther than the intended list may cause it to go too far. Use a selected destination range when you need an exact endpoint.
- Overwriting existing content. Fill Down replaces contents in the selected destination cells with the top cell’s content. Check the destination range before filling, particularly if it may contain notes, manually entered values, or different formulas. Undo right away if the wrong cells were changed.
- Misunderstanding relative references. Excel normally shifts references as a formula moves. That is useful when each row should use that row’s data, but it can be wrong if a formula needs to keep referring to one fixed input cell. Add dollar signs to a reference that must remain fixed, or use F4 while editing a reference on Windows to cycle through reference types. On many Mac keyboards, Fn+F4 or the equivalent function-key setting may be needed.
- Copying a value instead of a formula. If the formula bar shows a result rather than a formula after copying, you may have used Paste Values or copied a cell that only contained a value. Copy from the original formula cell and choose normal paste or Paste Formulas.
- Filling across instead of down. Ctrl+D fills down; Ctrl+R fills right on Windows. Check the selection orientation before using either shortcut. If you are unsure, use Home > Editing > Fill > Down.
Tips
- Use the selection method for predictable results: click the formula cell, extend the selection through the last destination row, then use Fill Down. This avoids relying on neighboring data to set the endpoint.
- To select a range quickly, click the Name Box to the left of the formula bar, type a range such as D2:D100, and press Enter. Then choose Fill Down. This is handy for long lists, but make sure the range starts with the cell containing the formula.
- When formulas should adapt to each row, leave row references relative. When a formula must keep using a tax rate, exchange rate, or other shared input in a single cell, make that reference absolute with dollar signs before filling. Check the copied results rather than assuming every reference should behave the same way.
- Use a Table when new records are added regularly. Besides extending a calculated column, a table can make the data easier to filter and manage. Give columns clear headers and avoid mixing unrelated calculations in the same table column.
- For a large fill, inspect more than just the top two results. A formula may work in the first row but encounter blanks, text, or a different data pattern later. Check representative rows and the final row before relying on the whole column.
- If the result appears unchanged after filling, the workbook may be set to manual calculation. Check Formulas > Calculation Options > Automatic. You can also recalculate with F9 on Windows. Be aware that recalculation shortcuts vary on Mac keyboards.
Troubleshooting
The formula only filled a few rows. If you double-clicked the fill handle, inspect the neighboring column Excel used to determine the range. A blank cell can make Excel stop at that point. Select the full intended range yourself and use Home > Editing > Fill > Down instead.
The formula did not fill at all. Confirm that you selected a range with the formula cell at the top and destination cells below it. If the cells are part of a protected worksheet, you may not have permission to edit them; check Review > Unprotect Sheet if you are authorized to change the sheet. Also confirm that the workbook is not open in a mode that prevents editing.
The results are wrong in lower rows. Click a result cell and inspect the formula bar. Check whether the cell references advanced as intended, and whether a reference that should stay fixed has moved. If the formula uses a shared input, edit the top formula to use an absolute reference, then fill the corrected formula again. Confirm that you have not selected or pasted into the wrong column.
The cells show the formula text instead of a calculated result. The cells may be formatted as Text, or Show Formulas may be turned on. Change the cell format to General, then re-enter the formula in the top cell and fill down again. On Windows, Ctrl+grave accent toggles Show Formulas; the key is usually beside the number 1, though keyboard layouts differ. You can also check Formulas > Show Formulas on the ribbon.
Some rows show errors or blanks. Check the source cells in those rows. A blank, text value, or invalid input can make a copied formula return a blank-looking result or an error even when the fill itself worked correctly. Compare the formula in an affected row with a nearby correct row and confirm that the source data is complete and in the expected format.
The fill replaced something important. Press Ctrl+Z immediately on Windows, or use Edit > Undo. If you have made further changes since the fill, Undo may also reverse those changes, so review the workbook carefully. If Undo is no longer available, restore from a saved copy or version history if your file is stored in a location that supports it.
Frequently Asked Questions
How do I copy a formula to every row without dragging?
Select the formula cell and the destination cells below it, then press Ctrl+D on Windows or use Home > Editing > Fill > Down. This fills the selected range without dragging.
Why does Excel change the cell references when I copy a formula?
That is normal for relative references. Excel adjusts them to match the formula’s new row or column. To keep a reference pointed at the same cell, make it absolute by adding dollar signs to the column and row references before filling.
Can I copy a formula down to the last row automatically?
Double-click the fill handle to fill alongside a continuous neighboring data range. Excel uses that neighboring column as a guide, so verify the endpoint. A Table is a better option when the list regularly gains new rows.
Will copying a formula also copy the cell formatting?
It can. The fill handle and standard copy-and-paste may carry formatting, depending on the method and options used. If you want formulas without the source formatting, use Paste Formulas from the Paste menu, or review the Auto Fill Options button that appears after using the fill handle.
Can I fill formulas down in Excel for Mac or Excel for the web?
Yes. Use the Fill command on the Home tab or drag the fill handle. Windows shortcuts may not behave the same on Mac or in a browser, so use the ribbon command when a shortcut is uncertain.
Does Fill Down copy the formula into blank cells only?
No. Fill Down copies the top cell’s contents throughout the selected range, replacing what is already in those cells. If you need to preserve existing content, select only the intended empty destination cells or use another method suited to your data.
Related
- How to Combine CSV Files in Excel with Power Query
- How to Color Values in Excel with Conditional Formatting
- Color Heat Rank in Excel with Conditional Formatting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- Excel RIGHT Function Examples: Extract Text from the EndUse Excel’s RIGHT function to return characters from the end of a text value. For example, if A2…
- Power Query VLOOKUP: How to Match Tables in Excel InsteadPower Query does not have a VLOOKUP function; use Merge Queries to match rows from two tables and…
- Count a Specific Word in Excel: Formulas That WorkTo count cells that exactly match a word, use =COUNTIF(A2:A100,D1); to count cells that contain it anywhere, use…
- IFERROR With IF in Excel: Syntax, Examples, and TipsTo use IFERROR with IF in Excel, put the IF function inside IFERROR: =IFERROR(IF(B2=0,"No quantity",A2/B2),"Check values"). IF makes…
- Excel TEXT Function for Dates: Format Dates as TextUse Excel’s TEXT function to display a date in a chosen written format: if A2 contains the date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.