4

I have an Excel spreadsheet with 5000 rows and columns till AY (size 12MB). Except for the first six columns, the rest contain either vlookups or other formulae. All the vlookups are in a separate Excel worksheet. I have changed the Excel setting to manually update the links and calculate formulae. Now every time I try to update the links, either Excel hangs or it takes something like 15 minutes.

Right now I am working on splitting the worksheet into four instances; where I will update four different Excel sheets in sequence. But it requires manually copying index columns to next stage. It takes 3-4 hours to update the links. Yesterday I started getting error messages that:

Excel does not enough resources to complete the operation.

I was left with no choice, but to split the entire operation into four small steps requiring manual intervention.

I looked at INDEX. Not really applicable on my case. Any ideas on how I can get it done quickly?

1
  • 5
    That sounds like a good candidate for a database application (ms-access or other) rather than a spreadsheet. Excel really wasn't made to handle massive amounts of data.
    – Stewbob
    Aug 26, 2009 at 23:15

9 Answers 9

5

5000 rows by 50 columns is not a large worksheet. 12MB is a perfectly reasonable small-to medium file size and you should have no need to do anything so drastic as reworking to a database or something similar.

From your description, the problem appears to be the links. How large are the referenced workbooks? Are they recalculating when you link to them? I like the pre-opening idea: you can see what's happening then.

Is your memory usage (look at Task Manager) exceeding your available physical RAM? Once memory has to be swapped out to disk, things start to slow down a lot.

Beyond the pre-opening and sorting help, I'd suggest taking a look at the non-lookup formulae: can any be converted into array formulae and called once per calculation instead of once per row?

On the VLOOKUP thing: have you tried using INDEX(value_range, MATCH(lookup_key, key_range))? There are situations where it's faster.

You don't say if you have any VBA. If you do, look at how many times VBA functions are called and how much time they take. VBA code that makes a lot of reference to Excel objects can be expensive.

4

If there is any data in your file that does not need to be conditional and will remain the same (e.g. a person's birth date) then after your first vlookup to get the data, 'Copy' and 'Paste Special - Values' the entire column/row to stop the file from recalculating those cells every time you run the calculation. Running a vlookup on data sourced from another vlookup I have noticed takes quite some time.

2

Unfortunately, that's the way it is with Excel spreadsheets... VBA isn't exactly the fastest option out there.

You might want to consider migrating the data to a Microsoft Access database.

2

Open the workbook(s) you are linking to before opening the workbook containing the links.

And sort the data then use the sorted option in VLOOKUP (use True for the last parameter).

2

I use Office 10 to get around this.

  1. I opened my huge file, re-saved it as a file type Excel workbook 1997-2003.
    • It prompted a message asking me something (I can't remember).
  2. I clicked OK and I had a new Excel 1997-2003 file.
    • The file went from 25MB to under 1MB.
  3. Then I just re-saved it again this time from the old 1997-2003 file to a new workbook and it got even smaller.

I have had this problem with multiple workbooks and this has always solved it for me.

0
1

Maybe get a faster computer or upgrade your RAM

1
  • 1
    this only goes just so far, sadly: Excel won't take advantage of more than one CPU or CPU core :(
    – warren
    Sep 12, 2009 at 11:12
0

I agree with Mike Woodhouse. This is not a lot of data.

As a test I would make a new spreadsheet with the data from both spreadsheet merged into 1 new spreadsheet. This will tell you if the issue is happening because the data is in two separate spreadsheet. This should not be an issue but it will be a good test.

0

I don't know if this is any help for your situation since it involves database links, but I had the same problem except that it was taking way too long to update a spreadsheet running a database query.

The way I sped things up by a factor of 15 was by first making the query select the necessary data instead of making Excel filter it. Second, in the Connection Properties window of my DB connection, I checkmarked the option "Remove data from the external data range before saving the workbook." This helped tremendously so that my file was not continously growing in size with the larger amounts of data I may be querying up.

Now my entire refresh takes around 2-3 seconds instead of 30-45.

0

In my experience I've found linking to external files to be the slowest process of all. I've read that this is due to Excel having to re-update the calculation model each time through a number of different linked files, rather than just one.

I suspect from your first post that that was what was slowing down the calculating to begin with. Did you try moving all the data into the same working file and updating all the links to be internal to that file?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .