Copy & Paste Values into Excel Defined Table Really Slow

Question

I have an Excel defined table of ~20K rows and ~20 columns.

Every month I need to refresh the data set. But when I copy paste values in, my Excel crashes.

However, if I convert the table to a named range, the copy paste takes <10 seconds.

Has anyone else had this issue? Does Excel need to create/update some sort of index with every value you put into a table? That could help explain why it takes so long when copy & pasting data.

Answer 1

As far as I understood, in tables, with AutoExpansion, the lines are incorporated 1 by 1, slowing down the process a lot especially when there are calculations (but not only).

Have you tried to turn off the AutoExpansion of tables, then copy-paste your data in the normal range under your table and then manually expand your table to include the new data ?

Answer 2

Had the same issue and Google led me here. Unfortunately I haven't found a way to disable AutoExpansion as @Aurélien suggested.

The solution I ended up with is pretty simple: resize the table first to the target size, then paste the data, so that Excel won't auto-expand for you.

I used this in a piece of code that union two tables into one, ~8000 rows, and it speed the pasting up materially. I suspect there is some inefficiency (resizing row by row perhaps) in the auto-expansion.