How to enable macros in Excel 2016, 2013, and 2010

This tutorial teaches how to enable macros in Excel 2010-2013. You'll also find step-by-step instructions showing how to disable macros in Excel or turn them on via Message bar and Backstage view. In addition you'll find a tip to run a macro even if all macros are disabled and get a link to a piece of code that gets users enable macros in a workbook.

First off, I'd like to share a brief intro to Excel macros and security risk linked to enabling them. A macro is a series of commands assembled together and designed to automate frequent routine tasks in Excel. You can run macros any time to perform certain actions.

Many macros are created using Visual Basic for Applications, or VBA. Lots of Excel users can record simple macros to streamline their work with spreadsheets. Developers create codes that are more complicated.

When enabling a macro in Excel that was written by someone else, you need to remember that it can carry a potential security risk. A person with malicious intent, like hacker, can make a destructive macro to spread a virus on your PC or network.

Read on to find detailed descriptions of macro settings, steps to enable Excel macros and save a macro-enabled workbook. You can also click any of the links below to get straight to the point.

How to enable macros in Excel from the Message Bar

If you open an Excel document that contains macros, you'll see the yellow Message Bar with a shield icon and the Enable Content button.

If you are sure, the macros are from a trustworthy source, just press the Enable Content button on the bar.

Press the Enable Content button on the Message Bar
The file will be opened as a trusted document and the security warning will disappear.

Excel - enable macros via the Backstage view

You can also enable macros in Excel using the Microsoft Office Backstage view. You can see the yellow Message Bar if you click the File tab.

  1. Click on the File tab.
    Click the File tab
  2. You'll see the yellow Security Warning area. Click Enable Content.
    In the Security Warning area, click Enable Content
  3. You will see 2 features: Enable All Content and Advanced Options. Select Enable All Content to always make the document's active content available.

The current Excel file will become a trusted document and all macros will be enabled.
Select Enable All Content to always make the document's active content available

How to always run macros for a trusted worksheet

If you create and share workbooks with macros you may want to avoid getting the security message every time. If you get all your workbooks only from reliable sources, you can choose to instantly run all macros for all publishers. Below you'll see how to enable all macros in Excel by default.

  1. Go to File -> Options -> Trust Center and press the Trust Center Settings ... button.
    Go to File -> Options -> Trust Center and press the Trust Center Settings ... button

    Tip. You can also click on the Trust Center Settings link on the Security Warning.
    Click on the Trust Center Settings link
  2. You will see the Trust Center window. Select the Macro Settings option.
    Select the Macro Settings option
  3. Select the radio button Enable all macros (not recommended, potentially dangerous code can run).

From now on all macros will run without warning.

Please note that this setting makes your computer open to viruses. You should be sure you get all workbooks with macros only from the trusted publishers.

Adjust macro settings on the Trust Center window

You can find all macro settings on the Trust Center window. These settings let you adjust the way you want to work with macro-enabled workbooks.

Follow the steps from How to always run macros for a trusted worksheet to get the Trust Center window.

Please see all the macro settings explained below:

  • Disable all macros without notification. Macros and security alerts regarding them get turned off.
  • Disable all macros with notification. If you select this radio button, macros will be disabled, but security alerts will appear if there are macros in a file. It lets you enable macros in Excel depending on the source.
  • Disable all macros except digitally signed macros. In this case, macros are turned off, security alerts appear and digitally signed by a trusted publisher macros run. If you have not trusted the publisher, you are offered to enable the signed macro and trust the publisher.
  • Enable all macros (not recommended, potentially dangerous code can run). All macros run without warning. Please note that this setting makes your computer open to viruses.
  • Trust access to the VBA project object model. This checkbox forbids or allows programmatic access to the Visual Basic for Applications (VBA) object model from an automation client. This security setting is for the code that computerizes an Office program and manipulates VBA environment and object model. For automation clients to access the VBA object model, the user running the code must grant access. You need to tick the check box to give access.

Run a macro even if all macros are disabled

Say the setting Disable all macros without notification is turned on your PC, but you need to run certain macro here and now. If you have Excel 2007-2013, you can enable macros even if your security settings don't allow this. The point is that Excel sees certain locations as trusted zones. This means if you store your macro-enabled files there, the security check can be omitted and the macro will run.

Please see the list of these trusted locations below:

Program Files\Microsoft Office\Templates

Program Files\Microsoft Office\Office12\Startup

