How to Remove Macros from an Excel Workbook: A Complete Guide for Beginners

Last updated on April 20, 2023 By Victor Chan

Macros are powerful. They can automate tasks and enhance your productivity in Excel. But they can also be dangerous. They can drag down your Excel performance, screw up your calculations, or even erase your files. I've seen it happen. A friend of mine opened a workbook that had a hidden macro that wiped out all his data. He was crushed.


That's why you need to know how to remove macros from Excel and how to shield your workbooks from unwanted or harmful macros. In this article, I'll teach you how to do that. You'll learn how to:

  • View and edit macros in Excel
  • Delete all macros by saving in .XLSX format
  • Selectively delete macros from an Excel workbook
  • Stop macros from running automatically in Excel
  • Protect your Excel workbooks from macro viruses

And we’ll also meet Code Crusher, a special agent who works for the Excel VBA Enforcement Division (EVED). His job is to hunt down and destroy rogue macros that create havoc and mayhem in the Excel world.


Are you in? Let's go!

1. What are Macros and Why You Might Want to Remove Them from Microsoft Excel

Macros are sequences of commands or actions that you can record or write in Excel using Visual Basic for Applications (VBA), a programming language. Macros can perform tasks that are repetitive, complex, or time-consuming, such as formatting data, creating charts, or generating reports.


You can create your own macros or use macros that are built-in or provided by others. You can also assign macros to buttons, shapes, or keyboard shortcuts to run them easily.


However, not all macros are useful or safe. Some macros can be annoying, unnecessary, or outdated. For example, you might have recorded a macro for a specific project that you no longer need, or you might have received a workbook with a macro that does something you don't want.


Some macros can also be dangerous, especially if they come from unknown or malicious sources. Macros can access and modify your files, settings, and system information.


They can also run other programs or scripts that can harm your computer or steal your data. These are called macro viruses and they are a type of malware.


Therefore, it's wise to remove macros from Excel if you don't need them or if you don't trust them. This will help you avoid potential issues and keep your workbooks

2. How to View and Edit Macros in Excel

Before you remove macros from Excel, you might want to view and edit them first. This will help you understand what the macros do and how they work. You might also want to modify or improve some macros instead of deleting them.


To view macros and edit them, you need to use the Visual Basic Editor (VBE), a tool that allows you to write and edit VBA code. Learn more in our ultimate beginner’s guide to how to edit macros in Excel.

3. How to Remove All Macros by Saving in .XLSX format

Code Crusher is a special agent who works for the Excel VBA Enforcement Division (EVED). His mission is to track down and eliminate rogue macros that cause havoc and destruction in the Excel world. He has a unique ability to detect and disable macros with his powerful code-crushing skills.


Sometimes you may want to share your workbook with others who don’t need the macros. Or maybe you just want to give your workbook a fresh start.


What’s a fast way to get rid of all macros in a workbook?


Save your workbook as an .XLSX file. It’s like a magic wand that makes all the macros disappear.

“You need to kill all the macros. Fast. Simple. One shot. Save the workbook as XLSX. That's the only way. XLSX can't handle VBA code. It wipes it out. No mercy. No trace. Save and done!” - Code Crusher

You can only store Excel VBA macro code in .XLSM, .XLSB, and the older .XLS format.


By saving your workbook as an .XLSX file, Excel automatically removes all the macros for you. How cool is that? You can always save a copy of your original workbook with macros if you want to keep them for later.


To save your workbook as XLSX, follow these steps:

  • Open the workbook that contains macros.
  • Click on File > Save As.
  • Choose a location to save your file.
  • In the Save as type drop-down list, select Excel Workbook (*.xlsx).
  • Click on Save.

You will see a prompt that warns you that the workbook contains features that are not compatible with the selected file format. These features include macros, user forms, ActiveX controls, and user-defined functions.


Click on Yes to continue saving the workbook. This will remove all the macros from your workbook.


This way, you will have two versions of your workbook: one with macros and one without macros.


Saving your workbook as XLSX is a quick and easy way to remove all macros from your workbook. However, it may also affect other features that you may want to keep. Therefore, you should use this method only if you are sure that you don't need any macros or other features in your workbook.

4. How to Manually Remove All Macros from an Excel Workbook

Sometimes you may want to remove macros from an Excel workbook manually. This can be useful if you want to have more control over which macros you delete or if you don't want to use the XLSX format.


However, this method can also be tedious and time-consuming, especially if you have many macros in your workbook. You also need to be careful not to delete any modules or code that you may need later.


In this section, I’ll show you how to manually remove all macros from an Excel workbook using two different ways: deleting them from the Macro dialog box or deleting them from the VBE window.

“You can manually delete macros from Excel. It's not easy, but it's effective. You need to be careful and thorough. Don't leave any trace of the macros behind.” - Code Crusher

To delete macros from an Excel workbook, follow these steps:

  • Open the workbook that contains the macros
  • Go to the Developer tab on the ribbon and click on the Macros button in the Code group. This will open the Macro dialog box that shows all the macros in the workbook. Or press ALT+F8.
  • Select one of the macros you want to remove and click on the Delete button. This will delete the selected macro and remove it from the list.
  • Repeat this step for all the macros in the workbook until none are left, or you have selectively deleted the ones you don’t want.
  • Then make sure you delete macros in User Forms. These don’t show up in the Macro dialog box, so you need to go into the VB editor. See below.
  • Click on the Close button to close the Macro dialog box.
  • Save and close the workbook.

Alternatively, you can also remove all macros from an Excel workbook by deleting all the code modules that contain them. This way, you can delete multiple macros in one go, if they are all stored in a single code module.


To do this, follow these steps:

  • Open the workbook that contains the macros and go to the VBE window (ALT+F11)
  • In the Project Explorer pane, right-click on a module with macros you want to delete and select Remove ModuleName from the context menu. This will delete the module and all the macros in it.
  • You can choose to export the module before deletion. If you don’t need a backup, select “No”.
  • Repeat the above step for all the code and class modules in the workbook until you’re left with the modules you want to keep.
  • Then look inside all Worksheet objects (e.g. Sheet1) and the ThisWorkbook object to check for more macros. You need to delete the actual VBA in the code windows because these macros aren’t stored in separate code modules.
  • Finally, look for VBA code in User Forms. See below.
  • Save and close the workbook.

5. How to Delete Macros in User Forms

Code modules, workbooks, and worksheets aren’t the only places where Excel can store VBA macros. To get rid of all unwanted macros in a workbook you’ll also need to look in User Forms.


Follow these steps:

  • Press Alt+F11 to open the Visual Basic Editor.
  • In the Project Explorer pane on the left side of the VBA editor, look for UserForms. If you don't see the Project Explorer, click View > Project Explorer in the menu.
  • You may need to expand the workbook's folder by clicking the "+" icon next to it.
  • Locate the UserForm object (e.g., UserForm1) under the "Forms" folder.
  • Double-click the UserForm object to open its code window, or right-click and select View Code.
  • Manually delete the UserForm macros you don’t want to keep.
  • Save and close the workbook.

6. How to Prevent Macros from Running Automatically in Excel

Code Crusher has advice for you. As a special agent whose job is to track down and eliminate rogue macros, he’s seen some nasty stuff in his time. You can get rid of all the macros you don’t want, or you can take preventive action.

“You have two choices: either you crush the macros that bother you, or you stop them from running in Excel in the first place.” - Code Crusher

One of the ways you can protect yourself from macro attacks is to prevent them from running automatically in Excel. This can give you more control over what macros you want to run and when. It can also save you from some unpleasant surprises, like losing your data or getting infected by a macro virus.


But there's a trade-off. By preventing macros from running automatically, you might also miss out on some useful or beneficial features that macros can provide. For example, some macros can automate tasks, enhance your user interface, or customize your Excel settings. Luckily, you’re always just a setting away from enabling macros again if you so choose.


So how do you prevent macros from running automatically in Excel?


Let me show you how.

  • Go to the File tab on the ribbon and click on Options.
  • In the Excel Options dialog box, click on Trust Center in the left pane and then click on the Trust Center Settings button in the right pane.
  • In the Trust Center dialog box, click on Macro Settings in the left pane and then choose one of the following options in the right pane:
  • 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)
  • Click on OK to save your changes and close the dialog boxes.

Read “How to Enable and Disable Macros in Excel” for more in-depth information.

7. How to Protect Your Excel Workbooks from Macro Viruses

Code Crusher here again. I'm back to warn you about another threat that you need to watch out for: macro viruses. These are nasty pieces of code that use macros to infect and damage your computer or data. They can spread like wildfire through Excel workbooks that contain malicious macros.

Macro viruses can ruin your day in many ways. They can delete your files, send spam emails, or download other malware. They can also hide in your workbooks and infect other computers that open them.


But don't worry. There are ways to protect your Excel workbooks from macro viruses. You just need to follow some simple rules and precautions. Let me tell you what they are.

  • Use the macro security settings in Excel as explained in the previous section. Choose the option that best suits your needs and preferences.
  • Don't open or run any Excel workbooks that come from unknown or suspicious sources. If you receive an Excel workbook as an email attachment or a download link, verify the sender's identity and intention before opening it.
  • Scan your computer regularly with antivirus software that can detect and remove macro viruses. Make sure your antivirus software is up to date and has the latest virus definitions.
  • Backup your important files and data regularly. This will help you recover them in case they are corrupted or deleted by a macro virus.

Conclusion: How to Remove Macros from Excel and Keep Your Data Safe

You've learned a lot in this article. You've discovered how to remove macros from Excel and how to stop them from running automatically. I've also given you some tips on how to shield your Excel workbooks from macro viruses.


Macros are powerful tools that can automate tasks and enhance your productivity in Excel. But they can also be dangerous if you don't use them right or if you get them from shady sources.

“Remember: macros can be your friends or your foes. It's up to you to decide which ones you trust and which ones you bust.” - Code Crusher

That's why it's crucial to know how to remove macros from Excel and how to protect your workbooks from unwanted or harmful macros. By following these steps, you'll be able to keep your data safe and sound.


I hope you enjoyed this article and found it useful and fun. If you have any questions or feedback, please drop them below. I'd love to hear from you!


And if you want to learn more about Excel VBA and Office Automation, check out my other articles on this topic:

Thank you for reading and happy coding!😎


Author: Victor Chan

Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.

Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.

Connect on YouTube, LinkedIn, Twitter.


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.