Preventing Auto Fill in Excel Tables

When working with Excel Tables, columns will automatically fill down when you create a new formula in a column next to the table. This is called a Calculated Column.

• Calculated columns in Excel tables are a fantastic tool for entering formulas efficiently. They allow you to enter a single formula in one cell, and also that formula will automatically expand to the rest of the column by itself. There’s no need to use the Fill or Copy commands. This can be incredibly time saving, especially if you have a lot of rows. And the same thing happens when you change a formula; the change will also expand to the rest of the advised column.

When we add a formula in or next to a Table, Excel takes a series of actions to create the calculated column.

1. Expand the Table with AutoExpansion.

2. Fill the formula down to all the cells in the column.

These actions can be seen in the Undo History drop-down. The little drop-down arrow next to the Undo button will open a menu that shows you the last few actions.

Undo the Auto Fill

You can undo the auto fill by using 3 ways as below

This always undo the last action taken, which in this case was the automatic filling of the columns with the same formula that was used for the original cell.

1. Keyboard shortcut – Ctrl+Z (easiest and fastest way)

2. by hitting the little drop-down arrow next to the Undo button which shows the last few actions.

3. AutoCorrection Options

The button appears to the right of the cell after you enter a formula in the Table column

Auto Fill Becomes Temporarily Disabled for the Column

After you undo the Auto Fill in the column, Auto Fill becomes disabled for that column. When you input another formula in the column it will NOT fill down.

However, you will see an option in the AutoCorrect Options menu to “Overwrite all cells in this column with this formula”. This will replace any formulas in the column with the formula you just entered.

AutoCorrection Options Menu

There are 3 options here:

1. Undo Calculated Column

It accomplishes the same thing we’ve seen above with Ctrl+Z or Undo.

2. Stop Automatically Creating Calculated Columns

It does what exactly says but it is Application-Level Setting. That means it will stop doing this for all tables on all sheets in all files, going forward.

The option to automatically fill formulas to create calculated columns in an Excel table is on by default. If you don’t want Excel to create calculated columns when you enter formulas in table columns, you can turn the option to fill formulas off. If you don’t want to turn the option off, but don’t always want to create calculated columns as you work in a table, you can stop calculated columns from being created automatically.

3. Control AutoCorrect Options

Control AutoCorrect Options will open the AutoCorrect Settings window where you can turn the calculated columns (Auto Fill) on or off.

If you select that second Stop option (either inadvertently or on purpose) and you later want to reinstate the Auto Fill feature, this is how to open the AutoCorrect Settings window:

> Go to the File tab on the Ribbon.

> click Options

> click Proofing

> click on the AutoCorrect Options button under AutoCorrect Options

> Select AutoFormat As You Type tab (if not already selected).

> Check the box that says Fill formulas in tables to create calculated columns Under Automatically as you work.

Now press the OK button.

It’s good to know how to get to this window because once you turn Auto Fill off, you won’t see the AutoCorrect Options Menu anymore until you turn it back on.

I hope this post is helpful if you are looking to stop automatically creating calculated columns in tables. If you like to learn the Benefits to use Excel tables, you can check out this post:
Benefits to use Excel Table