How to Prevent or Disable Auto Fill in Table Formulas

Bottom Line: Learn how to prevent Table formulas from filling down automatically with a quick keyboard shortcut, plus how to toggle the autofill formula settings.

Skill Level: Beginner

Download the Excel File

If you'd like a copy of the file I use in the video, you can download it here.

 Prevent Table Formulas From Filling Down.xlsx (19.3 KB)

Preventing Autofill on 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.

Let's look at the different ways to stop or prevent the auto fill.

What Happens When a Calculated Column is Created?

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

If the formula is to the right of the Table, Excel will:

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.

Undo the Auto Fill

The easiest and fastest way to undo the autofill is by using the keyboard shortcut Ctrl + Z. This always undoes 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.

You can also use the Undo button in the Quick Access Toolbar to accomplish the same thing.

The little drop-down arrow next to the Undo button opens a menu that shows you the last few actions Excel took. In this image, you can see that the last action Excel took was to fill the cells in the column with the formula. So hitting the Undo button (or typing Ctrl + Z) once will undo that fill action.

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. It essentially does the Fill step again.

You can also bring the auto fill behavior back by clearing/deleting all cells in the column and typing a new formula.

AutoCorrection Options Menu

Another option for undoing the fill is by using the AutoCorrect Options Menu. The button appears to the right of the cell after you enter a formula in the Table column.

There are three options on this menu.

1. Undo Calculated Column

Undo Calculated Column accomplishes the same thing we've seen above with Ctrl+Z or Undo.

2. Stop Automatically Creating Calculated Columns

Stop Automatically Creating Calculated Columns does exactly what it says, but it's an application-level setting. That means it will stop doing this for all tables on all sheets in all files, going forward.

So you only want to choose this feature if you really don't like having columns auto fill for Excel tables.

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.

1. Go to the File tab on the Ribbon.
2. Choose Options.
3. Choose Proofing.
4. Click on the AutoCorrect Options button.
5. Choose the AutoFormat As You Type tab (if not already selected).
6. Check the box that says Fill formulas in tables to create calculated columns.
7. Hit OK.

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.

It's a bit of a weird UI design for such an important and useful feature. I hope these options are added to the Table Design tab on the Ribbon in the future.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1