This guide will explain how to enable macros in your Excel spreadsheet.
Excel macros are a powerful feature that allows you to record and automate actions and tasks you frequently do in your workflow.
If you’ve ever worked with Excel, you may have found some tasks tedious or repetitive. Luckily, Microsoft Excel comes with a powerful scripting language known as Visual Basic for Applications or VBA that you can use to automate your workflow.
While VBA is a programming language, Excel can create scripts automatically through the use of a macro recorder. For example, you can create a macro that saves each worksheet as a separate PDF or a macro that hides all worksheets except the current active sheet.
While macros are extremely useful, they can also act like malware. These macros have the potential to run malicious computer code. To combat this, Microsoft has disabled all macros by default. Users that want to enable macros will have to tell Excel to do so explicitly.
Microsoft has also made it easier to detect whether a document contains macros or not. Standard Office documents are saved with a file type ending with ‘x’ while macro-enabled files end in ‘m’.
While running a macro-enabled spreadsheet from unknown sources is not advisable, there are still plenty of legitimate use cases where users may need to run macros daily. If you would like Excel to enable macros by default, you must adjust the application’s settings.
This use case is one way to use macros in Excel. If you are an advanced Excel user, you will likely already have several workbooks with macros added. Removing Excel’s default security settings can help you save time running your workflow.
Now that we know when to activate macros in Excel, let’s take a look at a sample spreadsheet with a simple macro.
A Real Example of a Spreadsheet with Macros Enabled
Let’s take a look at a real example of an Excel spreadsheet with macros enabled.
In the example below, our workbook includes a macro labeled LoopSheetsSaveAsPDF. When we run this macro, each sheet in the workbook will be exported as its own PDF.
To achieve this macro, we used the following script:
The script simply uses a loop structure to go through the list of worksheets in the current active workbook. Each worksheet is converted into a PDF and is saved in the same directory as the workbook.
You can make your own copy of the spreadsheet above using the link attached below.
If you have not yet followed our guide to enable macros permanently, you will have to allow Excel to enable macros for this sample spreadsheet explicitly.
If you’re ready to enable macros permanently in Excel, follow our guide in the next section!
How to Enable Macros in Excel
This section will guide you through each step needed to enable macros in your Excel spreadsheet.
When opening a spreadsheet with macros, the default setting in Excel is to disable macros. Users can see a security warning below the ribbon menu. To enable macros for this specific spreadsheet, simply click on the Enable Content button.
If you would like to set up your Excel so that it always runs sheets with macros, you can follow these steps:
- First, navigate to the File tab. On the bottom-left-hand side of the screen, click on Options.
- In the Excel Options menu, click on the Trust Center tab.
- The Trust Menu tab includes options that help keep your computer secure. Microsoft has a good reason to prevent Excel from running macros by default. If you understand these security concerns and would still like to change the settings, you can proceed by clicking on the Trust Center Settings button.
- Under the Macro Setting tab, click the option that says ‘Enable VBA Macros’. Click on OK to apply these changes to the current session.
Frequently Asked Questions (FAQ)
- Can we enable macros in the Excel web application?
Since Visual Basic for Applications is not available in Excel for the web, users cannot create, run, or edit macros online. Users can still open workbooks with macros, which will remain in the workbook when uploaded. To work with macros, users must download the workbook and open the file in the Excel desktop application. - How do I prevent running malicious macros?
Only open macro-enabled workbooks from trusted sources. When opening a file from an unknown source, ensure that Excel’s default settings for macros are enabled. This will prevent Excel from automatically running a potentially harmful script.
This step-by-step guide should be all you need to enable macros in your Excel application. Our guide shows how to adjust application settings to enable macros when opening a document automatically.
Excel macros is a powerful feature in Excel that you can use to automate your workflow. With so many other Excel features available, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us.