The steps in this article will show you how to enable macros in Microsoft Excel. We will first briefly cover the steps that are needed if you want to change the way that macros behave in your spreadsheets, then we will continue below with additional information and pictures for the steps.
- Open Excel.
- Select the File tab at the top-left of the window.
- Click the Options button at the bottom-left of the window.
- Choose the Trust Center tab at the left side of the Excel Options window.
- Click the Trust Center Settings button.
- Select the Macro Settings option in the left column of the Trust Center window.
- Choose the Enable all macros option.
- Click OK, then click OK again to save your changes.
Microsoft Excel has some advanced features that can really help to automate your workflow when editing spreadsheets. Among these features are things like VBA, which is a code language that lets you really take control of what happens in your spreadsheet, as well as a feature called macros.
A macro in Microsoft Excel is a recorded series of actions that automate certain tasks. For example, if you often work in a spreadsheet and perform the same series of steps, then there is a strong possibility that you can record a macro to do it, which you would then run again later to perform all of those actions automatically. Our guide is going to show you where to locate the setting that controls how and when macros are run, even giving you the option to choose to enable all macros that may try to run in any file that you open in Excel.
How to Enable Macros in Microsoft Excel By Changing an Option on the Macro Settings Menu
The steps in this guide were performed in Microsoft Excel for Office 365, but will also work in other versions of the Microsoft Office spreadsheet application. Note that enabling all macros is a potential security risk, so it’s something that you should only do if you are certain you can trust the person that sent you the file that contains the macros you are trying to run.
Step 1: Open your Excel file.
Step 2: Click the File tab at the top-left of the Excel window to open Backstage view.
Step 3: Click the Options button at the bottom of the column on the left side of the window.
Step 4: Click Trust Center in the left column of the Excel Options window.
Step 5: Click the Trust Center Settings button.
Step 6: Choose the Macro Settings option.
Step 7: Select the Enable all macros option (not recommended) or one of the other macro options listed on this screen.
Step 8: Click the OK button at the bottom of the Trust Center window, then click the OK button at the bottom of the Excel Options window.
How to Get the Developer Tab in the Excel Ribbon
While the method above is a good way to run macros in Excel, it doesn’t help you with their creation. Luckily you are able to generate your own Excel macros within your spreadsheet by making an adjustment to the ribbon so that you get the Developer tab. Once the Developer tab is enabled you can see several new ribbon options including Code, Add-ins, Controls, and XML.
- Select the File tab.
- Click the Options button.
- Choose the Customize Ribbon tab.
- Check the box to the left of the Developer option in the right column, then click the OK button.
You should now see the Developer tab at the top of the window, where you will find a Record Macro button in the Code section of the ribbon. There is also a Macro Security button in that section of the ribbon that you can click, which will open the menu from the previous section where you can adjust your security settings or disable macros a little more quickly.
Additional Notes
- As mentioned earlier, this method for enabling macros will work in most other versions of the Microsoft Office spreadsheet application, including Excel 2007. Note, however, that you will need to click the Office button rather than the File tab in Excel 2007 if you want to get to the Excel Options dialog menu.
- If you are only making this change to your security settings so that you can work with one file, and you rarely use macros, then it may be a good idea to return to the Trust Center and disable macros when you are finished. Macros can be used maliciously, so it’s a good idea to protect your computer in the event that you accidentally open a bad Excel file in the future.
- Many Excel files that contain macros will show an Enable Content button at the top of the window when you open an Excel file. You will typically need to click that button before you are able to run the macros. Alternatively you can click the File tab, select the Enable Content tab, then choose the Enable All Content option.
- You may have noticed a Trusted Locations tab in the Trust Center dialog box. This gives you the ability to see locations on your computer that are currently trusted. There is an Add New Location option on that menu if you would like to designate your own custom trusted location. If you save a file to one of those locations, Microsoft Excel will automatically trust any file in one of those locations, meaning that it will not open in Protected View. Microsoft recommends moving files to one of these trusted locations rather than changing your macro security settings.
- You can open the Visual Basic editor and use VBA code from the Developer tab by clicking the View Code button in the Controls section, or by using the F11 keyboard shortcut.
- If you do elect to change your macro security option, you are creating an environment where dangerous code can run on your computer. This could lead to a situation where your entire computer is affected, not just Excel or the Excel file that contained the code.
- On the Macro Settings menu, one of the security settings is to Disable all macros except digitally signed macros. This option means that you will disable macros by default for any file that you open, unless the file was created by a trusted publisher. You can click the Trusted Publishers tab in the Trust Center to see the list of publishers to whom you have granted this designation.
- The default security level for macros in Excel is the Disable all macros with notification option. If you do elect to enable macros, but it’s only a temporary change, then you can always go back to the Macro Settings menu and revert to this default option.
- Other Office applications, like Microsoft Word, also have a Developer tab and the ability to record and run macros.
- If an Excel file has a macro in it, the file type will be .xlsm rather than .xls or .xlsx. If you are creating your own Excel file with a macro you will be prompted to make the change to this macro-enabled file type, or to save a macro-free workbook in one of the standard file types.
Find how how to include your spreadsheet’s file name on each page when you print if you are concerned about the pages of your Excel file becoming separated after you print it.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop and more.