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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

Before - type a formula in the column next to the table
AFTER - formula is copied down entire table

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 History Shows Calculated Column Steps for Excel Table

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.

Ctrl + z to undo autofill

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.

Undo dropdown menu

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.

Excel Table Overwrite all cells in this column with the formula AutoCorrect Options

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.

AutoCorrect Options Menu for Calculated Columns in Excel Tables

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.

Undo Calculated Column in AutoCorrection options Menu

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.
fill formulas in tables to create calculated columns

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.

Conclusion

I hope this post is helpful if you are looking to stop automatically creating calculated columns in tables. If you'd like to learn more about Excel tables, you can check out this post: Excel Tables Tutorial Video.

If you have any questions, leave a comment below!

  • What do you recommend if you want to change the formula in a table mid-table? I have a longer table that contains a value that concatenates the year, some text, and an index number. The older data has all had Copy/Paste Values applied and is static. I want to be able to modify the table calculation so data entries for the new year will start again at 0001. I am sure there was a better way to write the formula to begin with, but now I am stuck with what I have. At this time the only way I know to change the formula is by editing the XML file which involves changing the file extension to .ZIP and opening in 7-Zip. Is there a better/less complicated way?

    • Hi Paige,
      One possible solution is to first apply the current/new formula to the entire column and let the auto-fill fill it down. Then paste values to the cells in the top rows that are for historical data. Any new rows added to the table should use the formula that was auto-filled, even though the top rows contain values.

      Another solution is to create a formula with logic to accommodate all rows, so you don’t have to paste values.

      I hope that helps. Thanks again and have a nice weekend!

  • Thank you for the great tutorials which I use in my daily work life and share with others. Your instructions are detailed enough to educate us and simple enough for us to follow along.

    I pray that all is well with you and your family, loved ones and friends during these uncertain times.

    Thank you for keeping us busy as the world stops for a moment to heal itself.

  • Thank god there’s a way to get rid of this. I frequently use maxed out million row tables and the auto fill of a million rows of complex formulas drive me nuts. Now I can manually fill multiple columns at once and go take a nap as the CPU burns.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >