Link to home
Start Free TrialLog in
Avatar of Sanjay Gandhi
Sanjay GandhiFlag for India

asked on

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.
Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image

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

Avatar of pradeepprahllad

Try an connection from Excel to database and refresh the data
Avatar of Sanjay Gandhi


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?
Avatar of redmondb

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)?


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

Avatar of redmondb
Flag of Afghanistan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,
Thanks a lot.