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:
- 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.
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.
- Is this slow-ness expectable with a 166MB file?
- Would it work better if I compiled the data as 65 columns of data plus 78 columns with formulas x 367,000 rows?
- 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.