Outlines step-by-step instructions for how to enable macros in Excel 2019, 2016 and 2013, and also Excel 2007 and 2010.
Find more business guides, tips and advice
Your colleague has promised you a great Microsoft Excel spreadsheet. During the first look at the spreadsheet, however, you notice that the promised functions don’t work. This is probably because the macros associated with the keyboard shortcuts are not active and you need to know the steps below for how to enable Excel macros.
Overview
Since it was first published in 1985, the spreadsheet program Microsoft Excel has been one of the most popular solutions for creating simple tables – including graphical representation of the data in diagrams. In addition, the program enables the calculation of commercial, statistical and other complex mathematical formulas and functions.
One of Excel’s great strengths is the tidy, functional user menu, which, among other things, offers useful sorting, grouping and filter functions as well as various import and export options for data. Since version 5.0, processes and actions in Excel can also be automated using macros . We’ll show you how to enable macros in Excel in the different versions below.
You can use macros to automate many tasks in Excel. Whether you want to delete certain rows or columns, mark special values or add additional functionalities, Excel offers you the right macro for every purpose.
Security risks with using macros
Excel macros are a great way to make working with the spreadsheet program easier. However, they are also known for their security gaps, because since they are nothing more than small subroutines, they can basically also contain malicious code that, in the worst case, paralyzes the executing system.
The harmful effect is not limited to Excel. Because Excel has an open interface to the operating system, the malicious code macro can completely destabilize the computer and even infect other computers via network connections. A single Excel macro with malicious code can paralyze an entire company in this way.
For this reason, macros are not activated by default in Excel or are only partially activated. Often all macros are generally deactivated or all macros without a digital signature are deactivated, which is why the corresponding scripts cannot be executed after importing or programming.
The instructions below will show you how to change the settings in the different versions of the Microsoft application and how to enable the type of Excel macros you want.
How to enable macros in Excel 2019, 2016 and 2013
If you want to activate macros in Excel 2013 and the successor versions 2016 and 2019, go to the so-called Trust Center . This option menu contains various settings that ensure the security of your device and compliant data protection when using Excel . There you can, for example, define trustworthy storage locations or make settings for general access protection and for the “protected view” of Excel folders. This security menu also contains the macro settings that can be used to activate or deactivate Excel macros.
Step 1
The first step is to go to the “ File ” tab in Excel . By default, the program first opens the “Information” menu. Use the side menu on the left to switch to ” Options ” by clicking that button.
Step 2
The Microsoft application opens the “ Excel Options ” dialog box , in which various categories are available for selection – including the Trust Center. Click on the entry and then on the button ” Settings for the Trust Center “.
Step 3
In the Trust Center dialog window that pops up, select the “ Macro Settings” target menu . There you can activate Excel macros by selecting one of the following three options:
- Deactivate all macros with notification : This standard setting basically means that macros are not activated in Excel. If you select this option, however, you will receive a notification when a macro is discovered or should be executed, and you can then activate it with special authorization.
- Disable all macros except digitally signed macros : This setting differs only slightly from the previous one. The difference is that macros with signatures from trustworthy publishers (which can also be defined in the Trust Center) can be executed without being asked.
- Activate all macros : With the last option you activate all Excel macros, which is why Microsoft also draws your attention to the associated security risk. If you also want to use macros from third parties or publishers, it is entirely your responsibility to assess the security in this case.
Once you have made your selection, confirm the changes to the macro settings by clicking on ” OK “. Trust Center and Excel Options close.
How to activate macros in Excel 2010
Macros are also deactivated by default in Excel 2010 for security reasons. The activation works in the same way as with the newer versions of the spreadsheet program – however, the Trust Center can still be found under the old name ” Security Center “. The procedure therefore looks like this:
- Go to the “ File ” tab .
- In the left side menu, click Options .
- Select the menu item “Security Center” and then press the “ Settings for the Security Center ” button .
- Select the desired option for activating the Excel macros (eg “Activate all macros”).
- Click OK twice to apply the changes.
How to activate macros in Excel 2007: step-by-step instructions
Excel 2007 also shows that Microsoft does not necessarily rely on constancy when naming the menus: In this version there is neither a trust center nor a security center. You can make the settings for security and data protection here in the trust center . Apart from the fact that some other buttons can also be found under different names, the process for activating macros in Excel 2007 hardly differs from the process in Excel 2010 and newer editions:
- Click on the Microsoft Office icon (top left).
- Press “ Excel Options ”.
- Select the menu item “Trust Center” and then click on “ Settings for the Trust Center ”.
- Go to the “Settings for macros” section and activate the desired option for Excel macros.
- Close the Center and the Excel options by pressing the ” OK ” button in each case .
How to deactivate Excel macros
Excel macros can also be used to execute malicious code. You should deactivate the macros if you are not using them:
- Open the macro settings in Excel, as described above, depending on your version.
- Then click on “ Disable all macros without notification ”.
How to create macros in Excel
After you have enabled macros in your version of Excel, you can now create macros, making your work much easier. To create macros, click on “VIEW” in the Excel menu bar – on the far right you will find the macro area where you can create new macros with the “Record macro …” function. You need to define each macro with the following properties:
- Macro name
- Key combination with which you want to trigger the macro
- Save macro in
- description (optional)
