• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

System Bottlenecks for Working with Large Workbooks

G

garylundblad

Member
I have consistently run into challenges when trying to work with large workbooks, say 15 MG or larger. I have 8 gigs of RAM and an i5 processor. What would be the bottleneck, and/or what can I do to beef up my system to perform better? Also when working with workbooks that have a lot of conditional formatting, or complex formulas, I have the same problem, even if the workbook isn't all that large.

Thank you!

Gary Lundblad
 
15Mb isn't large for your spec. I remember working with 50Mb file without much issue with similar set up.

8Gb is more than the memory limit for 32bit Excel (which I assume you have).

Likelihood is that workbook structure isn't optimal.

CF and multiple dependent formula (especially when working with large range) can be a resource hog.

Standard things you can do to improve performance:

1. Check used range for each sheet. See if any blank rows has unnecessary formatting. If found, get rid of it (delete entire row/column).

2. Avoid use of CF except where it is absolutely necessary.

3. Use named range, structured tables and/or finite range. If any formula is using entire column as reference. Replace it.

4. Get rid of volatile formula where you can.

5. If workbook has pivottable, check and see if there are redundant pivotcache.

There are more, but these are pretty common source of workbook performance issue.
 
Thank you for these ideas. Beyond this, to handle larger workbooks, say 50 MG or larger, tens of thousands of rows of simple data, what about my system could I beef up to make it easier to handle larger files, RAM, processor, other?

I used to have an i7, but they downgraded me when they purchased all new systems about a year ago. Would this affect my ability to handle large workbooks? I'm looking for what I can do to increase my power so issues like you mentioned above aren't an issue, as well as just large data sets, even 100k rows by 15-20 columns.

Thank you!

Gary
 
Shouldn't be an issue with your machine. I still use i5, 16Gb Ram. And up until about 6 months ago, used 4Gb ram only.

If you don't have any add-in specific to 32bit and don't need to connect to 32bit Access DB... you can install 64bit version of Office and that should improve performance.
 
Thank you! I believe we were having trouble with the 64-bit, so our IT made the decision to stay away from it. Hmmm..., I guess the performance challenges must just be Excel then. I would think with over a million rows, and thousands of columns, Excel would be able to handle large amounts of data without a hiccup, but alas this has not been the case for me.

Thank you again!

Gary
 
As a follow-up, our IT dept made some changes, but my system is still really bogging down when trying to create roughly 130,000 VLookup formulas. I am now using the 64-bit version of Excel 2016. Should this task be a challenge? If so, is there a way to beef up my system so it doesn't have such a challenge doing this type of thing? Thank you for any insights! The workbook I'm working in is a binary workbook table, with roughly 130,000 rows by 18 columns. I need to be able to work with data sets this large, and possibly larger without having these challenges.

Gary
 
No matter what system 130,000 VLookup formula will take a lot of resource.

Replace it with MS Query/PowerQuery (SQL statement) or VBA. See link for detail.

analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/
 
Thank you Chihiro! So you're saying that there is nothing I can do to my system to enable it to take 130k VLookup formulas in stride? No processor, no amount of memory, etc... would make any difference?

I apologize for my persistence. It's just hard for me to believe there isn't a system on the market that could do what I'm wanting. I understand that I could use one of these other options you are mentioning, but before I go to my boss with this answer I want to be absolutely sure there isn't a hardward upgrade route that could be taken.

Thank you again!

Gary
 
There may well be hardware upgrade that will impact performance. But it's not a guarantee and hard to say without knowing exact set up. You may end up spending a lot of money and find that it did not meet expectation in the end.

On the other hand, PowerQuery is specifically designed to handle large amount of data (1 million rows +), and it does it efficiently. You can also leverage MS Query, but you will need to know SQL statements and syntax pretty well (as it lacks advanced GUI that PowerQuery comes with).

Most standard LookUp operations are easy to build using query. It will take some practice, but as you learn "M" functions and/or SQL statements, it will become easier.

VBA, is last resort in my opinion. If other tools are able to the job, avoid VBA. As maintenance and succession planning becomes bit of nightmare, if there isn't multiple employee within your organization that's well versed in it (also, it's very easy to make mistakes in code that ends in unexpected result).
 
You must log in or register to reply here.
Back
Top