Do you find yourself often performing repetitive tasks in Excel for your work? Not anymore with macros. Do it once and record it; the macro does it for you whenever you want. In other words, macros record your tasks and repeat them whenever you run them.
You might have used functions before to perform specific actions. If not, a function is a set of particular tasks to perform when called. Macros in Excel are built on this idea. They automate your tasks so that you get the most out of your time and resources.
To create a macro for your next task, you must first enable it. I will guide you on every step to enable macros in Excel for both Windows and Mac.
But before that, let’s see why you need macros with practical use cases.
What are Macros?
A macro is a feature in Excel that records your commands and tasks to playback on demand. Basically, it frees up a lot of time for you with automation.
Macros are built on top of VBA (Visual Basic for Applications), which is Microsoft’s programming language for coding in Excel. Therefore, macros can be created using VBA. However, if you are not technically sound, Excel allows you to create macros with just a few mouse clicks.
When you record a macro, the tasks are automatically translated into VBA code behind the scenes and bound to a set of key combinations, simply called custom shortcut keys. If you press that custom shortcut key in Excel, the macro associated with it will start functioning.
If you are bad at memorizing keys, don’t worry, Excel has a solution for that too. You can easily access created macros from the tabs present on Excel’s interface.
Why should we enable Macros in Excel?
By now, you may have a good understanding of what macros are. Here are some of the use cases to replace repetitive actions with macros:
Data Management: Data is what excel is meant for. We deal with a vast amount of data in Excel every day. Automating simple and routine tasks such as removing duplicates, deleting empty cells, performing mathematical calculations, and applying formulas or principles with macros can save us a lot of time and resources.
Macros can even turn raw data into customized formats. Therefore, data handling will be quick and seamless with macros.
Create Custom Functions: Excel comes with several built-in functions that can perform typical tasks. But they have limited capabilities; they cannot crack your complex ideas. Thus, with macros, you can create custom functions to implement your unique ideas.
Generate Insights: You can break down large data sets into informative tables, charts, graphs, or even dashboards with macros.
You should be familiar with pivot tables if you have worked with Excel for a while. If not, pivot tables are extracted from larger data sets based on conditions or grouping. You can effortlessly make these pivot tables with macros.
Workbook Management: Are you working with multiple worksheets and workbooks in a single project? If that’s the case, you may need to keep track of all the sheets in a workbook, save a specific worksheet, or rename a worksheet or workbook. You can simplify these tasks with macros.
Integration: When working on real-world projects, it’s common to use Excel with various tools and database management systems. Manually integrating Excel with these outside sources can be tedious and time-consuming. That’s where macros come in; you can record a macro to automate integration.
How to enable Macros in Excel on Windows?
Launch the Excel app and click on ‘options’ which you can find on the left sidebar.
A popup window like this appears on the screen. Navigate to Trust Center from the left pane.
Select Trust Center Settings.
You should notice one more window popping up with the Macro Settings option on the left panel. Click on it to open a menu with four options.
Your choice should be Enable all macros(not recommended; potentially dangerous code can run) and click the ok button visible at the end.
One of the most significant features of Excel is that macros are enabled on your Windows OS now.
How to enable Macros on Mac?
The steps to enable macros in Excel are similar and simpler in Mac. All you need to do is follow the steps below to turn ON macros on your Mac.
- Open a random Excel file on your Mac.
- You will now be able to select Excel, visible on the top menu bar of the screen. Click on it.
- Select Preferences from the dropdown menu.
- Now, click on the Security icon.
- You will find an option Enable all macros(not recommended; potentially dangerous code can run). Choose it to enable macros in Excel on your Mac OS.
Potential Security Risks of enabling Macros in Excel
Macros can significantly benefit you in saving time and resources when working with Excel, but they show up some security issues that demand attention.
You must deal with multiple security challenges when macros are enabled on your system. A few of them include the following:
- Do you know that when you download an Excel file from the internet or other sources, it could potentially contain malicious malware codes embedded in macros? They start acting on your system once you download them.
- Malicious code in macros can trick the security walls of your system and give unauthorized access to the intruder.
- Activated malicious code can leak your personal data, resulting in a privacy breach.
- Some dangerous macros may be intended to crash your machine.
Sometimes, the malicious software could be too big to fit in a macro. In such a scenario, a macro can contain a link that directs the system to the URL from where the malware software gets downloaded.
Moreover, hackers are always looking to steal or gain access to your computer, so they can use it as they wish.
So, If you download an installer from an unauthorized source or receive a spam document via email, you may be directed to follow specific instructions, and one of them could be to enable macros. In that case, the file you are using may have a damaging virus embedded in macros.
How to avoid the Security Risks of enabling Macros in Excel?
Disable macros when not needed: An easy solution to prevent your system from being compromised is just not to enable macros. To put it clearly, it’s best to keep macros disabled by default and only enable them when it’s absolutely necessary or for trusted files.
Check file extension: Usually, the Excel files are saved with .xlsx by default. The files with this extension are not capable of storing macros. However, the workbooks that contain macros are saved with the .xlsm extension.
So, it is important to verify file extensions before downloading or opening any file from untrusted sources.
Digitally signed macros: You may ask for a digitally signed notebook from the author to prevent yourself from falling into the trap of malicious macros.
‘Disable all macros except digitally signed macros‘ is one of the four options you will find in the process of enabling macros. Choose it to configure your Excel only to run the macros that are digitally signed so that you know that the running macro is from the intended source.
Update software: Using outdated software can expose your system to more vulnerable malware attacks. Therefore, keep your OS and Microsoft applications up-to-date with recent releases so that your machine becomes stronger at fighting malware.
Employ antivirus: Despite taking precautions, your PC may still be susceptible to malware introduced through macros. So, it is advisable to equip your machine with antivirus to challenge harmful hacks.
There are multiple paid and free antivirus software options in the market to protect your machine from exploits.
Wrapping up
You now know the advantages of enabling macros in Excel. However, there are security challenges that come with enabling macros, so I even provided you with some tips on how to tackle them.
If you are looking for more automation in Excel to finish your next project before time seamlessly, then check out these Excel add-ins.