Solved

Big Excel Download

Posted on 2011-02-11
9
508 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
9 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
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
Comment Utility
Try an connection from Excel to database and refresh the data
0
 

Author Comment

by:sanjay-gandhi
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:sanjay-gandhi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks a lot.
Sanjay.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now