Solved

Big Excel Download

Posted on 2011-02-11
9
514 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:sanjay-gandhi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34869993
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

Eric
0
 

Expert Comment

by:pradeepprahllad
ID: 34870372
Try an connection from Excel to database and refresh the data
0
 

Author Comment

by:sanjay-gandhi
ID: 34873436
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?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 26

Expert Comment

by:redmondb
ID: 34875508
sanjay-gandhi,

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

Thanks,
Brian.
0
 

Author Comment

by:sanjay-gandhi
ID: 34877210
Thanks,

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.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 250 total points
ID: 34878297
Sanjay,

Thanks.

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.

Regards,
Brian.
0
 

Author Comment

by:sanjay-gandhi
ID: 34881586
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.
Sanjay.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34882412
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 http://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx. 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,
Brian.
0
 

Author Comment

by:sanjay-gandhi
ID: 34882516
Thanks a lot.
Sanjay.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question