Program Files\Microsoft Office\Office12\Library

Program Files\Microsoft Office\Office12\XLSTART

You can add any location on your PC to the trusted zone, by following the steps below:

  1. In Excel navigate to the File tab -> Options -> Trust Center -> Trust Center Settings -> Trusted Locations.
    In Excel navigate to the File tab -> Options -> Trust Center -> Trust Center Settings -> Trusted Locations
  2. Click on the Add new location button to see the Microsoft Office Trusted Location dialog box.
    See the Microsoft Office Trusted Location dialog box
  3. Click Browse to search for the needed place on your PC.
    Click Browse to search for the needed place on your PC
Note. Before adding any location to trusted, make sure it's really safe since such location can be attacked by hackers.

How to save a macro in a workbook

Macros need you to save the files in a new macro-enabled workbook format. This type adds security warning to your file. If you don't choose the correcttype, the command will not be kept. Below you'll find how to save a macro-enabled Excel document:

  1. Navigate to File -> Save As and browse for the necessary location on your PC.
  2. Click on the Save as type: arrow and select the Excel Macro-Enabled Workbook from the list of types.
    Select the Excel Macro-Enabled Workbook from the list of types
  3. Click Save to keep the results.

If you forget to select the type of Macro Enabled Workbook, you'll get a warning message notifying that the macro will not be saved:
Get a warning message notifying that the macro will not be saved

Click No if you need to keep the macro.

How to disable macros in Excel 2010-2013

Suppose, you only proofread the files and don't need macros enabled on your PC. You get documents from various sources and prefer to set your security settings to the highest level. Since some macros can be potentially dangerous, you can disable them all using the Trust Center options.

  1. Follow the steps from How to always run macros for a trusted worksheet to get the Trust Center window.
  2. Select the Disable all macros without notification or Disable all macros with notification radio button. If you go for the first option, you will not know if there are any macros in the current file. The second button lets you identify Excel workbooks that contain macros.

Use special code to make users enable macros in a workbook

The code from this article forces a user to enable macros in a workbook. This code hides all sheets until the macro is enabled. It also adds a warning sheet that alerts the user to run macros. As soon as a user turns the commands on, the warning sheet is hidden and all other content is shown.

  1. Add the code to your workbook.
  2. Create a new sheet named "Warning".
  3. Add a message on the worksheet asking the user to re-open the workbook and enable macros.
  4. Finally, save your workbook.

You may also be interested in

Check out Tutorial with Excel examples about Macros for some nice and simple samples to automate your daily spreadsheet tasks.

If you are starting to learn macros, read How to insert and run VBA code in Excel - tutorial for beginners.

That's all about enabling and disabling macros in Excel. Hope you've found the information helpful. Feel free to leave your questions in the comments below. Be happy and excel in Excel!

