Excel with table in takes up to 15 minutes to close the file

[benoj2005]

Hi

I've got a model which has a table in that's 369 columns and up to 10,000 rows. The model closes instantly when the data isn't in a table, but as soon as I put it as a table then it takes along time to close. For example if it's got about 1k of rows in then it'll take around 15 minutes to close the file and that close time will scale up and down with the number of rows.

If I resize the table on that 1k rows of data so the table is just two rows but leave all the data and formulas in tact but just outside of the table range then that 15 minutes to close becomes instant closing. At first I assumed it was due to the formulas in the model but then came across the resizing table trick to close the file which makes me think it's just the table causing the problem.

I need the data in table as it has power query running off the back of it. Appreciate it might be hard to troubleshoot without the file, I can ammonise all the data in it and upload if required but is there anything obvious anyone can think of? I heard >500 row tables causing performance issues with the old excels but I'm 365 and didn't think that was still an issue?

Thanks

[romperstomper]

That's a very big table. Do you actually need it in the same workbook as whatever you are doing with PQ?

[benoj2005]

Ideally yes. The table is used to input a list of employees for each business area and then costs them up for a budget build so the user needs to see all the fields in the table. The power query running off the back of that table manipulates the data into a suitable format for us to then load into our ERP.

[romperstomper]

I'm not seeing why the PQ and the table need to be in the same workbook then?

[benoj2005]

I suppose we could have two workbooks but question would be why, what does that achieve. Are you suggesting having the power query in that workbook is what is causing the slow closing? I'll test closing the file with the power removed later if that's the case

[romperstomper]

No, what I'm saying is that if the PQ is not in the same workbook, then the data doesn't need to be a table at all. (Personally I hate tables for working data)

[benoj2005]

Ah I see. Ok I thought the data had to be in a table to use power query & power pivot off the back off it. I also have 20 business areas filling these in so use power query to consolidate the data from one folder. I'm struggling to visualise the data not in a table but being able to use power query & power pivot functionality from the data. How do you get round this?

[romperstomper]

If the data source is another file, PQ can simply pull the data from a worksheet. It doesn't need to be a table. You can do it in the same workbook by using a named range rather than a table.

[benoj2005]

Ah interesting. I wasn't aware of the named range possibility with PQ.

So are we concluding that using tables is still inefficient even in excel 365 in terms of performance?

[romperstomper]

I couldn't say as I only ever use them for static data really (no formulas) or occasionally for small data sets with a few formula. I'd never use them for anything as large as your data set - that is a LOT of columns.