How to Enable Macros in Excel

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.

macro to save multiple sheets as a PDF

To achieve this macro, we used the following script:

script used to loop between sheets

 

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.

enable content when opening a document with a macro

If you would like to set up your Excel so that it always runs sheets with macros, you can follow these steps:

  1. First, navigate to the File tab. On the bottom-left-hand side of the screen, click on Options.
    enable macros in Excel by opening the Options menu
  2. In the Excel Options menu, click on the Trust Center tab.
    Click on the Trust Center to enable macros in Excel
  3. 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.
    enable macros in Excel
  4. Under the Macro Setting tab, click the option that says ‘Enable VBA Macros’. Click on OK to apply these changes to the current session.
    enable macros in Excel

 

 

Frequently Asked Questions (FAQ)

  1. 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. 
  2. 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. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like