Allowing macros in Excel lets you run VBA (Visual Basic for Applications) code that automates repetitive tasks, such as formatting reports, cleaning data, or generating charts. By default, Excel blocks macros for security reasons. To enable them, you can either change the Trust Center settings for all workbooks (not recommended for files you don’t trust) or use the simpler per-file method: open the file, click the yellow “Enable Content” button, and the macro will run for that session. This guide covers both approaches, plus how to make a folder a trusted location so macros run automatically.
What You’ll Need
- Microsoft Excel 2016, 2019, 2021, or Microsoft 365 (Windows or Mac). Steps for Excel for the web are noted separately.
- A macro-enabled workbook (file extension
.xlsm) or a file with a macro you want to run. - Administrator rights on your computer if you plan to change Trust Center settings (usually not needed for the simple “Enable Content” method).
Step-by-Step
Method 1: Enable Content for a Single Workbook (Easiest & Safest)
- Open the file in Excel. Double-click the
.xlsmfile. If the file came from the internet or email, Excel may show a yellow bar at the top with a shield icon. - Click “Enable Content” on the yellow bar. A dialog may ask “Enable macros?” – click “Enable Macros” (or “Yes”).
- Run your macro. Press
Alt+F8(Windows) orOption+F8(Mac) to open the Macro dialog, select the macro, and click “Run”. - Note: This only enables macros for this file, and only for this session. Closing and reopening the file will show the yellow bar again.
Method 2: Change Trust Center Settings (Allows All Macros)
- Open Excel and go to File > Options (Windows) or Excel > Preferences (Mac).
- Windows: Click “Trust Center” in the left sidebar, then click “Trust Center Settings…”. Mac: Click “Security & Privacy” in the Preferences window.
- Windows: In the Trust Center, select “Macro Settings” on the left. Mac: You’ll see a “Macro Settings” section directly.
- Choose an option:
- Disable all macros without notification – blocks all macros (default).
- Disable all macros with notification – shows the yellow bar (recommended).
- Disable all macros except digitally signed macros – runs macros signed by a trusted publisher.
- Enable all macros – runs all macros without asking. Warning: This is risky; only select if you trust every file you open.
- Click OK twice to close all dialogs. Restart Excel for changes to take effect.
Method 3: Add a Trusted Location (Auto-Enable for a Folder)
- Go to File > Options > Trust Center > Trust Center Settings (Windows) or Excel > Preferences > Security & Privacy (Mac).
- Windows: Click “Trusted Locations” in the left sidebar. Mac: Click “Trusted Locations” tab.
- Click “Add new location…”. Browse to the folder where your macro workbooks are stored (e.g.,
C:\MyMacros). - Optionally, check “Subfolders of this location are also trusted” if you want to include subfolders.
- Click OK to add the location. Now any
.xlsmfile in that folder will have macros enabled automatically – no yellow bar.
For Excel for the Web (Browser)
- Excel for the web (Office.com) cannot run VBA macros. You can only view the file. To run macros, you must open the file in the desktop Excel app.
Common Mistakes
- Clicking “Enable Content” on a file you don’t trust. Macros can contain harmful code. Only enable if you know the source.
- Saving the file as
.xlsxinstead of.xlsm. If you save a macro-enabled file as .xlsx, Excel will strip out the macros (or warn you). Always save as “Excel Macro-Enabled Workbook” (.xlsm). - Changing global settings to “Enable all macros” permanently. This leaves you vulnerable to any malicious file. Use the per-file method or trusted locations instead.
- Forgetting that macros are disabled after restarting Excel. The “Enable Content” button only applies to that session. You must re-enable each time you open the file (unless it’s in a trusted location).
- Looking for macros in the wrong place. Macros are not in the ribbon by default. Use
Alt+F8to open the Macro dialog.
Tips
- Check the file’s digital signature. If the macro is signed by a trusted publisher, you can set Excel to run signed macros automatically (option 3 in Method 2).
- Use a trusted location for your own macros. Create a folder like
C:\Users\YourName\Documents\MyMacrosand add it as a trusted location. Then your own files always run. - Keep a backup copy. Before running any macro, save a copy of your workbook (File > Save As > different name). Macros cannot be undone with Ctrl+Z.
- Test macros on a copy first. If you received a macro from a colleague, open it in a copy of your data to see what it does.
- Use the View Macros dialog (Alt+F8) to see all available macros before running, and read the code if you’re comfortable (Alt+F11 opens the VBA editor).
Troubleshooting
- The yellow bar doesn’t appear. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and ensure “Disable all macros with notification” is selected. If it’s set to “Disable all without notification”, the bar won’t show.
- “Macros are disabled” error message. This usually means the file is in a location that isn’t trusted and you haven’t clicked Enable Content. Click the button, or move the file to a trusted location.
- Macro runs but does nothing. Press
Alt+F11to open the VBA editor and check for errors (highlighted in yellow). Also verify the macro is assigned to a button or shortcut correctly. - File opens but macros are grayed out. You may be in “Protected View” – click “Enable Editing” first, then “Enable Content”.
- Can’t find the Trust Center on Mac. In Excel for Mac, go to Excel > Preferences > Security & Privacy. The options are slightly different but include Macro Settings and Trusted Locations.
- Macro works on Windows but not on Mac. Some VBA functions are platform-specific. Check for Windows API calls or file paths that differ. You may need to adjust the code.
Frequently Asked Questions
Is it safe to enable macros?
Only if you trust the file’s source. Macros can execute arbitrary code, including deleting files or sending data. If you didn’t create the file or don’t know who did, don’t enable macros.
Why does Excel block macros by default?
Because macros are a common vector for malware. Microsoft’s default is to disable them and notify you, so you can decide whether to trust each file.
Can I enable macros for all files in a folder automatically?
Yes. Add that folder to Trusted Locations (Method 3). Files in that folder will run macros without prompting.
What is the difference between .xlsm and .xlsx?
.xlsx cannot contain macros. .xlsm is the same format but explicitly allows VBA code. If you save a macro file as .xlsx, Excel will remove the macros.
How do I run a macro after enabling it?
Press Alt+F8 (Windows) or Option+F8 (Mac), select the macro from the list, and click “Run”. You can also assign a macro to a button or keyboard shortcut.
Does Excel for the web support macros?
No. The browser version only lets you view the file. You must open it in the desktop Excel app to run macros.
Related
- Export Email to Excel: 4 Quick Ways
- Export EndNote to Excel: 4 Easy Ways
- Excel Yearly Budget Template: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- Split String in VBA: Syntax and Practical ExamplesUse VBA’s built-in Split function to divide a string into a zero-based array: parts = Split(text, delimiter). For…
- Excel VBA Unprotect Sheet: Safe Methods and ExamplesTo unprotect a worksheet with VBA in Excel for Microsoft 365 on Windows, use Worksheet.Unprotect and supply the…
- Excel VBA: Find a Character’s Position in a StringIn Excel VBA, use InStr to find a character’s position in a string: it returns the character’s one-based…
- VBA Dictionary Keys: How They Work and How to Use ThemIn VBA, a dictionary key is a unique identifier used to store and retrieve an associated value in…
- Excel VBA Index Match Multiple CriteriaYes, you can use VBA to perform an INDEX/MATCH lookup with multiple criteria by building a helper column…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.