How To Enable Macros In Excel

How To Enable Macros In Excel

5 mins read1.3K Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Jul 18, 2023 20:04 IST

A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. The article talks about how to enable Macros in Excel.

2022_05_How-to-Enable-Macros-in-MS-Excel-1.jpg

MS Excel has a clean, feature-extensive menu with useful functions like sorting, grouping, filtering, and applying different formulas to sort and manage data. In version 5.0, Macros can help to automate processes and tasks in MS Excel. This feature is especially helpful if you work on repetitive tasks. Macros can help to automate those tasks. 

Content

In this article, we will cover how to enable Macros in different versions of the spreadsheet program.

What Are Macros?

MS Excel macro is a series of instructions written in a programming language called VBA (Visual Basic for Applications) that are executed to perform a certain task. 

For security reasons, Excel disables macros, so when you download a template from the site that has macros and you want to execute some buttons, it will show you the following message:

This means that you must enable macros (i.e. allow it to execute the actions we have programmed) to use those buttons. How do we enable macros? It will depend on the version of Microsoft Excel you have; Here we will see how to do it for each of them:

Top MS Excel Interview Questions
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more
Microsoft Excel vs. Google Sheets: Shiksha Online
Microsoft Excel vs. Google Sheets: Shiksha Online
MS Excel and Google Sheets are the most widely used spreadsheet applications, offering similar data management, analysis, and visualization functionalities. While they share many common features, users should be aware...read more
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more

There are two ways to enable Macros in Excel –

1. Enable Macros in Excel through Trust Center

Enabling macros in your Microsoft Office Excel workbook can save you time. A macro is typically written to automate a manual task by executing a set of computer instructions using Visual Basic for Applications (VBA) code. Use the Trust Center in Excel to enable or disable all macros in your workbook. Enabling all macros in your workbook will allow all macros to run. So, to enable Macros through Trust Center, you can follow the below steps –

Step 1. Open a spreadsheet, go to the ribbon, and click on the File option.

Step 2. Click on Options located at the lower left.

Step 3. After you click Options, you will be navigated to the Excel Options dialog box. Click on Trust Center.

Step 4. You will find Trust Center Settings, open that.

Step 5. Click on Macro Settings.

Step 6. Select the setting that you want to apply to your worksheet.

Read MS Excel Tutorials

Macro Configuration Options

Below are the explanations of each option, as seen in the above image.

Disable all macros without notification – This option will disable all macros and security alerts.

Disable all macros with notification – Unlike the option above, this one will display security alert messages, the famous yellow bar at the beginning of this article.

Here, the user needs to enable each macro they want to run. This option is available by default in Excel.

Disable all macros except digitally signed macros– Again, it will disable all macros and display security warnings. But if the macro has an official digital signature, it will run without a problem, and you don’t need to enable each macro.

Enable all macros (not recommended, dangerous code can run) – Once you enable this option, all macros execute with a warning. This makes your computer vulnerable to malicious codes.

How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more
How to Use HLOOKUP in Excel
How to Use HLOOKUP in Excel
The HLOOKUP in Excel enables finding data in a dataset horizontally. HLOOKUP stands for Horizontal and searches for a value in the top row of a table. The function then...read more

2. Enable Developer Tab in Excel

The “Developer” tab must display on the Excel menu ribbon to access any of the options for editing an existing macro. This tab is not part of the standard Excel menu ribbon. To show it, right-click on the Ribbon and click on “Customize the Ribbon”.

An Excel Options dialogue box will open up. Check the “Developer” option under Main Tabs and click “OK”. The developer tab will appear at the bottom of the menu ribbon.

Once you click OK, the Developer tab will be available beside View.

Developer Tab Options

The Excel Developer tab has some useful advanced options, which are –

The Macro Options: There is a macro ‘Code’ group in the developer tab that you can use to Record Macro, Use Relative References, and change Macro Security.

The first option on the Developer Code tab is the “Visual Basic” button. Clicking that button opens the VBA programming window. 

The second option is “Macros”. Clicking this button opens a small window showing the list of existing macros as well as various options. 

Add-ins: The Add-ins options allow you to manage inbuilt add-ins, Excel Add-in COM Add-ins.

Controls: The Controls group allows you to use interactive tools such as an Insert, Design Mode, View Code, Run Dialogue, etc.

XML: The XML group enables the Import and Export of data, adding Expansion Packs, Refresh Data, Map Properties, etc.

When you enable macros in Excel, you must consider the following factors –

  • The VBA code file is stored as a Macro-enabled workbook. VBA code will not be saved if the file is saved with another extension, hence macros won’t get enabled.
  • The Enable all macros runs all macros without warning or authorization. It mentions the warning while you choose this option. If the untrusted source macros are enabled, then things can get messy
  • The Disable all macros without notification option don’t explain why the macros don’t work. To read the warnings, always select Disable all macros with notification.

Conclusion

Enabling macros is simple yet dangerous as there is a possibility of encountering problems when accessing a macro-enabled spreadsheet. As a result, Excel blocks macros from external sources for security reasons. I hope this article helped you to learn how to activate macros in Excel. 

Tip – Enable macros only for trusted Excel documents.


FAQs

How do I disable macros in Excel?

To disable macros in Excel- Click the File tab => Options Select Trust Center from the left-hand pane, Click Trust Center Settings Select Macro Settings Disable all macros without notification and click OK

Why are macros not working when I share it with others?

It happens when the macros are in your Personal Macro Workbook and hence any other user cannot access it. You would need to ensure that you save macros into the workbook itself before sharing it with others.

What is macros used for in Excel?

We often perform certain tasks in Microsoft Excel that are repetitive. You can record a macro to automate such repetitive tasks. A macro is a set of actions that can be run as many times as you want. A macro records your mouse clicks and keystrokes.

Is Excel macros easy to learn?

Macros is not an easy topic. It is an advanced topic that uses Visual Basic for Applications (VBA) as its language. If you want to create basic macros, you would need to learn basic syntax and start coding. this will take a few hours. For advanced and complicated program creation, you would need to know about all the Excel formulas and how they work. This might take a couple of months.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio