Problem: A reader posed the following question: “I regularly get files from other people in my company. They arrive via Outlook or are download them from an Internet site. I worry that people aren’t smart enough to avoid getting vi­ruses or could maliciously slip something bad into the Workbook_Open macro to cause problems with my computer.

In Excel 2003, if you opened a file with a macro, it asked you to choose whether to enable or disable macros. How the heck should I know whether I should enable the macros when I haven’t even had a chance to look around the worksheet, or examine the macro code? What do I do?”

Solution: When you answered Enable Macros in Excel 2003, you were really taking a risk. Now is the time to use Protected Mode. Any file that comes from a potentially danger­ous location is open in the new Protected mode in Excel 2010. Here is the cool thing about Pro­tected mode: You can look at the workbook. You can scroll through it or go to other worksheets. You can look at the macros. When you are con­vinced that the file is safe, you click a button and the workbook is available in regular mode.

This is brilliant. You get to actually look at the workbook, and while doing so it cannot harm your computer. You get to make an educated de­cision as to whether the workbook may prove harmful. And, you know what? A lot of the time, you won’t even have to leave Protected mode. You can look at the worksheet, see what you need to see, and close the workbook.

Which Workbooks Open in Protected Mode?

Basically, any file that did not originate on your computer can open in protected mode. Specif­ically, these workbooks can be opened in Pro­tected mode:

  • Files that you download from the Internet
  • Files in your temporary Internet folder
  • Files that you open from Outlook
  • Files that fail validation

If you want to adjust those settings, click the words in the information bar in Figure 1, and then choose Protected View Settings.

Fig. 1

avoidmistakesFIGA1

You can turn off Protected mode for one or more of the situations shown in Figure 2.

Fig. 2

avoidmistakesFIG1

New Rules for Workbooks with Macros and Links

If you frequently use workbooks that have mac­ros or external links, you will like the following improvements.

Normally, if you open a workbook that has macros, you must click the Enable button to use the macros (Figure 3).

Fig. 3

aovidmistakesFIG2

In Excel 2010, when you decide to enable macros for a workbook stored on a local drive, that workbook becomes a trusted document. When you open that same file again, you will not be asked to enable the macros.

This should allow you to keep the setting for Disable All Macros with Notification as shown in Figure 4. In Excel 2003, this setting was known as Medium Macro Security. It forces you to choose to enable macros once for each file stored on a local hard drive.

Fig. 4

avoidmistakesFIG3

In a similar fashion, files with links to ex­ternal workbooks have a new behavior starting with Excel 2007. Instead of being forced to an­swer the question of whether you want to up­date external links, the external links are auto­matically disabled until someone clicks Enable Content (Figure 5).

Fig. 5

aovidmistakesFIG4

In Excel 2010, when you choose to Enable Content the first time for a file stored on a local hard drive, you will no longer be asked to enable content. You will instead be taken directly to a dialog like the Excel 2003 dialog where you get to choose to enable links or not (Figure 6).

Fig. 6

avoidmistakesFIG5

You can control the startup prompt for one specific workbook by choosing Data, Edit Links to display the Edit Links dialog. In the lower-left corner, choose Startup Prompt. The Start­up Prompt dialog shows the choices (Figure 7).

Fig. 7

avoidmistakesFIG6

Untrusting a Document

If you decide to enable content on one work­book, and then discover that the workbook is doing something detrimental, you don’t want those macros or links to automatically work the next time you open the workbook. You can’t change the trusted status for one workbook, but you can reset the trusted status for all work­books. Choose File, Options, Trust Center, Trust Center Settings, Trusted Documents, Clear (Figure 8).

Fig. 8

avoidmistakesFIG7

Using Trusted Locations

If you want to trust your own workbooks but not trust the workbooks created by others, you can start saving safe work­books in one specific fold­er and set up that folder as a trusted location. In the Trust Center, use the Trusted Locations item in the left navigation to set up trust­ed locations.

Beware of Group Policy Settings Set by Your IT Department

If your employer licenses Of­fice 2010, the people in IT are allowed to set up certain defaults in Group Policy. These defaults can be a bit dangerous in the hands of someone who doesn’t know Excel.

Overzealous IT people might choose to never show you the message that external links have been blocked. This allows the completely insane situation where you think that you have correct calcula­tions in the workbook but you are not getting the correct numbers from external links (Figure 9). If your IT people want the company to restate numbers to the Securities and Exchange Commission, this is a fine choice. Otherwise, they should not force this set­ting through Group Policy.

Fig. 9

avoidmistakesFIG8

 

The IT department can force this insane option on every­one in the company. Another setting that you can control through Group Policy is to force certain file extensions to always open in Protected mode (Figure 10).

Fig. 10

avoidmistakesFIG9

To check this setting, use the File Block Settings tab in the Trust Center. You or the IT person who is responsible for setting up Group Policy can control which file types open in Pro­tected mode.

CFO contributor Bill Jelen is an Excel MVP and the author of 35 books about Microsoft Excel. He is also editor-at-large of the CFO Excel Pro newsletter, where this article first appeared.

, , , , , ,

2 responses to “Excel Tip: Avoid Other People’s Mistakes”

  1. I have excel data with formula where to tind out the data over there, control F will not work abot which we all are known.Is it possible to get the ideas how can we find same as control F in the field where formulas are implemented. Please waiting anyone’s swift response.

Leave a Reply

Your email address will not be published. Required fields are marked *