To autofill in an Excel table, enter a value or formula in a cell, then drag its small fill handle down the column; Excel can also copy a formula through a table column automatically. For a numbered or date series, enter the first two examples, select them, and drag the fill handle. Start with a table that has a header row and a clear data range so Excel knows where the column begins and ends.
What You’ll Need
- Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The basic fill methods are similar, though some shortcuts and settings differ.
- A worksheet with a real Excel table, or a range you can convert into one. A table has filter arrows in its headers and usually a table style.
- For a sequence such as dates or invoice numbers, at least two starting values that show the pattern you want Excel to continue.
Step-by-Step
- Confirm that your data is an Excel table. Click inside the data. If the ribbon shows a Table Design tab, it is already a table. If not, select the data, including its headers, and choose Insert > Table. Confirm the range and check My table has headers if the first row contains column names, then select OK. Avoid blank rows or columns within the data because they can make Excel detect the wrong range.
- Choose what you want to autofill. Autofill can copy a value, repeat a text label, extend a recognizable series, or copy a formula. Decide whether you want identical content or a changing pattern. For example, dragging a single value usually repeats it, while two consecutive dates can provide a clue that you want daily dates. If you are filling a table formula, use column headers that clearly describe the data.
- Enter a formula in the first data row when you need a calculated column. In many Excel tables, entering a formula in one cell of a new column automatically copies it to the other rows in that column. With an Excel table named Orders and columns Quantity, Unit Price, and Line Total, enter this formula in the first data cell under Line Total:
=[@Quantity]*[@[Unit Price]]. If that row has a quantity of 3 and a unit price of 12, the result is 36. The structured references identify values in the current row, so the formula adjusts for each table row. - Use the fill handle to copy or extend a pattern. Select the cell or cells you want to use as the starting pattern. Point to the small square at the lower-right corner of the selection; when the pointer becomes a thin black plus sign, drag down over the destination cells. Release the mouse button to fill. If the handle is hard to see, zoom in or select the cell again. For a formula or value you want copied unchanged, one starting cell is usually enough.
- Give Excel two examples when you want a series. Enter two values that establish the interval, such as 1 and 2, or two dates one day apart. Select both cells, then drag the fill handle down. Excel uses the difference between the examples to continue the sequence. If you start with 1 and 3, for instance, Excel can continue by adding 2 each time. A single starting number is generally copied rather than treated as a request to count upward.
- Fill down a selected table range with a command. If you already have the formula or value at the top of the cells to fill, select that starting cell and the cells below it in the same column. On Windows, press Ctrl+D to use Fill Down. You can also choose Home > Fill > Down. On Mac, use Home > Fill > Down if the Windows shortcut does not match your keyboard setup. The top cell’s contents are copied into the selected cells; this does not create a new number series.
- Check the result and adjust the fill behavior if needed. After dragging, select the Auto Fill Options button that may appear beside the filled range. Depending on the starting data, options can include copying cells, filling a series, or filling without formatting. Choose the option that matches your goal. Inspect several rows, especially formulas, dates, and the last filled row, before relying on the results. If the table has a total row, make sure you did not overwrite it.
When you add a new row at the bottom of a table, Excel often carries a calculated-column formula into that row. You can add a row by typing directly below the table or by pressing Tab while the last cell of the last table row is selected. If the formula does not appear, enter it in the new row or copy it down, then check whether the calculated-column setting is enabled.
Common Mistakes
- Dragging from one value and expecting a sequence. A single number such as 5 often repeats. Supply two starting numbers, or choose a series option after filling, to show Excel the intended step.
- Using a regular range when you expect table behavior. A formatted range is not necessarily an Excel table. Click in the data and check for the Table Design tab. Convert the range with Insert > Table if you want table features such as structured references and calculated columns.
- Filling past the intended end of the data. A double-click on the fill handle can fill down based on adjacent data, but a blank or uneven neighboring column can stop it early or affect how far Excel fills. Drag to a clearly selected destination or use Fill Down on a deliberate selection.
- Overwriting existing entries or a total row. Check the selected range before using Ctrl+D or dragging. Fill Down replaces the contents of the destination cells; it does not protect data already there.
- Assuming every formula should stay identical. Normal cell references can shift when copied, while absolute references marked with dollar signs stay fixed. Table structured references such as
[@Quantity]refer to the current row. Check the first, middle, and last results to confirm each formula uses the intended references. - Confusing copied formatting with copied values. A fill operation may bring formatting along with the contents. Use Auto Fill Options and choose Fill Without Formatting when you want the values or formulas but need to preserve the destination cells’ appearance.
Tips
- Let calculated columns do routine work. In a table, enter a consistent formula once in a column and let Excel apply it to the rest of the column. This also helps the formula extend to new table rows. If different rows need different calculations, do not force them into one calculated column without checking the results.
- Use meaningful headers. Names such as Quantity and Unit Price make structured-reference formulas easier to recognize and maintain than generic headings such as Column1. Avoid duplicate or blank headers when building the table.
- Use a deliberate selection for large fills. Select the source cell and destination cells, then use Home > Fill > Down. This is often more precise than dragging through hundreds of rows and makes it easier to see exactly which cells will change.
- Use two examples for dates and repeating patterns. To fill weekly dates, enter two dates seven days apart, select both, and drag. To repeat a weekday pattern or a custom sequence, check the resulting cells rather than assuming Excel inferred the pattern correctly.
- Keep a source row intact. Before filling a formula over a large range, make sure the first formula is correct and the destination is the right column. For important workbooks, save a copy before making a broad change.
- Know where the fill-handle setting lives. If you cannot drag the handle in Excel for Windows, check File > Options > Advanced and look for Enable fill handle and cell drag-and-drop under Editing options. On Mac, Excel settings are under Excel > Settings or Excel > Preferences, depending on the release; look in the editing options for the fill-handle control.
Troubleshooting
The fill handle is missing or will not drag. In Windows Excel, open File > Options > Advanced and enable Enable fill handle and cell drag-and-drop. On a Mac, open Excel > Settings or Excel > Preferences and check the editing options. If the setting is already enabled, press Esc to exit cell-edit mode, select the cell once, and try again from its lower-right corner.
The formula did not copy through the table. First confirm the formula is in a table data column, not outside the table or in its header or total row. Enter the formula again in the first data cell. In Windows, the calculated-column behavior can be checked under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type; look for Fill formulas in tables to create calculated columns. If the option is off, turn it on or fill the cells manually. The names and locations of settings can vary by Excel release, especially on Mac.
Excel repeated a number instead of counting. Start with two cells that show the intended interval, select both, and drag. If you have already filled the range, use the Auto Fill Options button and select a series option if it is available. For an exact custom interval, use Home > Fill > Series and review the options before confirming.
The fill stopped too soon or went too far. A double-click uses nearby data to judge how many rows to fill. If that neighboring column contains blanks or has a different length, double-click may not match your target. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then select the exact destination cells and use Home > Fill > Down or drag to the intended final row.
Some results look wrong after filling. Check for text-formatted numbers, blank rows, mixed formulas, or references that changed unexpectedly. Click a result cell and inspect the formula bar. If the cells are meant to contain dates, confirm they are real Excel dates rather than text that only looks like a date. Correct the source cell first, then refill the intended range.
Frequently Asked Questions
How do I autofill a formula in an Excel table?
Enter the formula in a data cell in the table column. Excel commonly fills the calculated column automatically. If it does not, select the formula cell and the cells below it, then use Home > Fill > Down or Ctrl+D on Windows.
How do I make Excel continue numbers instead of repeating one?
Enter two starting numbers that show the increment, select both, and drag the fill handle. For a series that follows a more specific step, use Home > Fill > Series and set the direction, step value, and stop value as needed.
Can I autofill dates in an Excel table?
Yes. Enter one or two dates and drag the fill handle down. Two dates help Excel infer the interval, such as one day or one week. Check the filled dates before using them, particularly if you need weekdays only or a custom schedule.
Will a formula autofill when I add a new table row?
Usually, Excel extends a calculated-column formula to a new row. If it does not, enter or copy the formula in that row and check the Fill formulas in tables to create calculated columns setting. Keep the formula consistent throughout the column for the most predictable behavior.
Can I autofill without copying the cell’s formatting?
Yes. After using the fill handle, open the Auto Fill Options button and choose Fill Without Formatting when that option is shown. You can also format the destination cells after filling if the option is unavailable for the type of data you entered.
Does autofill work the same way in Excel for the web?
The fill handle and table formulas are available in Excel for the web, though the ribbon layout and some options may differ from Windows desktop Excel. Select the cell and drag its handle, or use the available Home tab fill commands. If an option is not visible, open the workbook in the desktop app.
Related
- Excel CSV Import Options: Which Method to Use
- Excel CSV Import Settings: Delimiters and Data Types
- Excel Import CSV Decimal Separator: How to Set It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Sort in Excel and Google SheetsTo sort data without separating related values, select a cell in the complete table, open the sort controls,…
- How to Add a Logo to a Header in Word (Microsoft 365)To add a logo to a header in Word, open the header by double-clicking the top of a…
- Excel Data Validation Date Format ExplainedIn Excel for Microsoft 365 on Windows, set a date rule by selecting the cells, choosing Data >…
- How to Merge Cells in Excel Without Losing Your DataTo merge cells in Excel without losing their contents, first combine the values you need into the top-left…
- How to Add Subtotal Rows in Excel by Category or DateTo add subtotal rows in Excel, sort your data by the field you want to group, select a…
- How to Make One Page Landscape in Word 365: Step by StepTo make one page landscape in Word 365 while keeping the rest of the document portrait, isolate that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.