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.
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:
- Expand the Table with AutoExpansion.
- 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.
- Go to the File tab on the Ribbon.
- Choose Options.
- Choose Proofing.
- Click on the AutoCorrect Options button.
- Choose the AutoFormat As You Type tab (if not already selected).
- Check the box that says Fill formulas in tables to create calculated columns.
- 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.
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.
Perfect tip – just what I was looking for. Well communicated!
Jon,
In an Excel table, I can enter a formula in a cell in a blank column and it is automatically copied to all other cells in the column. If I then add another row to the end of the table, the formula is replicated in the corresponding cell of the new row (normal behaviour).
However, if I overwrite every databody cell of that column with, say, different constants, and then add a new row, the original formula still appears in the added new row. Where, then, does Excel keep a record of the formula (obviously not in the databody cells themselves), and can I access it in VBA?
I am ultimately wanting a method of reliably re-entering a (new) repeating formula for a column when some of the entries have been manually overwritten without losing those values in the process. I’m sure this can readily be done in VBA, eg, by saving the manual entries, resetting all the column formulas (using .DataBodyRange.Formula = “=…” to the new one, then selectively re-writing the overwrites from the saved list. However, I am intrigued by the puzzle described above (which might yield a simpler solution), if you know the answer.
I am struggling with the same thing. I had one formula, but have since amended it, but any new rows pre-fill the old/wrong formula. I’m not well-versed in VBA, but am not an Excel rookie either.
Thanks for any help
Thank you