0
We work on a huge excel file. it contains a sheet setting 22000 lines
We use Excel 2011 on Mac and Windows on Intel i7 with 8Gb Ram.
- We have disabled the automatic computation of the file
- the file contains sometimes 13 columns and 15000+ lines
- there is no macro
- there is no link to external files
- the sheet can't be further split; one sheet contains most formulas and one sheet contains static data + 1 single column computing an id
We wait up to 10 minutes when a copy/paste is made We wait up to 5 minutes when a formula has been changed
Same order of magnitude on Mac and Windows machines.
Do you know any best practice to deal with such size besides developing a tool?
Abdelkrim
Posted 2012-10-02T10:14:46.567
Reputation: 101
4Well it would be very helpful to see what formulas exactly are evaluated. There are tons of optimization ideas that come to mind. Excel is, however, not the most efficient environment to do operations like lookups. That should be done using a database. I don´t see why you stick to Excel, having that many rows. – TheBlastOne – 2012-10-02T10:20:17.377
1It's not always possible to use a database for this sort of thing. For example at work we use the Essbase spreadsheet add-in and that can easily generate 10-30-40k lines of data in Excel. Putting all that data in Access for processing just to spit it out in Excel for reporting purposes (because Access does not let you control your report as much as Excel would) seems overkill. – ApplePie – 2012-10-02T11:12:46.850
1But yes, we'd need to know what formulas you are using. Maybe even a link on the workbook if possible. – ApplePie – 2012-10-02T11:13:51.483
1That size of a data set is not anywhere close to being too large for excel. That said knowing which formula's the formula sheet uses it will be difficult for the community to help you – Pynner – 2012-10-03T02:04:08.173