Ways You Can Enable Macros in Excel and Disable When Needed

Microsoft Excel has quickly become a go-to app for data analysis, financial models, and other business computations and calculations, but many users are unaware of the app's automation features. Namely, only a handful of users use Excel's macros and VBA code features, which can significantly increase your productivity and efficiency.

Today's guide will show you what macros are and how they can help your workflow, provide five methods for enabling macros in Excel, and teach you how to turn them off when unnecessary. Moreover, we'll also show you how to prevent potential data loss caused by Excel macros.

Note:If you are a Mac user, this guide will help you to enable and disable macros in Excel.

Macros in Excel

Before jumping into the different methods for turning Excel macros on and off, we must first briefly discuss what macros are, what they're used for, and what benefits they bring to spreadsheet enthusiasts.

What Are Excel Macros?

At their core, Excel macros are a pre-recorded set of commands that automate tedious Excel tasks and streamline your spreadsheet workflow. They use the Visual Basic for Applications programming language that Excel understands, or VBA for short, and are therefore known as VBA codes.

Benefits of Enabling Macros in Excel

As pre-recorded scripts that do Excel tasks with a click of a button, macros bring various benefits to users working in Excel:

• ⏰ Save Precious Time – As they can be invoked with keyboard shortcuts and do multiple actions in a few clicks, macros can significantly increase efficiency and productivity, saving users time for other Excel tasks.
• 🚀 Avoid Repetitive Tasks – Excel macros are excellent for reducing the number of tedious spreadsheet tasks because they can automate multiple actions to a few shortcuts.
• 🔧 Decrease Human Errors – Regardless of your Excel skills, you can still make mistakes as a human. Conversely, macros can't, and they can create more accurate analyses by reducing human input and, therefore, errors.
• 🎯 Simplify Spreadsheet Formatting – Macros are fantastic for formatting spreadsheets and creating charts, with most Excel users utilizing them for these capabilities.

In essence, macros make it effortless to manage data, pivot tables, and tidy up your spreadsheets while saving time, so let's see how you can enable them in Excel.

How to Enable Macros in Excel

Now that we know more about Excel macros and their advantages for streamlining and automating your Excel spreadsheet workload, we’ll immediately dive into the five methods for enabling them in Excel. These five ways work in excel 365, excel 2019, excel 2016, excel 2010 and excel 2007.

Enable Macros in Excel Using Security Notice for a Single Workbook

As macros can be dangerous to your Excel data, opening a workbook that contains them will display a security warning, asking you to enable macros. That's one of the most basic ways to turn macros on in Excel and only requires you to do the following:

1. Open Microsoft Excel and tap the File tab in the top left corner.
2. Go to Open and load up a workbook that has macros enabled.
3. Tap the Enable Content on the security warning when the workbook loads.

   

4. Confirm your choice by hitting Enable Macros in the pop-up window.

   

Remember to carefully read the warning about trustworthy sources, as enabling macros on documents whose origin you're unsure of can be catastrophic to your other XLS files.

Enable Macros in Excel Using Backstage View for a Single Workbook

Backstage View is another excellent way to enable Excel macros for a specific workbook, and here's how you can use it:

1. Launch Microsoft Excel and navigate to the File tab.
2. Head to Info and tap Enable Content.

   

3. Select Enable All Content, which makes it a trusted document and allows macros to run in that specific workbook.

Like the security warning method, you should only click Enable All Content if you're sure about the legitimacy and origin of the XLS files you're using.

Enable Macros in Excel for One Session Using Microsoft Office Security Options

Since Excel macros can be catastrophic to your XLS files when misused, the Excel app also offers a way to enable them for a single session so you wouldn't forget to turn them off when not needed. This method uses steps similar to the previous one but lets you adjust how long macros will be enabled. Here's how you can use it:

1. Open Microsoft Excel and head to the File tab in the top left corner.
2. Navigate to Info and click Enable Content.

   

3. This time, click on Advanced Settings.
4. Choose the Enable content for this session option and hit OK.

   

Enabling macros for single use is perfect for XLS files you first want to investigate without making them trusted documents like in our previous method.

Allow Macros in Excel in All Workbooks Using the Trust Center

Excel also has a dedicated security settings area called the Trust Center. Users can find various security settings, including executing VBA codes or macros. That makes the Trust Center an excellent alternative method for allowing macros in Excel, and here's how you can use it to enable macros in all your Excel workbooks:

