Problem:
Recently I have been working with fairly large sets of data.
However, Excel seems to handle these very slowly (it takes 3-5 minutes just to open or save the file).
Spreadsheet details
- Rows: 50,000
- Coloumns: 90
- Worksheets: 1
- File size: 157mb.
The sheet consists of pure data.
No formulae or VBA script has been added yet.
There are no cell references (eg =H3) or arrays.
Specs:
Office 2010 32 bit
Processor : i7 3.4 GHZ Quad Core
Ram : 6 Gb
Windows 7
Steps taken thus far
I tried saving it as an Excel binary (.xlsb) file, which helped a bit (and reduced file size a bit as well).
However, 50,000 rows is nowhere near the limit of Excel 2010 (which is 1,048,576 rows), yet it's really slow. I also remember older versions of Excel being able to deal with large chunks of data relatively fast.
Is there any way of speeding Excel up?
Best Answer
I decided to try and mimic as closely as possible your scenario. To do this I:
However, for me this spreadsheet was only 24MB in size, which is only about 15% of the size yours is, so I cannot account for that.
Unfortunately, the only context you've provided is the read/write speed for opening/saving your spreadsheet, so this is all I could test for. My results are as follows:
Now, since my 50,000 rows and 90 columns of data only amounted to 24MB, my data is obviously going to take a lot less to save and load than your 157MB file (which is about 6.5 times larger).
However, as you can see, the speed of opening and saving files is not dictated purely by the size of the file itself. Working with an SSD is a lot faster than a local hard drive which in turn is a lot faster than a networked drive. Also, the type of network has a huge impact on data transfer speeds (e.g. Gigabit ethernet is a lot faster than fast ethernet).
Unfortunately, the closest computer I could find to run this test had the following specs:
Summary
In a nutshell: