How to enable macros in excel

MS Excel, also known as Microsoft Excel or Excel, is essential spreadsheet software widely used worldwide. This spreadsheet software is so powerful that we can record extremely large data sets in different cells across multiple desired worksheets. In addition, we can perform various mathematical calculations and analytical operations using existing functions and formulas. In Excel, it is very common that we may need to perform the same operation or set of actions multiple times. However, Excel enables users to perform repetitive tasks by automating them to save significant time. This is where an existing feature called Macros comes into play.

In the old days, Excel Macros were very common and could run automatically in Excel. However, this raises the possibility of running into errors in terms of security concerns. This forced Microsoft to implement safety and security precautions by disabling the execution of Macros by default. Although we can still use Macros easily in Excel, we must first allow or enable the Macros feature.

In this tutorial, we discuss the various step-by-step methods or solutions on how to enable Macros in Excel. Before discussing the methods, let us first take a brief introduction to Excel Macros.

What are Macros in Excel?

Excel offers various features, and one essential feature of it is Macros. Macros are mainly small programs stored within Excel documents that help us automate specific operations by executing them in Excel. Excel allows us to record a Macro and save a set of actions accordingly. Whenever we need to perform the same actions again, we can execute the recorded Macro, and the associated operation is performed automatically. That way, we can quickly perform repetitive activities while minimizing the time spent on the core operation or other meaningful tasks.

Although we usually record a Macro using mouse clicks and GUI, the actions are converted into VBA codes by Excel behind the scenes.

How to enable macros in Excel?

An essential advantage of the Excel program is that it provides many ways to perform any specific task, be it a simple task or a complex operation. In the same way, we get different ways to enable macros in Excel. However, each specific method has its benefits, and it helps to enable macros for certain use cases. The following are some common ways to enable macros in Excel:

Enabling Macros using Security Notice

The fastest way to enable any macros for the specific workbook is to use the Security Warning in that particular workbook’s window. If our Excel program has the default configuration for the macros, we usually see a warning message at the top of the worksheet under the ribbon. This yellow security warning box displays a message, “Macros have been disabled”. It means that Excel has blocked any macros from executing in that workbook.

Excel also shows us the ‘Enable Content’ button apart from the warning message, which can be used to allow macros for that individual file or workbook. However, it is recommended to use this button only if we trust the source of the corresponding Excel file.

Suppose there is any Excel window with a VBA editor running on it. In that case, we will see the Microsoft Excel Security Notice window after we open any Excel file with macros. In that case, we usually do not see the above yellow warning bar. If we trust the file or the source it came from, we can enable macros of that particular file by clicking on the ‘Enable Macros’ button in the Microsoft Excel Security Notice window.

The Microsoft Excel Security Notice window looks like this:

Enabling Macros using the Backstage view

Another easy method to enable the macros in Excel is to use Office Backstage view. To access the options present in the Backstage view, we must go to the File tab and select the Info from the list. After that, we must click on the drop-down arrow associated with the ‘Enable Content’ button. This will display two additional options, such as:

Option 1: Enable All Content

The first option, Enable All Content, is used when we want to make any specific Excel file trusted, allowing its active contents like macros. This method works only for that particular Excel file. However, all other Excel files with macros will not be allowed. We will get the Warning message on other similar files.

When we click the ‘Enable All Content’ option, it works similarly to the previous method where we had to click on the ‘Enable Content’ button via the Security Notice’. Once we enable the file’s contents, it is treated as a trusted document. Therefore, if we open the same file again, we will not get any warning again in the future for that particular file with macros.

Option 2: Advanced Options

The second option, Advanced Options, is used mainly for enabling the macros of the particular file only for the session. This option does not make the associated file a trusted document. In some cases, enabling the macros for only a single session makes sense. For instance, suppose we have an Excel file with macros (or VBA), and we only wish to investigate it without making it a trusted document. So, we can use option 2 (Advanced Options) and enable the file’s content for the duration until the respective file is kept open in Excel.

After we click on the ‘Advanced Options’ button, we will see another window named Microsoft Office Security Options. Here, we must select the circle radio button associated with the ‘Enable content for this session’ option to enable macros for single-use and click on the OK button. If we close the file and reopen it, the warning will reappear, and macros will be disabled.

Enabling Excel Macros using the Trust Center

The Trust Center enables us to decide how the macros should work for all future files. Once we adjust the settings from the Trust Center, the corresponding settings are applied globally. That means the applied settings become the new default configuration and apply automatically whenever a new Excel file with macros is opened.

We must perform the following steps to go to the Trust Center and set the desired settings for macros accordingly:

• First, we need to open an empty Excel file. After that, we must navigate the File tab on the ribbon and select the ‘Options’ from the left side list in the Backstage view.
  
  Alternatively, we can go to Excel Options by pressing the keyboard shortcut ‘Alt + T + O’ or ‘Alt + F + T’.