1. Launch Microsoft Excel and click the File tab.
2. Select Options at the bottom, tap Trust Center, and click on Trust Center Settings.

   

3. Locate and click on Macro Settings on the left side of the window.
4. Select the Enable all macros option and hit OK.

   

Remember that using the Trust Center enables macros throughout all your workbooks, putting you at a more significant risk, which is why the Excel app warns you that this isn't recommended due to the potentially dangerous VBA code.

Using Trusted Location to Turn on Macros Permanently in Excel

Finally, there's also the permanent enabling of Excel macros in specific computer or network locations, which lets you add trusted locations instead of adjusting global macro settings. Yet again, Excel's Trust Center is the location you'll need to visit, and here's how you can use its Trusted Location feature:

1. Open Microsoft Excel, navigate to the File tab, and click Options.
2. Head to Trust Center > Trust Center Settings > Trusted Locations.
3. Modifying the default Trusted Locations isn't recommended, and you can add your own instead. Hit the Add new location button to do it.

   

4. Hit Browse and add a location of your choice. You should select the "Subfolders of this location are also trusted" option before hitting OK.

   

This option is highly recommended as it lets you save XLS files with VBA code or macros in a specific location. These files will be the only ones that can run macros, which allows you to enable them without tampering with Excel's security settings and putting yourself at a significant risk of data loss.

How to Disable All Macros in Excel

If you no longer need Excel macros and want to turn them off entirely, an excellent way to do that is, once again, through Excel's Trust Center. You can follow these steps to turn off macros in Excel:

1. Open Excel and click the File tab.
2. Hit Options in the bottom left corner.
3. Click on Trust Center > Trust Center Settings.

   

4. Choose Macro Settings and select Disable all macros without notification.

   

5. Tap OK when done.

It's worth noting that the Trust Center also allows you to turn off Excel macros with notifications or turn off everything except digitally signed macros, which are VBA codes and macros you can trust.

Potential Issues With Using Macros in Excel

Enabling macros comes with various security warnings and "not recommended" notifications in Excel, and that's related to the dangers of using macros. Therefore, following safe practices is highly advised to ensure XLS file security and macro functionality, and we've provided a few tips for you:

Test Macros on Unimportant Files

Having a random XLS test file for testing purposes is highly recommended, especially when you can't confirm the trustworthiness of the macro's source. Such a file will allow you to test the macro function and peek into its VBA code to ensure it's safe for your important tasks and data.

Keep Your Computer Updated

Macros can be the carriers of viruses and malware, and these hazardous VBA codes can effortlessly penetrate older computers with outdated OS and antivirus tools. Thus, keeping your PC up-to-date with the latest OS security patches and always having the newest antivirus software version with recent virus definitions is crucial when handling macro-enabled files, especially from untrusted sources.

Use a Data Recovery App

Untested and unvalidated macros can cause data loss when used with the wrong XLS files, especially if the macros are designed to make Excel data changes in your spreadsheets. Nonetheless, a dedicated data recovery app like Wondershare Recoverit can reverse such data losses.

Here's how you can use it in case of macro-caused data loss:

1. Launch Wondershare Recoverit and select Hard Drives and Locations.

   

2. Select the disk drive that stores macro-enabled XLS files. The app will automatically scan it for lost data.

   

3. You can use various file filters and add keywords to look for specific Excel data.

   

4. Wondershare Recoverit lets you preview files to verify their integrity before recovering them. You can pause or stop the scan and click Recover to save the discovered Excel files when ready.

   

Wondershare Recoverit can help whether you're dealing with unsaved recent changes, lost Excel files, deleted XLS data, or data loss caused by macros. The app supports over 500 data loss situations and works wonders with documents, audio, pics, and videos, supporting over 1,000 file types.

You can use it on internal disk drives, USBs, external storage devices, NAS servers, and more, as the app supports over 2,000 storage devices, making it an incredible tool for battling data loss situations.

Conclusion

Enabling macros can significantly enhance your Excel experience, increasing your productivity and efficiency, and you can do it for single workbooks or across all files. Excel's Security Notice and Backstage View handle macro settings for one workbook or session. The app's Trust Center and Trusted Location let you enable macros in Excel across all documents or turn them off entirely.

Due to their file-modifying capabilities, macros come with various warnings, and if you're handling macro-enabled files from unverified sources, it's best to follow the mentioned safe practices.

These include testing macro on unimportant XLS files, keeping OS and antivirus up-to-date, and using a dedicated data recovery app like Wondershare Recoverit if a macro causes data loss.