Big Excel Download

Posted on 2011-02-11
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.
Question by:sanjay-gandhi

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


Expert Comment

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

Author Comment

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?
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 26

Expert Comment

ID: 34875508

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


Author Comment

ID: 34877210

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

LVL 26

Accepted Solution

redmondb earned 250 total points
ID: 34878297


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.


Author Comment

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

Expert Comment

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 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,

Author Comment

ID: 34882516
Thanks a lot.

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Question 17 15
How to display data labels along bottom of a line chart 3 19
Excel Charts: How is this Chart made? 8 29
Excel filter on tab not showing any entries? 5 22
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

810 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