How to Enable and Disable Macros in Excel
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Using macros in Excel help lower human error and save time by taking away repetitive tasks.
But all the power and might of Excel macros are for naught if you don’t know how to enable them.
In some cases, you would need to disable macros to protect your files.
In this article, we’ll show you how to enable macros in any spreadsheet. You’ll also learn how to disable macros for security purposes.
Kasper Langmann, Co-founder of SpreadsheetoLet’s get into it!
*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.
Table of Content
Why enable/disable macros?
First off, a macro is a series of commands or instructions stored in an Excel module. Macros can be run anytime you need to do tasks related to those instructions.
You can think of it as something like a program inside Excel which contains a series of instructions. Once you run or open them, they will execute the instructions they contain.
Kasper Langmann, Co-founder of SpreadsheetoIf you know how to make a macro in Excel, you would be able to save so much time and even minimize human errors.
The problem is, macros can be used for good and for evil. Macros can literally be used to delete files on your hard drive.
For that purpose, Excel, by default, will not run a macro-enabled worksheet.
If you trust the macro and you would like to use it, then you would have to manually enable it. You can even set up your Excel to always run macros for a trusted worksheet.
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I’ve included one for you (for free).
Download it right below!
Note: Change the file extension to .xlsm which is a Microsoft Excel Macro-Enabled Worksheet. Currently, the file is in .xlsx which is the normal Microsoft Excel extension. We have to change the extension so you can download the file.
Download the FREE Exercise File
How to enable macros in Excel
Enabling macros is easy.
There are two ways you can enable a macro in Excel and both are quite easy:
- Via the message bar
- Via the backstage view
For this tutorial, we included a simple macro file you can download which calculates the final grade average of a data set:
Note: Change the file extension to .xlsm which is a Microsoft Excel Macro-Enabled Worksheet. Currently, the file is in .xlsx which is the normal Microsoft Excel extension. We have to change the extension so you can download the file.
Kasper Langmann, Co-founder of Spreadsheeto
Enable macros from the message bar
Right after opening an Excel document with a macro, you will see a yellow message bar right below the Ribbon.
Try opening an Excel macro file and you’ll immediately see the message bar.
Kasper Langmann, Co-founder of SpreadsheetoIf you trust the source, just click ‘Enable Content’ on the message bar.
Enable macros in the backstage view
Another way to enable macros in Excel is from the Microsoft Office Backstage view (what you see when you click ‘File’ from the tab list).
Click ‘File’ from the tab list.
You’ll see a big ‘Security Warning’ right there.
Press the ‘Enable Content’ shield box and select ‘Enable All Content’.
Excel’s Trust Center macro settings
Excel has a ‘Trust Center’ where you can change macro settings.
To access it, you can click the ‘Trust Center Settings’ under the ‘Security Warning’ on the backstage view.
If you’re opening a non-macro Excel file, you can access the ‘Trust Center’ by clicking ‘Options’ on the left-hand sidebar of the backstage view.
Then, click ‘Trust Center’ from the left sidebar and press the ‘Trust Center Settings’.
On the ‘Trust Center’ window, click ‘Macro Settings’ on the sidebar.
You’ll then see the different options of macro settings.
The options are as follows:
- Disable all macros without notification
- Disable all macros with notification
- Disable all macros except digitally signed macros
- Enable all macros (not recommended; potentially dangerous code can run)
How to disable macros in Excel
Excel, by default, disables all macros with notification.
Every time you open a macro on your computer, Excel will disable it and will have you enable it manually.
Kasper Langmann, Co-founder of SpreadsheetoBut if you would like to disable all macros and not get any notification about it, choose the first option — ‘Disable all macros without notification’ — under ‘Macro Settings’ in the ‘Trust Center’ (discussed above).
Wrapping things up…
Excel macros can both be a blessing and a curse. That’s why you have to be careful with the macros you open.
If you feel like the source isn’t trustworthy, don’t open it. And if you think you don’t need macros at all, feel free to disable it without notification so you wouldn’t be bothered in the future. 👍
Kasper Langmann, Co-founder of Spreadsheeto