• 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.

Slow Vlookups in Large Spreadsheet

M

mkeisha

New Member
I am using Excel 2007 and have a spreasheet with 400,000 rows. When I try to do vlookups and/or filters, it's taking about 20 minutes or so to complete each command. The file size is over 100 MB. How can I speed it up? Is this even possible?


I am working on an HP Pavilion dm4 laptop with an Intel Core i5 CPU and 4 GB of RAM. Will installing more RAM help?


Thank you.

Mary Ann
 
While it is a large amount of data you are dealing with, that shouldn't be the only thing slowing you down. Are there an equal amount of VLOOKUP's being performed? Perhaps we can cut down on the formula use. Things get especially slow if you filter things that lots of formulas are trying to use.

Consider working in manual calculation mode?

Should data be migrated to Microsoft Access, and then run queries from there?
 
There are techniques for dealing with this size of files.


It really depends on what your trying to do


Have you thought of using Power Pivot?

Have a look here

http://www.powerpivot.com/


Moving to Excel 2010 will also assist you as 2007 is terribly slow in quite a few areas
 
Hi, mkeisha!

Even agreeing with Luke M and Hui's comments, I'd follow the path suggested by bobhc -aka b(ut)ob(ut)hc-. Move data to Access.

Regards!
 
To add to the debate, It is also important to know if you are doing this routinely or as a one-off project.
 
You must log in or register to reply here.
Back
Top