By default, Excel disables all macros to protect you from malicious viruses. But you can enable macros to automate repetitive tasks in Excel.
A macro is a sequence of Excel commands and instructions which allows you to automate complex and repetitive tasks. Instead of manually carrying out a series of time-wasting tasks yourself, you can record the tasks as macros to execute them automatically.
However, they are lots of risk from the security point of view when you run macro-enabled Excel files. Some of the macro-enabled workbooks may carry malicious macro viruses that can damage or completely delete files on your drive, compromise your data, and even corrupt your entire computer.
By default, Excel disables all macros to prevent you from opening a potentially infected file. This post will show you how to enable macros in Microsoft Excel to use them to enhance your workflow. You can enable macros on a file-by-file basis, or for all workbooks, or in a trusted location.
How to Enable Macros on Individual Files
You can enable macros for certain individual files from the message bar or in the Excel backstage view.
Enabling Macros from the Message Bar
When you open an Excel document that contains a macro and you’ll see a yellow message bar right below the Excel Ribbon (see below screenshot). It will say that Macros have been disabled in this Excel document. Click on the ‘Enable Content’ button to enable the macros.
Enabling Macros in the Backstage View
Another way you can enable macros is through the backstage view. Open the ‘File’ tab in Microsoft Excel. In the ‘Info’ tab, you will see a yellow ‘Security Warning’.
Click on the ‘Enable Content’ icon and choose ‘Enable All Content’ to enable all macros in this file.
In both methods, once you enable macros, Excel makes that document a trusted document which means when you open that document in the future it won’t ask you to enable macros again.
How to Enable Macros for One Session
Sometimes you only want to enable macros for a single time on a certain file and you don’t want to make that file a trusted document. In such cases, Excel allows you to enable macros for only one session of a file.
To do that, click on the ‘File’ tab in Excel and open the ‘Info’ tab in the backstage view. In the Security Warning area, click ‘Enable Content’ and select the ‘Advanced Options’.
In the ‘Microsoft Office Security Options’ dialog box, select ‘Enable Content for this session’ and click ‘OK’.
Now, macros are enabled for the current session only. When you reopen the document, excel will ask you to enable macros again.
How to Enable Macros in All Workbooks
Excel has a Trust center where you can enable or disable all macros in workbooks by default. Excel Trust Center contains security and privacy settings that can help keep your computer and documents safe.
To enable all macros, go to the ‘File’ tab in Excel and click ‘Options’ on the left-hand sidebar of the backstage view.
The Excel Options dialog box will open. Click ‘Trust Center’ on the left-hand sidebar and click on the ‘Trust Center Settings’ button on the right.
In the Trust Center dialog box, select ‘Macro Settings’ on the left sidebar, and on the right side, you can see the four macro settings.
The Four Macro Settings:
- Disable all macros without notification: This option disables macros entirely, blocking them without confirmation. When this setting is selected, you will not see any notifications.
- Disable all macros with notification: This is the default option that blocks macros but displays a notification. This allows you to enable macros on a file-by-file basis. Don’t click ‘Enable Content’ if you don’t trust the source.
- Disable all macros except digitally signed macros: This option blocks all macros except digitally signed macros. Excel will still ask you for a confirmation for most macros, but trusted macros will run without confirmation.
- Enable all macros: When this setting is selected, you can run all the macros without confirmation. Also, this option will make your system vulnerable to potential malware and viruses. But with this setting, you don’t have to enable macros every time.
Now to enable all macros without confirmation, choose the fourth option, ‘Enable all macros’ under the Macro Settings. Then, click ‘OK’ to save the settings.
If you wish to disable all macros, choose the appropriate setting in the above options.
Enable Macros in the Trusted Location
You can set up Excel to trust specific locations on your system or on the local network where you can store macro-enabled files. If you open any file in the trusted location, Excel will run its macro automatically without confirmation, even if the ‘Disable all macros without notification’ setting is selected in the Trust Center.
To add a trusted location, Go to the ‘File’ tab and click ‘Option’ in the backstage view.
Click ‘Trust Center’ on the left sidebar and open ‘Trust Center Settings’ on the right side of the dialog box.
In the Trust Center dialog box, click on the ‘Trusted Location’ at the top-left corner, and on the right side, you can see all your ‘Trusted Locations’ listed inside a box.
If you’re are adding a location from your network, check the box next ‘Allow Trusted Location on my network’. If you’re are adding a location from your drive, leave it unchecked. Then, click the ‘Add new location’ button.
Next, click the ‘Browse’ button on the Trusted Location dialog box and select your location from your drive or your network. Remember, it is not recommended to choose a drive from a network because malicious macros may easily spread through them.
If you want any subfolder of the selected location to be trusted too, check the ‘Subfolders of this location are also trusted’ checkbox. If you want, you can also add a description of the trusted location in the ‘Description:’ box. Then, click ‘OK’.
Once that is done, you can see your new location is added to the list of paths.
Now, You can save your macro-enabled Excel files in any of the listed trusted locations and run its macros without any security warnings.
How to Remove Trusted Documents
If you decided not to run any macro in your Excel files because of the security issue and chose the first macro setting (Disable all macros without notification), you will find that you can still run macros in certain files.
It’s because Excel will remember your previous operations. If you enabled macros on some files by clicking ‘Enable Content’ when they opened while the default setting (Disable all macros with notification) is selected, Excel will remember those operations.
Once you click the ‘Enable Content’ button on a macro-enabled document, Excel will add that file to its trusted documents. And even after you change your macro setting to ‘Disable all macros without notification’, that file is still a trusted document, hence, you can still run macros in that file.
In order to completely disable all macros, you need to remove those trust documents. To do that, go to File → Options → Trust Center → Trust Center Settings.
In the Trust Center window, click on the ‘Trusted Documents’ options. Click the ‘Clear’ button to clear all the Trusted Documents.
Then, click ‘Yes’ in the pop-up widow to confirm.
Now, all the trusted documents are cleared. Remember, when you clear the Trusted Documents, not only they won’t run any macros but also ActiveX controls and other types of active content in those documents.