How to improve performance with large excel files

Question

I am working with Excel 2007 on a Windows 7, Core i5 2.53GHz (2 cores, 4 threads), with 4GB DDR3 @ 533MHz RAM.

I need to extract and organise data from a huge database and, to achieve it, I have a worksheet where I:

1. a. paste part of the raw data into 6 columns x 36,600 rows
   b. extract the data I’m interested in from a) and work on it with 8 columns x 36,600 rows

I then copy the results of the formulas of 6 of the columns in 1.b) and paste-value them into a second worksheet, where I am going to compile all the data I need and analyse it.

2. a. for each group of 6 cols x 36,600 rows, I will need to add 5 columns of formulas (mostly small additions with no more than 2 values each, and some INDEX-MATCH);
   b. the total amount of data will end up at 130 of these groups, so: 780 columns of data plus 650 columns of formula x 36,600 rows.
   c. once I have compiled and analysed all the data, I will turn it all into values – which I will later use with LOOKUPs/INDEX-MATCHes formulas.
   d. Neither worksheet has automatic formatting, both have minimum formatting (coloured cells to identify those which have formulas), if I click CTRL + END it goes straight to the end of the range in use.

So, my problem is that, as of now, I have built the formula structure to which I will add the data bit by bit, but the file is 166MB and is using up all of my processor’s resources. I have set calculation to manual, but it still takes up to 5 minutes to open, close or save this file.

Questions:

1. Is this slow-ness expectable with a 166MB file?
2. Would it work better if I compiled the data as 65 columns of data plus 78 columns with formulas x 367,000 rows?
3. As I am in the process of acquiring a new laptop, would a better processor overcome the difficulties? (I am thinking on the lines of a QuadCore i7 with 16GB RAM)

NOTE concerning Question 3: I am not looking for hardware recommendations as I am in the process of acquiring the machine described above. I want to know wether I may expect improved performance when working with this file.

Answer 1

This is just to settle the questions I ask and which were not answered in the comments

2) Yes, less columns with more rows is more efficient.

3) The new laptop helped. It still takes some time to open and calculate the worksheet, but it's much faster than before (a fourth or less of the wait time).

Answer 2

Excel is extremely memory intensive, and inefficient when working with large arrays. Using SSD or ram drive does not help because most will be resident in memory and not the hard disk. Keep track of the memory usage in Task Manager, and try to keep the ram usage in excel to under 2gb. Break up the data into smaller files, and use a collating or summary worksheet to call the data as needed instead of opening up everything one time. Typically a 55mb file may take up 1gb memory in excel, and take 2-3min to open depending on the formula structure, so 5mins to open 166mb is not unexpected.

Save to binary files, and that will improve loading and saving the large files. Use the fastest CPU and memory possible. To test if your excel file is scalable to CPU cores, run a calculation with a timer in VBA using just 1 CPU core, and then rerun using 2 cores. If the speedup is close to 100%, then more cores will also help. Use a desktop instead of a laptop.