Big Excel Download

An Excel download from an ERP package is weighing 30MB. There are no images, no links. Only data and some formulae. This opens up very slow in Excel, and on each data change, is slow in calculation. Is there some way by which I can make it run faster? My system has 4GB RAM, and a 2Ghz processor.
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
redmondbConnect With a Mentor Commented:


My laptop has 2GB RAM (sigh), a 2.44GHz, 4-Core processor and a 5400RPM disk. I'm running Windows 7, Office 2010 and the latest Norton Internet Security.

I created a test file with the same spec. as yours, except that my columns must be larger than yours as its size was 52mb.

With the laptop in its normal steady-state (lots loaded, nothing busy), it takes about 30 seconds to open my file. Calculations (a paste special multiplying M and N by an integer) are almost instant. However, that's about as basic an operation as there is  - VLookup, Match, arrays etc. are all going to be significantly slower. What are you doing?

Some immediate suggestions.
 - Saving the file as an xlsb, more than halves its size (20mb) and reduces its load time to 10 seconds. Assuming that you're opening the file more than once, this is definitely recommended. (Let me know if you want to know more about the xlsb format.)
- If your PC is multi-cored (is it?), make sure that Excel is using all (or at least lots!) of them. (In Excel 2010 this on the Advanced tab in options.)
 - While disconnected from the internet, temporarily turn off your anti-virus and see if that significantly impacts on load time. (I'm not suggesting this as a solution, merely more fact-finding!)
 - If columns M and N never change after the file has first been written, then remove the formula in O and P - select the two columns, copy and then Paste Special, selecting "Values".
(BTW, unless absolutely necessary, I never leave formulae on spreadsheets. Instead I copy the formulae in the first data row as comments into the bottom header row and then use paste special to replace the formulae by values.)

Finally, you didn't mention where the bottleneck is - Task Manager and Resource Monitor should make this clear.

Eric ZwiekhorstSAP Business ConsultantCommented:
Try when downloading from ERP to choose Table and not Pivot Table.
That might speed up ..

If you really need a Pivot table make it yourself afterwarts, the macro used in the ERP download is not very efficient.

Kind regards

Try an connection from Excel to database and refresh the data
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Sanjay GandhiFounder, KenhalAuthor Commented:
Dear Friends,
Objective is to find reasons how to handle such a big file. Or, will all big files be slow, then the answer is "yes big files will be slow". So we need to know what are the ways to handle big files, for example read the following solution:

"While doing calculations, change automatic calculation method to manual, and keep adding the data, adding the formulae, and when the job is done, convert calculation to automatic again".

Do we have such solutions available?

Some clarification, please...
(1) How much data (e.g. "one sheet, x rows by y columns")?
(2) What are the formulas?
(3) What format is the spreadsheet (.xls, .xlsx/m or .xlsb)?

Sanjay GandhiFounder, KenhalAuthor Commented:

1) Two sheets in the file, each sheet is a year's data, and has 15 cols x 200,000 rows
2) Last two cols have multiply formulae, one is M x N, and next is N x 10%
3) Spreadsheet format is xlsx

Sanjay GandhiFounder, KenhalAuthor Commented:
Thanks Brian,

All that combined works well. And over this time I was also exploring more. What I explored was, that I pressed Ctrl+End, and it took me to a distant cell where I never visited (consciously, otherwise I must have). Then I deleted all the blank columns and blank rows till that cell after my end of data, which helped me reduce the file size.
Next was loading problem, when I tried what you have suggested, it significantly reduced my load time. I worked on every suggestion individually, and every suggestion of yours worked to contribute to solve my problem.
Therefore, I m closing this question here. Yet, if you could still throw some light on binary format (xlsb), it would be great. Thanks, all points yours, dear.
Hey, many thanks, Sanjay. Glad this is working for you.

Good catch on the extra columns/rows. Excel 2007 has significantly improved the handling of the "real" last cell, so I suspect that either your ERP is outputting spaces or, more likely, isn't getting the xlsx format exactly right. It would be interesting, as a one-off, to copy the real cells (not sheets) to a new xlsx, save that and see if its size is significantly different.

While looking for a good link on xlsb, I came across the following useful link on Excel 2007 performance We've covered the highspots, but it's worth a read.

My take on xlsb...

Unlike the XML-based xlsx/m formats, xlsb uses a Binary Interchange File Format - as does xls. As the name indicates, the data is binary thus cutting out the XML size and conversion overheads - an xlsb can be read almost straight into memory.

I've been happily using xlsb's for a couple of years without any problems, but there are a couple of reasons for not using the format for every spreadsheet...
 - If a spreadsheet goes corrupt, you are significantly more likely to be able to recover something from an xlsx/m than from an xlsb.  
 - When opening an untrusted xlsx, you know that at least it won't run a macro. There's no such mechanism for xlsb's, so any xlsb may have a macro.
 - Programs (such as your ERP) can comfortably write and update xlsx/m files without having Excel 2007+ installed. This would be extremely difficult to do with an xlsb (which is probably why your ERP is writing huge xlsx's).

All the best,
Sanjay GandhiFounder, KenhalAuthor Commented:
Thanks a lot.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.