Enabling Macros in Excel

Since I get a fair amount of readers downloading my financial spreadsheets and asking, "How do I get the macros to work?" I decided to write a quick tutorial on just that topic.

"Macros," where Excel is concerned, are simply sets of instructions that tell Excel to automate some process (or processes) inside the spreadsheet. In my case, I often use macros to sort and/or clear out spreadsheet data (with a single click!) and to perform a variety of similar mundane duties. Macros can be huge time- and effort-savers, but because they could conceivably be programmed to do pretty much anything to the computer they're running on — say, wipe out the hard drive, or even something worse — we have to be very careful with the Excel macros we run. You always want to verify a spreadsheet macro's source, and make sure you trust the soul who created the thing.

In any case, when you're ready to enable macros in your Excel program, you'll find that it's a snap.

Enabling Macros in Excel 2003 and Older

1. In your Excel menubar, click TOOLS —> MACRO —> SECURITY, as shown below.

This will open up a message box like the one shown below.

2. Select your macro Security Level: either "Medium" (recommended) or "Low" (not recommended).

Checking the radio button beside "Medium" (which is what I'd recommend) means Excel will prompt you for permission each time a macro tries to run. Checking the radio button beside "Low" (not something I'd encourage!) means Excel will have free reign to run any and all macros in the spreadsheets you open.

You might notice the words "Virus scanner(s) installed" in the bottom left corner of that screenshot. Having a good antivirus program running at all times is a great idea; having a good antivirus program running when you're using Excel macros is a must.

Still Not Working? There May Be an Excel Quirk at Work.

Complete the procedure above, then save and close your spreadsheet.
Close Excel completely.
Reopen Excel and your spreadsheet
Complete the procedure above once more.
Save and close Excel again.
When you open Excel the next time, your macros should be operating normally.

Yeah, it's odd, but I find that in Excel 2003, I usually have to change the security settings, save, and close Excel a couple of times before the changes take effect. It's annoying ... but that's life, right?

Enabling Macros in Excel 2007

1. In Excel, click the Office button in the upper left corner of the screen.

The "Office Button" is the fancy button in the top left corner — the one with the colorful Office logo. Click it to open the menu shown in the next step.

2. Click the "Excel Options" button in the lower right.

3. Click the "Trust Center" button on the left. Then, at the bottom right, select "Trust Center Settings" as shown below.

The image above can be enlarged by clicking.

4. In the next window, select "Macro Settings," then select the radio button for "Disable all macros with notification."

5. To close the Trust Center window, click the lower right "OK" button.

6. Save and close Excel completely.

7. Reopen Excel and your spreadsheet (one with macros, anyway).

Now there should be a SECURITY WARNING notification beneath the Office ribbon, as shown in the next step.

8. In the SECURITY WARNING banner, click the "Options" button.

9. Select the radio box beside "Enable this content," then click "OK."

After you click "OK" and the window closes, your macros should function normally. Because you selected "Disable all macros with notification" in Step 4 above, you will need to follow Steps 8 and 9 each time you open a spreadsheet that contains macros (if you want the macros to run, that is).

Alternately, in Step 4 above, you could select "Enable all macros." This would allow any and all macros to run freely at all times. But I would not suggest it! Don't do this unless you are ABSOLUTELY SURE you won't be downloading or using unsafe spreadsheets (and by extension, macros) from ANY source! Malicious macros and Active-X controls can do serious damage to your computer and/or network!

Excel 2007: Macros, Encryption, and Antivirus Issues

If Excel 2007 is blocking macros due to problems with "encryption" and your antivirus software, and you're not seeing the "Enable this content" radio button as shown above in Step 9, you likely need to download the latest Service Pack for Office 2007. For me, the fix was installing Office Suite Service Pack 2, found here:

Office 2007 Service Pack 2

Microsoft has a bit of info on this issue on its Knowledge Base Article 927150.

Alternately, you can save the offending file in Excel 2003 format (.xls), and the macros should run properly — assuming you've made the Excel 2007 changes noted above in Steps 1 through 9, of course.

Enabling Macros in Excel 2010

With Excel 2010, Microsoft has added an additional layer of protection — over and above that found in Excel 2007, which centered on simply finding the "hidden" menus where macros can be enabled.

When opening a macro-enabled spreadsheet, by default, Excel 2010 will disable macros and show its new "Protected View" ribbon at the top of your screen:

This "Protected View" feature means that enabling macros has now become a two-part process. To get things rolling, we need to "turn on," or enable, macros in Excel 2010. So follow along:

1. Open Excel. Click the FILE tab above the Office ribbon. (It's the first tab on the left.)

2. In the left-side menu, click OPTIONS.

3. In the "Excel Options" window that appears, scan the left menu and click TRUST CENTER.

4. On the right side of the screen, click the TRUST CENTER SETTINGS button.

5. Now you'll see the TRUST CENTER window. In the left menu, select MACRO SETTINGS.

6. You will now see four "Macro Settings" options. Choose "Enable all macros...."

7. Click the OK button at the bottom right of the "Trust Center" window.

8. Click the OK button at the bottom right of the "Excel Options" window.

9. Close Excel, and then reopen it. Macros should now be enabled.

You've now enabled macros in Excel 2010. However, if "Protected View" is enabled, which it WILL be by default, then macros will still NOT run.

Rather than disabling "Protected View," a feature that, actually, I'm glad Microsoft added, I would suggest that you create a "Trusted Locations" folder and save your trusted, macro-enabled spreadsheets in it, and run them from there. Doing so will allow Excel macros to run, but only in spreadsheets that you've stored in the folder (or folders) you designate as "Trusted Locations."

To create and designate a "Trusted Locations" folder:

1. On your hard drive or thumb drive, create a folder into which you'll save all your trusted, macro-enabled spreadsheets.

My "safe" folder, as an example, is called "Excel Documents" and is located at "C:\DATA\Excel Documents\" but you can name yours whatever suits you.

2. Open Excel 2010. Click the FILE tab. Click OPTIONS in the left-side menu.

3. In the "Excel Options" window that appears, click the TRUST CENTER button on the left.

4. Click the TRUST CENTER SETTINGS button on the right.

5. On the left, select TRUSTED LOCATIONS.

6. Near the bottom, click the ADD NEW LOCATION button.

7. A small window appears. Click the BROWSE button.

8. Navigate to the folder you created above. Select the folder, and click OK.

9. You should be back to the "Microsoft Office Trusted Location" window. Checkmark the "Subfolders of this location are also trusted" option. Then click the OK button.

10. You should be back to the "Trust Center" window. Click OK.

11. You should be back to the "Excel Options" window. Click OK.

12. Close Excel. Place a trusted, macro-enabled spreadsheet in your "Trusted Locations" folder. When you open it, all macros should be functional.

At this point, as an example, if you were to download a macro-enabled spreadsheet from the internet, and save it to your desktop, its macros would not run when you opened the file.

However, if you saved that same spreadsheet into a folder you designated as a "Trusted Location," the macros would then execute.

Need a Bit of Help?

If you need more help with your security settings and macro operation, just drop me a line. I can't promise to have all the answers, but I'll do my best!