Table has many benefits. One of them is Table AutoFill Formulas. If you add a formula in any table column, it copies the formula automatically. Further editing of the formula also is automatically copied to all other cells in the column. Sometimes, the AutoFill stops working. How to re-enable this feature? Here are the instructions
(Reading time 3 min)
Contents
How to enable Table AutoFill Formulas
You may think that this option will be in File – Options – Advanced. It is NOT. This is a part of AutoCorrect set of features. Therefore, you must go to File – Options – Proofing section. Click the AutoCorrect Options button.
Activate the Fill formulas in tables to create calculated columns option. That’s it.
How did Table AutoFill get disabled?
By default, Table AutoFill Formulas is enabled.
Who disabled it? Most probably, you yourself – without realizing the impact.
Here is what happens. You add a calculated column to a table. Table AutoFill Formula copies the formula across the column.
In some cases you DO NOT want it to AutoFill. Why? Because you want only one cell to have a different formula. This is done by using the icon which appears immediately after Table AutoFill Formulas action completes. When you click on the icon, it allows you to Undo the action. Now the edited cell has different formula and rest of the cells in the column have the original formula.
This UNDO column action disables the Table AutoFill Formulas feature only for that particular column. Sometimes we choose the other option called Stop Automatically Creating Calculated Columns. This deactivates the Table AutoFill Formulas features for all tables in future.
With Table AutoFill Formulas feature deactivated, adding a formula in a table column does not auto-fill at all. Formula goes only into the current cell. It does show an icon (SmartTag). This offers only a manual action to fill the entire column with the formula. There is no option to re-enable the Table AutoFill Formulas option. This option needs to be used manually every time you add or edit the formula – which is very irritating.
To re-enable Table AutoFill Formulas, you must use the Proofing option as shown below. In general, it is not a good idea to deactivate AutoFill option.
Learn Excel Tables
Read the entire series of 12+ articles about how important Tables are and how to use them effectively.
Excel Tables – Knowledge Pack (live book).
2 Responses
thanks for this Excel tip. You wouldn’t think it Autofill would be found there
Any setting which can be customized and which needs to be remembered appears in Files – Options.