32 Responses to "How to enable macros in Excel 2016, 2013, and 2010"

  1. fourny says:

    problème de compatibilité en Excel 2007 et Excel 2013
    Un logiciel de compta comportant des macros qui fonctionnent sur 2007 ne fonctionne plus sur 2013 ?

  2. Anonymous says:

    how to use ful formate

  3. Arun says:

    Great way of teaching.. very simple and effective.

  4. Sohaib says:

    Hello
    Please give me explanation for the following formula, I will be very thankful for this kindness.
    1: =IFERROR(VLOOKUP(B4,CatModules!B:C,2,FALSE),"")
    2: =IF(CostGroup!A2"",CostGroup!A2,"")
    3: =IF(SUMIF('Detailed Budget'!$D:$D,'Budget Summary'!$B4,'Detailed Budget'!W:W)>0,SUMIF('Detailed Budget'!$D:$D,'Budget Summary'!$B4,'Detailed Budget'!W:W),"")

    4: =IFERROR(IF(VLOOKUP(A2,'Detailed Budget'!$A$2:$H$501,8,FALSE)0,VLOOKUP(A2,'Detailed Budget'!$A$2:$H$501,8,FALSE),""),"")

    5: =IFERROR(IF(OR(NOT(H3=""),NOT(I3="")),H3+I3,""),"")

    6: =IFERROR(IF(M$2:M$501"",ROUND(SUMIFS(M$2:M$501,$A$2:$A$501,VLOOKUP(A4,'Detailed Budget'!$A$2:$A$501,1,FALSE))/(SUMIFS(N$2:N$501,$A$2:$A$501,VLOOKUP(A4,'Detailed Budget'!$A$2:$A$501,1,FALSE))),2),""),"")

    7: =IFERROR(IF(OR(NOT(Q4=""),NOT(R4="")),Q4+R4,""),"")

    Please give the detail of each formula if possible.

    Thanks

  5. Sohaib says:

    Please send me to my email if possible.
    Thanks

  6. Satya says:

    Hello,

    I've a macro which written on excel 2010 vba.
    Now my system is upgraded to MS-Office 2013.
    This macro which is written in excel 2010 vba is not working..

    Please provide the solution if you can..

    Thanx in Advance.
    Satya

  7. Avijit says:

    Nothing visible after enabling micros in excel..no other micros output.

  8. Juzer says:

    I want to put date in Column A and want in same column as soon as date is entered it should give me number of days till date , how to make it possible through VBA Excel code

  9. Andrew says:

    When I get to the place to enable all macros all of these options are grayed out. Is there a way to make them visible?

  10. Chittaranjan says:

    After opening Excel sheet, a security warning message came that : Macro have been disabled. Enable Content

    My question is.. I have to highlight particular cell to yellow ex. cell A1. Earlier(before Enable content) it was while background. After enabling content A1 cell should be highlighted to yellow.

    Can this be possible by macro ?
    Please help me and suggest

  11. Lorraine says:

    Hi,
    Please help me.

    I created an excel 2013 file consists of macros and saved it as Add-in so I can customize them and pin in Menu bar. Unluckily, excel did not detect the macros in the add-in. Can someone help me. Thanks.

  12. Zubair says:

    Hi
    Can anyone help me ?
    I'm unable to use the colorindex formula in excel 2013
    Even I enable macro

  13. Chuck says:

    I have a user with a laptop. I deployed an Excel file onto that laptop. Even when I set the macros to be enabled always and the file is coming from a trusted location, Excel doesn't enable macros, nor does it even put up the button to do so. What other settings should I be looking at?

    Thanks!
    Chuck

  14. Chuck says:

    After further investigation, it appears that macros are enabled, it just won't run the Workbook_Open() routine. Hhhmmm...

  15. Choe says:

    Hi
    I've try to use SigmaXL at excel 2013, but don't know why it prompt out message " Cannot run the macro 'OpenHelpDataSetFolder/-ClickHandler' The macro macro may not be available in this workbook or all the macro may be diaabled.

    Can help me solve this issue please.

    Thanks

  16. Risa says:

    Hi,

    I've try to open macro enabled excel file and got notification to update link. Already click update link but my macro can't update the file. i still continue then the file can not run properly. Please advice.

  17. Muntaqim says:

    When I run my VBA Project it's show a massage box. "project is unviewable" what can I do now to view my project

    • Doug says:

      Muntaqim:
      It sounds like whoever built it locked it and you won't be able to open it. The builder doesn't want people to be able to see the code.

  18. Jack says:

    I can't run the macro even after enabling it. What to do?

  19. Kapil says:

    After downloaded and executed the exe, and after accepting the license terms, dialogue box comes and throws an error - can't open "Freefincal-Robo-advisory-template-final-June-20-2018-EXE-Version.xlsm" - error description "unknown".

    I am using MS excel 2013 version, enabled all macros already.

    Please help.

  20. leonard says:

    i did the.action about 'enable content' wrongly, it is not my aim, so how to reset the excel and show the worning again? and please send the comment to my mail box, many thanks in advance

  21. Pushparaj says:

    If I use the Vb codes and save as in required format my macro excel sheet is working fine. If I use the same in new another excel sheet the formula which I used in Vb codes previousely is not working. If I use the vb codes in one excel file it should be available in all new excel file. To get so what I should do please help me

  22. Patrick says:

    thank you for nice article. please can now do all my work for me and email it to my boss? i be at beach...bless your kindness.

  23. my name liha hong says:

    I have a problem with my computer and i want you to help. I open excel 2016 in data file .The cells are spread i cannot change because it is security and some time the computer get sruck can you have me with these .

  24. Martin says:

    My Microsoft Excel 2010 does not have the "Macro" setting option to select enable or disable.
    There is a macro file I'm trying to use but it has a yellow bar that says "content disabled" does not support running macros "PURCHASE".
    The trust center does not give me macro option to easily select enable.
    What can I do to enable the file?
    Do I need an upgrade? What upgrade would work?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard