It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,...
Since it was first introduced in 1985, the Microsoft Excel spreadsheet program has been one of the most popular solutions for creating simple spreadsheets as well as displaying data as graphics on charts. The program also allows you to calculate commercial, statistical and other complex mathematical formulas and functions.
A big strength of Excel is its clean, feature-rich user menu with useful functions such as sorting, grouping and filtering as well as various options for importing and exporting data. As of version 5.0, you can use macros to automate processes and tasks in Excel. We’ll show you how to enable this feature in different versions of the spreadsheet program.
How do you enable macros in Excel?
Excel macros are a great way to simplify your work with the spreadsheet program. However, macros are also known for their vulnerabilities. Since they are nothing more than small subroutines, they may contain malicious code that can paralyse your operating system in the worst case scenario. For this reason, macros are not enabled by default in Excel or are only partially enabled. Very often, all macros are disabled or all macros without a digital signature are disabled, which is why the corresponding scripts cannot be executed after import or programming.
The following instructions show you how to change the settings in the various versions of the Microsoft application and how to enable the type of Excel macro you want to use.
For detailed information on the functionality and advantages of Excel macros, including a brief introduction to macro programming or recording, see our basic guide to Excel macros.
How to enable macros in Excel 2019, 2016 and 2013
If you want to enable macros in Excel 2013, 2016 and 2019, go to the Trust Center. This options menu contains a number of settings that ensure the security of your device and data protection compliance when using Excel. For example, you can define trusted locations or make settings for general access protection as well as Protected View settings for Excel folders. This security menu also contains macro settings for enabling or disabling macros in Excel.
Start by opening the “File” tab in Excel. By default, the program opens the “Info” menu. Go to the options by clicking the “Options” button in the side menu on the left:
The Microsoft application opens the Excel Options dialogue window, where you can choose from variety of options, including the Trust Center. Click Trust Center and then click the “Trust Center Settings” button.
In the Trust Center dialogue window that appears, select the “Macro Settings” menu. In this area, you can enable macros in Excel by choosing one of the following three options:
- Disable all macros with notification: This default setting means that all macros are automatically disabled in Excel. However, if you select this option, you will receive a notification if a macro is detected or will be run. You then have the option of giving special permission to enable it.
- Disable all macros except digitally signed macros: This setting differs only slightly from the preceding one. The difference is that macros with signatures from trusted publishers (which can also be defined in the Trust Center) run without an alert.
- Enable all macros: The last option enables all macros, which is why Microsoft alerts you to the security risk associated with this setting. If you want to use macros from third parties or publishers, it is entirely your responsibility to assess their safety.
Some Excel macros can only be used if you have also enabled the “Trust access to the VBA project object model” option. To do this, select the corresponding checkbox.
Once you have made your selection, save the macro settings by clicking “OK.” The Trust Center and Excel options will close.
How to enable macros in Excel 2010
Macros are also disabled by default in Excel 2010 for security reasons. Macros are enabled in the same way as in newer versions of Excel. However, the Trust Center is called the Security Center. The procedure is therefore as follows:
- Open the “File” tab.
- Click “Options” in the side menu.
- Select the “Security Center” menu item and then click the “Security Center Settings” button.
- Select the option you want to use for enabling macros in Excel (e.g., “Enable all macros”).
- Click “OK” twice to save the changes.
Enabling macros in Excel 2007: Step-by-step guide
Microsoft is not always consistent with menu naming, as shown by Excel 2007: There’s no “Security Center” in this version. Security and privacy settings are made in the Trust Center. Apart from several different buttons with different names, the procedure for enabling macros in Excel 2007 is basically the same as the procedure in Excel 2010 and newer versions:
- Click the Microsoft Office icon (top left).
- Click “Excel Options.”
- Select the “Trust Center” menu item and then click the “Trust Center Settings” button.
- Go to the “Macro Settings” area and enable the option you want to use for Excel macros.
- Close the Trust Center and Excel options by clicking the “OK” button for each option.