Resources

I have had several Excel spreadsheets go bad on me with varying success in opening the spreadsheet. Excel spreadsheets become corrupt for a variety of reasons including unexpected shutdowns, computer crashes, viruses, and macros. Sometimes, an object within the spreadsheet causes the problem.

It is possible to open corrupt spreadsheets. Many software utilities exist for that very purpose. However, before you buy one, you can try a few of these do-it-yourself steps. Each of the following is a separate technique to try, not a sequence. Try the various options and hopefully open your spreadsheet.

• If you’re on a network, try opening the spreadsheet on a different computer. If it works, re-save the spreadsheet with a new file name. See if the new file will open on your computer.

• Disable macros. If your corrupt spreadsheet has macros, you should be prompted by a warning. Choose to disable the macros and see if the file opens.

• Another option to try is to open Excel in “safe mode” before trying to open the spreadsheet. This disables VBA and add-ins which may be interfering with your document. To open Excel in safe mode, go to the Start button, choose Run and then Browse. Look for the Excel.exe file which will be in the Program files>Microsoft>Microsoft Office folder. Once you find the path, add a “/s” to the end so that your path looks something like this (depending on the location of your version of Office):

C:\Program Files\Microsoft Office\Office\Excel.exe /s

• Set Excel to manual calculations before trying to open a corrupt spreadsheet. To do this, go to Tools >Options >Calculation tab. Find the calculation area and select manual. Now, try opening the file. (In Excel 2007, go to the Formulas tab and find Calculation options (on the right side) and select Manual from the list.

• If you can open the spreadsheet, consider saving it in the SYLK (symbolic link) file format. This format only allows you to save one worksheet at a time, so if you have multiple worksheets, you will need to save the file multiple times by going to each worksheet and saving the “active” worksheet as an SYLK file.

• Try opening the spreadsheet by using a different version of Excel or different spreadsheet application altogether (such as the free Calc program from OpenOffice.org.) This sometimes works because many newer versions of spreadsheet programs have better file recovery features.

• If you have Excel 97 (and only Excel 97) you can use the Microsoft Excel 97 Recovery Macro to recover worksheets into a new workbook. This macro is available for download at the Microsoft Web site.

• Try opening the spreadsheet in Microsoft Word or importing the data into Access. You will need an Excel converter in order to open an Excel spreadsheet in Word. You can download the converter from Microsoft.  You may be able to at least recover your data using this option though you’ll lose your formatting and formulas.  

• You can also use the Excel viewer to open corrupt workbooks. Like many of the options, you will not be able to recover formulas, just data.

If you have Excel 2007, you can easily publish your spreadsheet as a PDF file thanks to the added capabilities in this edition of the program. If you have an earlier version of Excel, you still have options for doing so such as using a PDF conversion program or service.

First, let’s look at using Excel 2007 to convert your spreadsheet into a PDF file. I have done this a few times. For example, one time I needed to send a list of names, addresses, and phone numbers to my sister when she was planning a party. I knew that she didn’t have Excel, so sending her the spreadsheet wasn’t going to be too helpful. By converting the list to a PDF file, I was able to send her a document that she could easily read.

It’s quite simple: go to the Office button and choose Save As, then click PDF or XPS. Click on “Publish” and there you have it! But wait, there’s more. Before hitting the Publish button, you can set the optimization level to either Standard (this is the default setting) or Minimum. Standard is ideal for online publishing and printing. If you won’t be printing the document, choosing Minimum reduces the file size.

Just as you can specify which worksheets to print, you can specify exactly what gets published to PDF. You can publish all pages, specific pages, the active sheet, all worksheets, the highlighted selection, and metadata. In addition, you can choose to ignore print areas or not as well as make sure that the document is ISO 19005-1 compliant. To set these different options, click the Options button. This opens a dialog box that looks remarkably similar to the box that you see just before printing a spreadsheet. Make your selection, click OK, and then hit Publish.

For those who don’t have Excel 2007, publishing a spreadsheet to PDF is a bit more involved. If you simply need one document converted and don’t anticipate needed to create PDFs often, I recommend using PDFonline.com. This website offers a free conversion service. You must upload your document to the site and provide an email address. PDFonline can covert a variety of file types to PDF including XLS. Once the spreadsheet is uploaded, they convert it to PDF and email the finished document to the email address provided a few minutes later.

If you anticipate needing to convert many spreadsheets, you’d be better off buying a converter or downloading the free spreadsheet application from OpenOffice.org. This spreadsheet application, Calc, is capable of publishing PDF files.

What? You mean you want to store your Excel spreadsheets somewhere other than your My Documents or Documents folder? Blasphemy! All kidding aside, you can easily change your default folder for saving Microsoft Excel documents. For example, if you primarily use Excel for business purposes yet also use your computer for personal use, you will likely want to keep business and pleasure separate. You may have two partitions on your hard drive - one for business and one for personal use - or you might have separate folders or external drives for these exact purposes.

No matter how you have separated your own personal file system, it can be a pain to regularly specify the path for saving your spreadsheets. I’m assuming that your Excel spreadsheets are primarily for business, so I’ll approach this topic from that angle. Let’s say that you have a computer running Windows Vista with a standard C: drive and the default Documents designation as well as a copy of Microsoft Excel 2007. When you go to save an Excel spreadsheet, the default location points to Users\UserName\Documents.

This is fine and dandy for many basic users, but not for you, right? You want to save your Excel spreadsheets in a separate area. To change the default Save As folder, all you need to do is head on over to the Office Button and choose Excel Options. From there, select Save in the pane found on the left side. Under Save Workbooks, you’ll see Default File Location. Bingo! This is it folks. Simply enter the new path and any new Excel file will be directed to this new location for saving.

One thing that’s lacking is a “browse” button. I don’t know about you but I am not terribly confident about my forward slashing and back slashing skills - and I’m a computer geek! Rather than manually typing in a path, you’re better off going into Windows Explorer first and finding the exact location you prefer. Once you find it, you can copy it and then paste it into this field.

To do this, open Windows Explorer by going to the Start button, right-clicking it, and choosing Explore. Navigate to your desired new default location and then click inside the address bar. Once highlighted, click the key combination of “Control+C” (for copy). Now, you have the exact path. Once inside the Excel Options/Save Workbook area, you can copy the path by clearing the setting inside the Default File Location and then entering the key combination of “Control+V” (for paste). Finally, click OK.

Earlier versions of Excel have different steps. Do the same copying and pasting routine and then go to Tools, Options, General. From there, you will be able to specify the default Save As location by copying your desired path into the appropriate text box.

Being able to save your files to a different location gives you a great deal of flexibility and helps you to be far more organized. I’m a huge fan of folders, sub-folders, and even sub-sub-folders. The more you can categorize your files and store them according to general needs, the better. It all starts by understanding how to specify the default Save As folder and learning how to save to alternative locations.

Welcome to the world of new file formats. If you’ve recently upgraded to Microsoft Excel 2007, all of your new spreadsheets will be saved in a format other than the familiar .XLS format. Instead, the new file extension is .XLSX will be your new default format. I went through a few growing pains of my own when I switched to Office 2007. I quickly found out that I was an “early adopter” of Office 2007 and many of my colleagues could not open my Excel files.

Not only did Microsoft redesign the look of Office 2007, it changed the file format to a new standard based on XML known as the Microsoft Office Open XML format. The 2007 versions of Word, Excel, and PowerPoint have all adopted this new format. While the format has its pluses (I’ll share them in a minute), it also has its minuses - the main one being that many users simply can’t open your Office documents!

The new file formats do offer plenty of improvements including smaller file sizes (up to 75% smaller in some cases), better macro detection features, greater control over privacy, interoperability and integration of business data, and improved damaged file recovery.

For example, have you ever noticed how large your Excel files can become? Some of mine are huge! By saving using the .XLSX format versus the standard .XLS format, these same files are automatically compressed (and then de-compressed when re-opened) without requiring additional utilities such as WinZIP. Smaller file sizes mean less storage concerns and faster downloads.

Worried about macros? The .XLSX format helps to ease those concerns by prohibiting both VBA and ActiveX controls. Because of this restriction, the security risks are greatly minimized. For files created that do contain macros and ActiveX controls, the file extension .XLSM is used instead.

The Document Inspector is designed to work with this new format, making it easier to protect your personal information from appearing in the spreadsheet’s properties.

Business interoperability is a reality with the new formats as well. For organizations that require customer-defined schemas, the new format is a dream come true. Unlocking existing information and working with it in Office programs involves an Office file, ZIP utility, and an XML editor - and you’re in business!

Remember all those corrupt Excel files you used to encounter due to a bad chart or table? With the new file formats, data recovery has been improved. Now, the files store the data in a modular fashion with the different components stored separately. If a chart is corrupt, the file itself can still be opened despite the corruption.

In Excel 2007, the following new file formats apply: workbook (.XLSX), macro-enabled workbook (.XLSM), template (.XLTX), macro-enabled template (.XLTM), non-XML binary workbook (.XLSB), and macro-enabled add-in (.XLAM).

While these features are wonderful, not everyone has upgraded to Excel 2007. This means that your files may not be readable to others. Frustrating, I know. You have a couple of options: you can either save your files in the old .XLS format or ask your partners to download a converter from Microsoft. Once they have the converter installed, their Office programs should be able to handle the new Excel 2007 file extensions.

To say that spreadsheets are filled with data is an understatement. In addition to the data that you enter into the spreadsheet, Excel also maintains “metadata” about the document itself. This hidden data typically contains information such as the spreadsheet’s title, author, creation date, edit date, and other details. Excel can contain standard properties, automatically updated properties, custom properties, organizational properties, and document library properties.

I occasionally need to look at a spreadsheet’s properties for a variety of reasons. I may want to know who last saved a shared document or even the physical path of where the spreadsheet is located on the hard drive. You likely have your own reasons. Whatever the reason, looking at the document’s properties is easy.  

In versions of Excel prior to Excel 2007, all you need to do is go to the File menu and choose Properties. The Properties dialog box will appear detailing the various properties of the worksheet.

In Excel 2007, the File menu is gone. But never fear, you can easily look at the spreadsheet’s properties by going to the Office Button and clicking Prepare followed by Properties. This places a horizontal pane across the top of your worksheet listing the basic Excel document properties such as author, title, subject, keywords, category, status, and comment. You can easily edit these properties once the pane is opened by simply clicking inside the text boxes and either deleting the existing data or entering new data.

In addition to the basic properties, you can also view the advanced properties. To do this, go to the upper left corner of the Document Properties pane and click the down arrow next to the words “Document Properties.” Click Advanced Properties. This launches the Properties dialog box where you’ll see a series of tabs: General, Summary, Statistics, Content, and Custom.

Viewing a spreadsheet’s properties is useful in general. You can take it even further by fully using this feature. For example, if you are collaborating with others, you may want to take advantage of the Custom properties. The Custom tab of the Properties dialog box has a series of pre-defined fields that you can use to customize the spreadsheet’s properties such as “checked by,” “client,” “date completed,” “department,” “editor,” “project,” and “purpose.”

For example, if your spreadsheet details a particular client’s purchase history, you can select “client,” specify the type as text, and enter a value such as the client’s name. Next, you can select “purpose,” specify the type as text, and type the purpose of the spreadsheet in the value field. Now, your colleagues can easily discern the spreadsheet’s purpose and who it relates to by viewing this hidden metadata.

In addition, to being able to customize and enhance your spreadsheet by using the document properties to its fullest, you can also do the opposite. Sometimes, you don’t want your personal information attached to a document. Excel 2007 comes with a tool that removes personal data from the spreadsheet’s properties. Simply go to the Office Button, click Prepare, and click Inspect Document. Next, select Document Properties and Personal Information (if should be on by default). When the inspector finds information, a button appears saying “Remove all.” Click this button and your document properties will disappear!  

Yes. Not only can you password protect the entire spreadsheet, you can do much more including restricting how much control a user has as far as editing your worksheets.

First, let me show you how to password protect your spreadsheet in Excel 2007 and then I’ll show you a few other techniques that may come in handy.

In Excel 2007, you can password protect the spreadsheet by going to the Office Button and clicking on Prepare. Next, click on Encrypt. A dialog box opens prompting you to enter a password. Be sure to pick a password that you’ll be able to remember. Otherwise, you won’t be able to open your spreadsheet.

While password protecting a worksheet ensures that only those with permission can view the data, it doesn’t stop them from manipulating or editing the data. You can further protect your Excel spreadsheets by using the “Protect Worksheet” and “Protect Workbook” options.

These options are found in the Review tab in the Changes area of Excel 2007. Earlier versions of Excel also allow you to protect your worksheets using similar steps. You can either protect the entire workbook or individual worksheets within the workbook.

Protecting a worksheet is useful for presenting read-only data. You can then share the worksheet with others without worrying about them accidentally deleting key data or making unauthorized changes. Simply click the Protect Worksheet icon and create a password for un-protecting it later. Again, use a password that you will remember. The dialog box also lists numerous restrictions and permissions that you can adjust to allow users minimal editing capabilities.

When protecting worksheets, you can also “lock” cells. To do this, go to the Home tab and find the Cells group. Click on Format followed by Format Cells. Now, go to the Protection tab and click Locked. They aren’t locked yet, though. Locking doesn’t go into effect until you have protected the worksheet. This allows you to edit until you’re ready to protect the worksheet.

Protecting the entire workbook is also helpful. Again, you will specify a password for un-protecting it just as with the Protect Worksheet function. These passwords are purely administrative - they allow you to change the protection on and off. They do not password protect the workbook.

The Protect Workbook dialog box also prompts you about what you want to protect: structure or windows. Protecting the workbook’s structure protects the overall layout of the book in general - not the data. For example, by protecting the workbook, users will not be able to move or delete worksheets but they may be able to edit data unless you also protect the individual worksheets. Protecting the windows prevents users from resizing the workbook’s display size.

Depending on what you want to protect, you can either password protect your spreadsheets so that only those who you intend to view the data can access it or you can place editing restrictions by protecting the worksheets directly. You can also use a combination of these different protections for the ultimate in controlling and protecting your data.