How to Enable Macros in Excel (With Definition and Steps)

By Indeed Editorial Team

Published 26 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Macros are basic programs and scripts that automate different tasks within Microsoft Excel. They make work easier by reducing the number of tasks or actions a user is required to perform while creating spreadsheets. Knowing how to enable macros is beneficial because it can make your work easier and faster and can enhance your skills in Excel. In this article, we define what macros are and explain how to create macros in Excel and adjust macro settings in the Excel Trust Centre.

Related: Excel Skills: Definition and Examples

What are macros?

When learning how to enable macros in Excel, it's helpful to know what they are. Macros are scripts that a user can add to spreadsheets. They can automate typical actions in Excel and other Microsoft Office products. Excel disables macros by default for security reasons, as users can add malicious code in Excel through macros. Using Excel macros requires you to first enable the macro feature. A macro is a collection of commands and functions stored in a Microsoft Visual Basic module that a user can execute anytime there's a task associated with those commands.

Users can create macros by recording a series of keystrokes using Excel's macro dialogue box. Their complexity depends on the Excel function you're trying to automate. Macros provide numerous helpful features, such as lowering the risk of human error, which can happen when you perform a lot of repetitive data entry tasks. They also help shorten the time required to do fundamental computing operations and allow you to focus on more complicated problem-solving and idea-generation tasks. They can also help with executing more complex calculations that involve several steps.

Related: Problem-solving Skills: Definition, How to Learn and Example

How to enable macros in Excel

The following steps elaborate on how to enable macros in Excel for a specific workbook if you don't want to alter Excel's default macro settings:

  1. Open your Excel spreadsheet: Select and open the Excel file that contains the macro you want to use. Excel displays a yellow box with a message expressing a security warning that the application has disabled macros when you first launch it.

  2. Locate and select the "Info" tab: Click on the "File" option that's found in the top-left corner of Excel, and after clicking it, you can choose the "Info" option.

  3. Enable the macro content you want to use: Click the option "Enable Content" in the "Security Warning" section, go to the drop-down menu and select "Advanced Options," then click "Enable content for this session." When you close the Excel file window after following these steps, Excel disables the macros for the specific spreadsheet.

  4. Check future sessions: Make sure Excel displays the warning message in the yellow box during your next session to ensure Excel still has macros disabled by default. This helps ensure compromised Excel files don't affect your computer.

How to create macros in Excel

You may want to enable certain macros in your spreadsheet but find that they don't exist. You can record your own macros to automate any repetitive tasks. To record a macro, perform the following steps:

  1. Enable the "Developer" tab: Enabling the "Developer" tab allows you to access Excel's macro and VBA tools. It's necessary to begin with this step because Excel hides the developer tab by default.

  2. Record a macro: When you're viewing the "Developer" tab, click "Code" group and then click "Record Macro". Alternatively, in the "Macro name" box, enter the name of your macro, then enter a shortcut key in the "Shortcut key" box and write a description in the "Description" box and then click "OK" to begin recording.

  3. Perform the actions you want to automate: This can be an action such as filling down a column of data.

  4. Stop recording: Once you've finished recording your macro, go to the "Developer" tab and click "Stop Recording."

  5. Test macro: Ensure to test the functionality of any macros you've recorded to see if any adjustments are required.

Adjust macro settings in the Trust Centre

Macro settings are generally located in the Trust Centre. If you're using a personalised computer, you may have easy access to change your macro settings. If you're using a work or school computer, you may find that the system administrator prevents you from changing any default settings. It's also important to note that you can only change macro settings for your current session. As soon as you close your Excel session, it reverts to its default settings. The steps to change macro settings in the Trust Centre are:

  1. Click the "File" tab and then you can select "Options". Under "Options", click the "Trust Centre" tab and then choose "Trust Centre Settings".

  2. When you're viewing the "Trust Centre" window, you then click "Macro Settings".

  3. Make the selections you want in the "Macro Settings" window and then click "Ok".

Macro security settings and how to change them in Excel

There are various macro security settings available to help you automate your tasks. These include:

  • Disable all macros without notification: This option disables all macros in your document, including any security alerts about them. If you have documents that include unsigned macros that you trust, you can save them in a trusted location, which Excel executes without reviewing them.

  • Disable all macros with notification: This is the default configuration when disabling macros. You can use this setting to enable certain macros on a case-by-case basis.

  • Disable all macros except digitally signed macros: This setting allows macros that are digitally signed by a trusted publisher. A notification pops up for untrusted publishers and you can then select whether to trust the publisher and activate those signed macros.

  • Enable all macros: You can select this option to enable all macros. It's not recommended to use this setting since it can expose your device to malicious code.

  • Trust access to the Visual Basic for Applications (VBA) project object model. These are developer-level settings that are able to create their own macros within Excel. For these macros to function properly, you're usually required to activate this setting.

To change security settings, go to the "Developer" tab and in the "Code" group, click "Macro Security". In this window, click "Macro Settings" and then click the option that you want to activate or deactivate.

Related: 18 Essential Developer Skills You Can Learn

Permanently enabling macros

If you're planning to work with macros frequently, there's an option for permanently enabling macros in your Excel files. This can expose your device to malicious code, so it's important to carefully consider your needs before selecting this option. The standard steps for permanently enabling macros in your Excel files are:

  1. Open the relevant Excel file.

  2. Open Excel's "File" tab and select "Options."

  3. In the "Excel Options" dialogue box, select "Trust Centre settings" under the "Trust Centre" option.

  4. In the "macro settings" option, click on "enable all macros" and then click "OK" to apply the selected macro setting.

Another way to do this is by using the yellow "Security Warning" dialogue box that appears when you open an Excel file that contains a macro. You can enable macros permanently for the Excel file you've selected by simply clicking the "Enable Content" option in this dialogue box.

Risks of using macros

As you work with macros in your Excel files, it's important to be cautious of the following:

  • Excel saves VBA-coded files as a "macro-enabled workbook." Saving these files with a different extension may not save the embedded VBA code, which disables the macros.

  • The security setting "enable all macros" activates all macros without asking for permission or warning. This is risky because this setting enables macros from both trusted and non-trusted sources.

  • The option "disable all macros without notification" may not provide any information about why macros aren't running, so to see macro related warnings, select "disable all macros with notification."

Advantages of using macros

The advantages of macros are they're simple to create, store and use. While you're working, macros can keep track of your keystrokes. You can even assign a keyboard button to a macro, allowing you to perform typical tasks like text formatting, adding formulas to spreadsheets, assigning rows to a target and formatting data into matched columns and rows just by clicking a single keyboard shortcut.

Making adjustments to a macro is also as simple as creating one. When you've recorded a macro, you can use Microsoft's Visual Basic Editor to see, alter and rectify problems in a macro's code. Microsoft provides some in-program assistance to help beginners navigate the process of creating and altering macro code. There are explanations on how to share macros between workbooks with numerous users and how to copy macros from one spreadsheet to another.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles