Yes. It is not only formulas which get copied when you extend a table. Everything gets copied. Read on to find out how much time you are wasting by not knowing this!
How to add more data to a table?
Two ways: Copy paste or manually type (or in case of external connections, refresh the data).
In either case, the Table expands and copies the base formatting. I am sure you have already noticed that. We also saw that if you add raw data, calculated columns are automatically copied and filled up.
But that is not all. There is more – much more!
Table copies EVERYTHING other than values
I am trying to create a comprehensive list here. I may have missed some things but still…
Formulas, Number formatting, Conditional formatting, validations, named ranges (extends vertically), Locked and Hidden status.
Notice that items which are applied while the WHOLE COLUMN is selected get carried forward. If you select few cells in a column and apply any of the above items, those will NOT get auto-copied.
Sparklines also get copied, if they are a part of the table (inserted as a column).
Now when a new row of data is added, you don’t have to update the Sparkline data range.
We will see how calculated columns can be added more easily with intuitive formulas. Something you cannot even imagine!
This article is a part of series: Knowledge Pack – Excel Tables