To turn off autofill formulas in Excel, go to File > Options > Formulas and uncheck “Automatic” under Calculation options, or disable the fill handle by unchecking “Enable fill handle and cell drag-and-drop” in File > Options > Advanced. The first stops formula recalculation, the second stops the drag-to-fill feature that copies formulas into adjacent cells.
What “Autofill Formula” Means in Excel
Excel has two separate features that people loosely call “autofill formula.” The first is automatic calculation: when you change a value, Excel recalculates every formula that depends on it. The second is the fill handle—the small square at the bottom-right corner of a selected cell. Dragging it copies the cell’s content, including formulas, into neighboring cells. Both can be annoying if you want to enter data without Excel “helping.” This article covers how to disable each, plus related settings like Flash Fill and table auto-expansion.
Turning Off Automatic Formula Calculation
If your formulas recalculate every time you type a number, and you want to stop that, change the calculation mode:
- Click File > Options (on Mac: Excel > Preferences > Calculation).
- Select Formulas on the left.
- Under Calculation options, choose Manual instead of Automatic.
- Optionally check “Recalculate workbook before saving” if you want Excel to update values when you save.
With Manual mode, Excel only recalculates when you press F9 (Windows) or Cmd+ = (Mac), or when you save if that option is checked. Note that this affects the entire workbook, not just one sheet. If you only want to stop formulas from copying when you drag, use the next method instead.
Disabling the Fill Handle (Drag-and-Drop)
The fill handle is what lets you drag a cell’s corner to copy a formula down a column. To turn it off:
- Go to File > Options > Advanced (on Mac: Excel > Preferences > Edit).
- Under Editing options, uncheck “Enable fill handle and cell drag-and-drop”.
- Click OK.
After this, dragging a cell’s corner no longer copies anything—it just selects the range. You can still copy and paste formulas normally with Ctrl+C / Ctrl+V (Cmd+C / Cmd+V on Mac). This setting also disables the right-click drag menu that lets you choose “Fill Series” or “Fill Formatting Only.”
Stopping Excel Tables from Auto-Filling Formulas
If you’re using an Excel Table (Insert > Table, or Ctrl+T), Excel automatically copies a formula down an entire column when you enter it in one cell. To stop that, you have a few options:
- Convert the table to a range: Right-click anywhere in the table, choose Table > Convert to Range. This removes the table’s automatic behavior, but you lose table features like structured references and banded rows.
- Use the Undo button (Ctrl+Z) immediately after Excel fills the column—this reverts the auto-fill, but only for that one action.
- Turn off “AutoCorrect” options: Go to File > Options > Proofing > AutoCorrect Options, then the AutoFormat As You Type tab, and uncheck “Fill formulas in tables to create calculated columns”. This stops future auto-fills in tables while keeping the table structure.
Note: The AutoCorrect option is available in Excel for Windows; on Mac, go to Excel > Preferences > AutoCorrect and uncheck the same option under the “AutoFormat as you type” section.
Flash Fill and Other Auto-Complete Features
Flash Fill (Data > Flash Fill, or Ctrl+E) automatically fills a column based on a pattern you start typing. It’s not a formula—it inserts static values—but many users confuse it with autofill. To turn it off:
- Go to File > Options > Advanced.
- Under Editing options, uncheck “Automatically Flash Fill”.
This stops Excel from suggesting Flash Fill when it detects a pattern. You can still trigger it manually with Ctrl+E. Similarly, the AutoComplete feature that suggests cell entries as you type can be disabled in File > Options > Advanced by unchecking “Enable AutoComplete for cell values”.
Common Problems and Misconceptions
Misconception: Turning off automatic calculation stops formulas from copying. It doesn’t—it only stops recalculation. To stop copying, you must disable the fill handle or table auto-fill as described above.
Problem: Manual calculation left on accidentally. If your formulas suddenly show stale values, check the calculation mode. A quick way is to look at the status bar: if it says “Calculate” next to “Ready,” you’re in Manual mode. Press F9 to recalculate.
Problem: Fill handle disabled but dragging still copies. This can happen if you’re using a touch device or have a third-party add-in that overrides the setting. Also, double-clicking the fill handle (when enabled) auto-fills down to the last adjacent cell—disabling the handle removes that too.
Problem: Table auto-fill keeps overriding your manual entries. If you type a different formula in one cell of a calculated column, Excel may overwrite it with the column’s formula. To prevent this, use the AutoCorrect option to turn off calculated columns, or convert the table to a range.
How to Choose the Right Setting for Your Workflow
Decide what you actually want to stop:
- If you want to enter data without Excel recalculating every keystroke (e.g., while typing a large dataset), switch to Manual calculation.
- If you want to prevent accidental formula copies when dragging, disable the fill handle.
- If you want to keep tables but stop formula auto-fill, use the AutoCorrect option.
- If you want to stop Flash Fill suggestions, uncheck Automatically Flash Fill.
You can combine these. For example, a data-entry clerk might disable the fill handle and set calculation to Manual, while a financial analyst might keep Automatic but turn off table calculated columns to avoid errors.
Frequently Asked Questions
Why does Excel keep copying my formula when I drag? That’s the fill handle. To stop it, go to File > Options > Advanced and uncheck “Enable fill handle and cell drag-and-drop.” If you still want to copy formulas occasionally, use Ctrl+C and Ctrl+V instead.
How do I stop Excel from auto-filling formulas in a table without losing the table? Go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab, and uncheck “Fill formulas in tables to create calculated columns.” This keeps the table’s formatting and filters but stops the automatic column fill.
Does turning off automatic calculation affect other workbooks? No, calculation mode is set per workbook, not globally. However, if you open multiple workbooks, each retains its own setting. Excel may prompt you to recalculate when opening a workbook saved with Manual mode.
What is the keyboard shortcut to recalculate manually? Press F9 on Windows to recalculate all open workbooks. On Mac, press Cmd+ = (Command plus equals). To recalculate only the active sheet, use Shift+F9 (Windows) or Cmd+Shift+= (Mac).
Can I turn off autofill for just one column? Not directly. The fill handle and table auto-fill are global settings. For a table, you can override the calculated column by typing a different formula and then using Undo (Ctrl+Z) if Excel overwrites it—but the setting itself applies to all tables in the workbook.
Will disabling the fill handle also disable Flash Fill? No, they are separate settings. Flash Fill is controlled by “Automatically Flash Fill” in Advanced options. You can disable one without affecting the other.
Related
- Excel Sales Tracking Template: Free XLS & How to Use
- Excel Schedule Template: How to Make One
- Excel Sales Tracking Template Weekly: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Weekend vs. Weekday DatesIn Excel, use WEEKDAY to tell a weekend from a weekday: if the date is in A2, =WEEKDAY(A2,2)>5…
- How to Filter in Excel to Find the Exact Data You NeedTo filter in Excel, click a cell in your data, then choose Data > Filter; use the arrow…
- Excel SUMIF by Text Color: What Works and What Doesn’tExcel’s SUMIF function cannot directly test a cell’s text (font) color. To total values by color, use a…
- Calendar Day Formula in Excel: Dates, Weekends & MoreFor the day number within a date, use =DAY(A2), where A2 contains an Excel date; for example, January…
- How to Filter an Excel Sheet by Text: Steps and TipsTo filter an Excel sheet by text in Microsoft 365 for Windows, select a cell in your data…
- SUMIFS Larger Than: Excel Formula GuideTo sum values in Excel where a related cell is larger than a number, use the SUMIFS function…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.