Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

  1. We have disabled the automatic computation of the file
  2. the file contains sometimes 13 columns and 15000+ lines
  3. there is no macro
  4. there is no link to external files
  5. 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?

share|improve this question

migrated from stackoverflow.com Oct 4 '12 at 4:08

This question came from our site for professional and enthusiast programmers.

4  
Well 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 Oct 2 '12 at 10:20
1  
It'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. –  Alexandre P. Levasseur Oct 2 '12 at 11:12
1  
But yes, we'd need to know what formulas you are using. Maybe even a link on the workbook if possible. –  Alexandre P. Levasseur Oct 2 '12 at 11:13
1  
That 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 Oct 3 '12 at 2:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.