• Next, we must select the ‘Trust Center’ option in the left pane of the Excel Options window and click on the ‘Trust Center Settings’ button. This will launch another window named ‘Trust Center’.
  
• We must navigate the ‘Macro Settings’ from the left side list in the Trust Center window. This will show us four options under the Macro Settings section. We can now choose one out of these four individual options to enable or disable macros accordingly.
  Since we want to enable macros in Excel, we need to select the last option, ‘Enable all macros’. However, it is not recommended by Excel due to security reasons. Instead, we can try out other options.
• After selecting the circle radio button for the desired option, we must click the OK button to apply the settings in our Excel program. The applied settings will be followed for all the future Excel files with the macros.

Note: The fastest way to access the Trust Center is to click on the ‘Macro Security’ button under the Developer tab. However, the Developer tab must be enabled to use this method because the corresponding tab is disabled in Excel by default.

Let us briefly understand each option under Macro Settings of the Trust Center to decide better whether macros should be enabled or disabled automatically.

Option 1: Disable all macros without notification

When we select the ‘Disable all macros without notification’ option in the Trust Center window, it usually disables all the macros content in an Excel document and does not notify us regarding this behavior. This option typically disables macros silently. It is useful for people who never intend to run macros.

However, we must note that this option does not typically disable all the macros. The documents stored or opened from the trusted locations are not bound under this option. All files and their macros from the trusted locations will still run without going through the checks of the Trust Center System.

Option 2: Disable all macros with notification

This is the default setting and also a recommended option in Excel. The only difference between this default option and the last option is that this option disables the macros from the untrusted locations but notifies us about it.

With this option selected, if we open any Excel file with macros, Excel usually displays a Security Warning notification within the sheet under the ribbon. Also, an additional button to ‘Enable Content’ appears there.

Option 3: Disable all macros except digitally signed macros

This option also disables all macros from untrusted locations like the previous options. However, one exception is that macros digitally signed by a trusted publisher will be allowed. Besides, if we have an Excel file with macros received from a publisher that is not a trusted one, we have to either enable macros for that particular file or add the publisher to our list of trusted publishers.

This option is mostly used by publishers that distribute Excel add-ins. Apart from this, this option is rarely used. All unsigned macros are disabled with this option selected, and Excel displays no notification.

Option 4: Enable all macros (not recommended; potentially dangerous code can run)

As the name suggests, this option allows or enables all macros irrespective of the location and publisher. However, this option is never recommended because it leaves our device vulnerable to macro viruses.

Specific Case: Enabling macros in a trusted location

It is never recommended to enable macros globally for all the Excel files from the Trust Center. Instead of tampering with the default macros settings, we can configure Excel to trust any specific location on our computer or local network to enable macros automatically. If we place the macros in that particular location, Excel will trust that file and execute the macros without further warning or notification. However, all the other Excel files with macros will be treated based on the default or global settings recommended by Excel.

We can perform the below steps to add any specific folder location as a trusted location and enable all the macros permanently in that location:

• First, we need to go to the File tab and select the Options from the list.
  
• Next, we must navigate the ‘Trust Center’ option from the left side list and click on the ‘Trust Center Settings’ button.
  
• In the ‘Trust Center’ window, we must navigate the ‘Trusted Locations’ option from the left-hand pane. Excel will display a list of several locations on our local storage or network that are added by default. Excel mainly uses these existing locations to save add-ins, templates, macros, etc. The existing locations must not be tampered with for the proper working of Excel.
  
  Although we can use any listed location to save our workbooks with macros, it is not recommended. Instead, we can add/ create a custom location.
• In the ‘Trust Center’ window, we must click on the ‘Add new location’ button to set up a custom trusted location.
  
• In the next window, we must enter the path or location of our desired folder. Also, we can click on the ‘Browse’ button to select a location using File Explorer. To add the subfolder of the selected location/ folder as trusted, we can select the checkbox associated with the ‘Subfolders of this location are also trusted’. Next, we can enter any brief message in the description box if desired. This can help us manage multiple custom locations.
  
• Lastly, we must click the OK button. We can now save any Excel file with macros into our custom location, and Excel will treat it as a trusted document irrespective of the settings made in the Trust Center.

Important Points to Remember

• It is not recommended to enable macros permanently as they can be harmful if they are not from trusted and authentic sources.
• We can identify Excel documents with macros by checking their extension. Generally, the files containing macros (or VBA codes) are saved as a ‘macro-enabled workbook’ or in the XLSM extension.
• It is essential to note that there is no option to programmatically enable or disable macros in Excel using VBA (Visual Basic for Applications).
• Excel disables all the macros by default but notifies users regarding it every time we open any file with the macros. However, we can silently disable all macros using the first option, ‘Disable all macros without notification’, from the Macro Settings under Trust Center. This will automatically block macros in Excel files without notifying us.