Excel 2007: Protect Sensitive Data In Your Documents

By David H. Ringstrom, CPA

Your spreadsheets might contain more personal or confidential information than you realize. Such data often reside in the nooks and crannies of your spreadsheet, such as in the Properties window, or in overlooked hidden rows, columns, or worksheets. Fortunately the Document Inspector feature in Excel 2007 allows you to scan for and eradicate such information with just a few clicks of your mouse. I’ll show you how in this article, and will also discuss the Remove Hidden Data tool for Office XP and Office 2003 users.

Try it now:  A free 60-day trial of Office 2007 is available for immediate download. Note that you can instruct the installation program to leave your present version of Office intact so that you’re free to work in either version. Or, try the online test drive.

Transition tools: Office Online offers an online command reference that shows where your favorite Excel 2003 commands reside in Excel 2007. You can also download and install the command reference tool on your computer. Yet another option is to add the Get Started tab to your Excel 2007 ribbon — this puts numerous training aids right at your fingertips.

Introducing the Document Inspector

This feature — also available in Word 2007 and PowerPoint 2007 — allows you to scan your documents for information that you might not want to share with others. It’s easy to get started:

1. Open the spreadsheet that you wish to scan.
2. Click the Office button, choose Prepare, and then Inspect Document.

Protected Worksheets: You cannot initiate the Document Inspector from a protected worksheet. If the Inspect Document choice is disabled, either activate an unprotected worksheet, or click Unprotect Worksheet in the Changes section of the Review tab of the ribbon.

3. You may be prompted to save your document, as shown in Figure 1. It’s always best to run the Document Inspector on a separate copy of your document, so that the original remains intact.

Figure 1: Be sure to save a copy of your original document, as certain changes by the Document Inspector cannot be undone.

4. As shown in Figure 2, the Document Inspector allows you to scan seven categories of content. You can clear the checkboxes for any categories that you wish to omit. When you’re ready, click Inspect.

Figure 2: You can choose which areas of your spreadsheet to inspect.

5. After the inspection you’ll see results for each category, as shown in Figure 3. At this point you have a couple of options:

Figure 3: The Document Inspector provides summary level inspection results.

·         Click the Remove All button for any category. The Document Inspector only alerts you to the presence of personal or possibly confidential data, so you may wish to close the Document Inspector and determine what will be removed.

·         Click the Reinspect button to repeat the document inspection, perhaps after using the Remove All button(s) to eliminate personal or confidential data.

·         Click the Close button to dismiss the Document Inspector window.

Understand the Categories

The Excel 2007 Document Inspector searches for the following types of information:

• Comments and annotations – You’ll usually know that a spreadsheet contains comments — such cells have a little red arrow in the upper-right-hand corner. However, it’s possible to obscure comments by hiding the indicator:

1.      Click the Office button, and then choose Excel Options.

2.      Choose Advanced, and then scroll down to the Display section.

3.      Choose No Comments for the “For Cells with Comments, Show” option.

Change Annotations: The Document Inspector also includes comments automatically added by the Track Changes feature within Excel.

• Document properties and personal information: Certain authorship properties are set automatically when you create new documents:

1.      Click the Office button, and then choose Prepare, and then Properties.

2.      As shown in Figure 4, your name may appear in the Author field. It’s easy enough to clear this field and think that your work is done — but it isn’t.

Figure 4: The initial Properties window shows your name, but not other potential personal data.

3.      Click the Document Properties button —just above the Author field — and choose Advanced Properties.

4.      Click the Summary tab, and erase your company name if it appears within this window, as shown in Figure 5.

Figure 5: The Properties dialog box provides access to all document properties fields.

• Custom XML data: Advanced users can store additional document properties in custom XML data that is not readily visible, but can be retrieved by others.
• Headers and footers: Headers and footers within a worksheet may contain seemingly benign information, such as your name or your company name.
• Hidden rows and columns: The Document Inspector alerts you to the presence of hidden rows and columns. You can click the Remove All button to delete the hidden rows or columns, but doing so may compromise the integrity of your spreadsheet. It’s typically best to remove hidden rows or columns by hand.
• Hidden worksheets: You can hide worksheets within your workbook by choosing the Format command within the Cells section of the Home tab. Under Format choose Hide & Unhide, and then either Hide Sheet or Unhide Sheet. As with hidden rows or columns, it’s usually best to remove these manually, although the Document Inspector can do so with just the click of a mouse.
• Invisible content: Any object that floats above the worksheet, such as charts, shapes, clip art, macro buttons, etc. can be hidden. Even if you’re the person that hides the objects, you might later forget about the hidden content. To demonstrate, I’ll show you how to add an object, and then how to toggle its visibility property:

1.      Choose Shapes in the Illustrations section of the Insert tab within the ribbon.

2.      Choose the first rectangle shape, and then draw a rectangle on your worksheet — simply hold down your left mouse button, draw the rectangle, and then release your mouse.

3.      Once you add the rectangle, the Drawing Tools Format tab should appear automatically in the ribbon. Choose Selection Pane from the Arrange tab.

Alternative: You also access the Selection Pane by choosing Find & Select within the Editing section of the Home tab. Choose Selection Pane from the resulting drop-down menu.

4.      The Selection and Visibility pane shows you a list of all shapes on the current worksheet. You can use the Show All and Hide All buttons at the bottom of the pane to control visibility of all shapes at once, or click the box that appears to the right of each object. For instance, in Figure 6 Rectangle 5 is hidden, so the box is empty. However, Chart 2 is visible, so the box contains an eye. This allows you to hide or unhide objects as you wish.

Figure 6: The Selection & Visibility pane allows you to hide or unhide objects.

Handle With Care

Although it’s a helpful tool, keep in mind that the Document Inspector only alerts you to the presence of personal or sensitive data. In some cases you could damage your spreadsheet by allowing the Document Inspector to remove columns, rows, or worksheets. Further, the Document Inspector can’t notify you of every possible data breach — only a careful review by a human can offer that assurance. For instance, the Document Inspector won’t notify you of these instances:

• One object, such as a chart or text box, obscured by another object floating above it.
• Cells formatted as hidden.
• Cells with a font color set the same as the worksheet background. For instance, users sometimes set the font color of certain cells to white to obscure certain areas of a spreadsheet.

Customizable: You can create custom modules that allow the Document Inspector to identify other types of sensitive data within your spreadsheets.

Protect Office XP/2003 Documents

Office XP and Office 2003 users can install the free Remove Hidden Data tool to remove personal data from their documents. The tool isn’t as robust as the Document Inspector — you can’t set any options — but it does provide a log file of the types of data that was removed. Once you download and install the tool, a new Remove Hidden Data command will appear on the File menu in Excel, Word, and PowerPoint.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.