Higher speed of MS Excel

[smsko]

Hello,

in the last few month the volume of the sales data extracted from the SQL server
and analysed in Excel has grown so much, that current computer that I use
is becoming too slow to handle +200MB xlsx files.

I'm merging tables and creating pivot tables to view the data.

What should be the done in order to get better speed?

PowerPivot? Installation of Excel on one of the servers that I have in the company
or maybe is there an option to lease cloud server with MS Office installed that can handle
so much data?

 

[mole999]

Whats the size of excel before you import the data, is excel holding change history, are the files shared, do you actually need to download all the data, how many formulas / formatting are you using

 

[smsko]

Whats the size of excel before you import the data, is excel holding change history, are the files shared, do you actually need to download all the data, how many formulas / formatting are you using

I'm building the tables in excel by importing data from the sql. Sheet contains all data from all of the items from all of the invoices that we have sold in our store. Data is updated
during the night from all of the locations and after that query pulls data from sql to excel.
Change history is not important. Files are not shared. I need to download all data due to daily change and updates.

Formulas? There are few formulas and data is stored in tables.

Pivot tables are created from those tables.

I hope this has provided a bit more details.

 

[Macropod]

What are you doing with the data? If you're only analysing them and keeping the results, you'd possibly do better to do all the processing in memory rather than on worksheets. In the past, I've analysed data sets containing ~12M records this way and found it was many times faster than loading the different sub-sets of the data (there were 150 of them, with ~80K records each) and this was in the days of Excel 2000, which only supported 64K rows/sheet.