Fixing Corrupt Excel Files

I had an Excel file go bad on me today.  I spent a few hours getting it all wired up, only to to open it up later and be faced with the error;

Very frustrating!

I did learn a bit about accessing/fixing corrupt Excel files though.  Here are some tips I found to deal  with a corrupt Excel file.

I. Excel has a built-in "fix" feature.  When opening a file in Excel, you can ask Excel to try repairing the the file by selecting  Open and Repair in the File|Open dialog box.

That did not work for me...

II. If you just need to view the data in the Excel file, you can download the free Microsoft Excel File Viewer to try viewing the data in your corrupt file.

That did not work for me either...

III. Try opening the file in Microsoft Word.  For me, the file opened in Word, but it came up as gibberish.

Still no luck, I need to keep trying...

IV. Try opening the file in Google Docs.  Google docs is a web based set of Office-like applications that can read and write Microsoft Office formatted files.

Nope, Google Docs could not open my file...

V. In a new Excel worksheet, you can try linking to the corrupt workbook and pull the data out.

1. Start Excel.
2. From the File menu, Select Open.  Browse to the folder with the corrupt file, then click Cancel.
3. Select File | New.
4. In Cell A1, enter =FileName!A1.  Where FileName is the name of your corrupt file.  If the "Select Sheet" dialog box appears, select the appropriate worksheet and click OK.
5. Copy Cell A1 to the cells with the data you want to pull from the corrupt workbook.

This worked for me, woo-hoo!

If you ever encounter a corrupt Excel file, I hope one of these tips works for you.  I'd highly recommend giving these tips a try before buying one of the many Excel "repair" applications that claim to fix corrupt Excel files.

Excel Tip for Selecting a Range of Cells

A while back a colleague of mine turned me on to a great Excel keyboard shortcut for selecting a range of cells. The key combination is CTRL + SHIFT + END. By holding these 3 keys together Excel selects a range of cells starting with your current cell down to the last used cell on your worksheet. This is quite handy if you frequently use lists, data-tables or recordsets in Excel.

Consider this example. Using the data-set shown below, with your cursor in cell A1, CTRL + SHIFT + END produces a selected range from A1 through C11.

CTRL + SHIFT + END is smart enough to know that the last row containing data is 11 and the last column containing data is C.

This is one of my favorite tips for Getting Things Done with Excel.

If you are feeling kinda nutty, try CTRL + SHIFT + HOME. It works the opposite of CTRL + SHIFT + END. Try it, and you'll understand I mean by "opposite".

Doing research within Microsoft Office Apps

An often overlook feature in Microsoft Office Applications (Word, PowerPoint, Excel, Outlook) is the Look Up function.  With the Look Up function you can;

• Look up a word in a dictionary (with pronunciation and definitions)
• Get a list of synonyms and homonyms from an online Thesaurus
• Translate a word or phrase to a different language
• Find references to a word or phrase in an Encyclopedia
• Lookup a stock quote or find a company profile

...all within Microsoft Office! 

To use the Look Up function simply highlight a word or phrase, right click and select Look Up.  Your MS Office application will open up a pane on the right and display the results.  For example, here is a quick Look Up on the word "Productivity".

How cool is that! 

This feature has been around since the early versions of Microsoft Office.  What amazed me is that this feature does not get a lot of press.  In fact, I just stumbled across it recently.  If you have not yet tried the Microsoft Office Look Up function, I'd highly recommend it.

Related Links:

• Microsoft Office Timesaver: Using the Research Pane @ Dummies.com

Zoom Tip for Microsoft Excel

I use Microsoft Excel quite a bit.  I like the zoom function (View -> Zoom) to fit as much of the worksheet on my screen as I can.  For you Excel zoomers out there, I have a quick zoom trick for you.  This tip requires a mouse with a scroll-wheel. 

To make the worksheet magnification becomes smaller, hold down the CTRL key and scroll back (toward you).

To make the worksheet magnification larger, hold down the CTRL key and scroll forward (away from you).

This is a quick and handy way to find the optimum zoom magnification for your worksheet (and eyes!).