Solved

Big Excel Download

Posted on 2011-02-11
9
515 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

628 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