How to handle large data sets (180 MB file)

[stockmarket123]

Hey guys

our company uses MS Office 2010. We have a 180 MB excel file that imports all our sales data from our ERP via MS query, as well as links to other external Excel spreadsheets to gather company data It constantly crashes despite rebooting multiple times and gives error messages such as "Excel cannot complete this task with available resources. Choose less data or close other applications".

I have closed everything except Excel lol

Besides the crashing, it is also extremely laggy. I am looking to improve on this system which includes building a SQL view that centralizes all the required data and perhaps running the data through Power Pivot instead, and then running the reports at that point onwards. We will also upgrade to Excel 2016 if that will play a part.

Does anyone here agree with my assessment? or perhaps have better ideas? I don't have alot of experience with other products so hope to solve this problem with Microsoft products. Please note, I am intermediate/advance user of Excel, and starting to learn Power Pivot/BI tools.

Thank you kindly

 

[mole999]

what file extension is the file working as (that might effect the file size).
data drawn into a database might be more efficient. then export your data to display.
a crashed file is useless for everyone
have a look at VOLATILE and see what of your formulas update on each change

 

[stockmarket123]

Hi Mole

Thanks!!

Yes, I just checked the extension, its a regular, Excel Workbook (.xlsx)

I am not too sure what the VOLATILE function does, but the formulas that reside in the workbook include

1) multiple nested IF THEN statements with WEEKNUM functions
2) multiple VLOOKUP statements with references to other workbooks on our network
3) YEAR and MONTH and DATE formulas
4) multiple PIVOT TABLES contained over several tabs
5) MS Query to pull the info from our ERP system which takes a long time to update.

please let me know if you have any further suggestions, but I am working with our ERP provider to centralize all our data into one virtual SQL table, and then go from there.

Thank you

Winston

 

[ScottR]

As you surmise, the root cause here is relying on Excel to do all of your heavy lifting where as SQL Server Integration Services (SSIS) is pretty much designed to address the type of business problem you've described.

 

[mole999]

save your file as xlsb

volatile is like a cascade, you make a change to a cell and lots of the workbook then has to update, have a read here > Volatile Excel Functions -Decision Models