Enabling macros in Excel can really speed things up, automating all those repetitive tasks. But, Excel disables them by default because macros can be a security risk. Here’s a breakdown of how to enable them safely for different scenarios - be it for a single file, during your current session, for all workbooks, or from trusted locations.
Enable macros in all workbooks
If you’re constantly using macros and want them enabled for all Excel workbooks by default, you can adjust your Trust Center settings. Just be aware that this does open you up to potential security risks from malicious macros.
- Click the File tab and select Options from the left sidebar.
- In the Excel Options dialog box, click Trust Center in the left pane, then click the Trust Center Settings button on the right.
- In the Trust Center dialog box, select Macro Settings from the left sidebar. You’ll see four options on the right for handling macros.
These are the available macro settings:
- Disable all macros without notification: This option will block all macros without providing any alerts.
- Disable all macros with notification: The default setting; macros are disabled, but you’ll see a notification with the option to enable them.
- Disable all macros except digitally signed macros: This disables macros, except those that have a trusted digital signature.
- Enable all macros: Allows all macros to run without any warnings, which can pose a security risk.
- To enable all macros by default, select Enable all macros. Then, click OK to save the changes.
With this setting, all macros in any workbook will run automatically, without prompts. If you want to revert this change, simply go back to the macro settings and select a different option.
Enable macros in trusted locations
Another good way to manage macros is by designating certain folders as trusted locations. Any files in these folders will have their macros enabled without any security warnings. It’s a good balance between convenience and security, and better than allowing macros everywhere.
- Click the File tab and select Options.
- In the Excel Options dialog box, choose Trust Center from the left pane, and then click Trust Center Settings.
- Select Trusted Locations in the Trust Center. You’ll see any existing trusted locations listed here.
- To add a new trusted location, click on Add new location.
- In the Microsoft Office Trusted Location dialog box, click Browse and select the folder you want to trust. If you want Excel to also trust all subfolders, check the box labeled Subfolders of this location are also trusted. You can also add a description for the location. Then, click OK to confirm.
- The new trusted location will now appear in the list. Click OK to close the Trust Center.
Now, any macro-enabled files saved in these trusted locations will run macros without security warnings. Be careful when adding network locations, as this might increase the risk of malicious macros running.
Enable macros on individual files
You can enable macros for specific Excel documents either through the message bar or the backstage view.
Enable macros from the message bar
When you open an Excel file with macros, you’ll see a yellow message bar below the ribbon telling you that macros have been disabled.
- In the yellow message bar that appears below the ribbon, click the Enable Content button to activate the macros in the current document.
Enable macros in the backstage view
If you don’t see the message bar, you can also enable macros through the backstage view.
- Click the File tab to access the backstage view.
- In the Info section, you’ll see a yellow Security Warning that says macros have been disabled.
- Click Enable Content and then choose Enable All Content to allow the macros to run in the document.
After you enable the macros this way, Excel will consider the document as trusted, and you won’t get the security warnings again.
Enable macros for one session
If you only need macros enabled temporarily, without making the file trusted, you can enable them for just your current session.
- Click the File tab to open the backstage view.
- In the Info section, find the yellow Security Warning, click Enable Content, and select Advanced Options.
- In the Microsoft Office Security Options dialog box, select Enable content for this session and click OK.
Macros will be enabled for the current session. When you close and reopen the file, macros will be disabled again unless you repeat these steps.
Remove trusted documents
If you’ve previously trusted documents but now want to disable macros, you can remove them from your trusted documents list.
- Click the File tab and select Options.
- In the Excel Options dialog box, choose Trust Center and click on Trust Center Settings.
- Select Trusted Documents from the left sidebar.
- Click the Clear button to remove all trusted documents.
- Confirm the action by clicking Yes in the pop-up dialog.
Now, Excel will not automatically allow macros, ActiveX controls, or other active content in those previously trusted files. Always make sure you trust the source before enabling any macros.