TIPS: Corrupt Excel Files and Prevention

I’m sure some of you would have experienced at some point Excel showing the following error message:

”Damage to the file was so extensive that repairs were not possible.  Excel attempted to recover your formulas and values, but some data may have been lost or corrupted”

I’m here to break some myths surrounding Excel file corruption.

MYTH: My Excel file corrupts because the file is too big.

WRONG!

The corrupted file above is only 1.8Mb

What is the real reason for my file to go corrupt then?

Your Excel file becomes corrupt mainly because it now requires more resources (RAM) than what your computer has. Your version of Excel can process up to a limit of 1GB of RAM computation for Excel 2003 and 160MB for Excel 2002 (check your version). Your PC however has available usually around 500MB of RAM (after taking into account other programmes that you are using e.g. Outlook).

How do I reduce the amount of resources that my Excel file uses?

There are 2 things that you can do:

  1. Avoid using symbols in your tab names (e.g. “@”, “(“, “)”, “-”). (This is the easiest step, but least effective)
  2. Cut down on your VLOOKUP formula (this is the hardest because this formula is so prevalent, but it is the biggest resource hog in Excel)

What are the actions I can take to cut down on use of VLOOKUP formula?

  1. Keep lookup range on the same worksheet
    If your range is small, keep it on the same worksheet instead of creating a separate tab for it.
  2. Sort the lookup range and use “type 1” lookup (beware of error potential of unmatched lookups)
    Most of the time (in fact all the time), we use “type 0” lookup, i.e. the zero in the last variable VLOOKUP(A$,$C$3:$D$9,2,0). This actually forces Excel to search the entire block of data range, clogging up RAM everytime Excel tries to calculate, given our large data range and number of formulas.
    “Type 1” lookup will instead stop when the match is found, or the closest match, but this formula works only when the range is sorted in ascending alphabetical order.
    WARNING: Use with caution, however, as “type 1” lookup will not flag error if there is no exact match. One way to workaround is to use the MATCH formula at the side to look for an exact match.
  3. Cut down on lookup range size
    Some of us, due to the large data range, will conveniently select the whole column as our lookup. DON’T! As my point above, this combined with “type 0” lookup actually forces Excel to search >65k rows for each and every computation cell. That’s a lot of search to do!
    So the rule is, VLOOKUP(A$,$C$3:$D$9,2,0) is better than VLOOKUP(A$,$C:$D,2,0), even though they will give you the same result. The former will only lookup 7 rows, while the latter is looking up 65,536 rows!
  4. Use alternative formula (e.g. INDEX + MATCH, OFFSET)
    VLOOKUP is popular because everyone knows it. But if you know you’re going to work on a large extensive model, it could be worth your while to explore other formulas which might give you less heartache.

That’s it for now from your resident Excel expert. Do apply the tips above and have fun in your models! J

[pre 0]

Advertisements

Tagged: , , , , ,

One thought on “TIPS: Corrupt Excel Files and Prevention

  1. Catherine Lee November 23, 2009 at 7:28 am Reply

    Thanks, Anthony! Great tips!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: