If you find yourself repeating a certain task, you can create a macro in Excel to automate it.
To create a macro, you will have to record your keystrokes and mouse movements. You can find the feature to record macros from the Developer tab.
After you record your macro, you can edit it from the VBA editor window.
Recording Tasks to Create a Macro in Excel
Recording a macro in Excel is a three-step process. These steps include enabling the Developer tab, recording the macro, and then editing it.
Or, if you’re familiar with VBA you can directly enter your code without recording it in the first place.
Step 1: Enable Developer Tab
The Developer tab is hidden by default in Excel. Refer to the following steps to enable the Developer tab:
- Go to File > Options.
- Select Customize Ribbon from the panel to your left.
- Choose Main Tabs under Customize the ribbon.
- Select the box next to Developer.
- Click OK.
Step 2: Record a Macro
You can create and record your tasks as a macro to automate it on command. When recording your macro, make sure you perform only the tasks you wish to automate.
Here are the steps you can follow in recording a macro on MS Excel:
- Head to the Developer Tab.
- Select Record Macro from the Code section.
- In the Record Macro window, enter the following details:
- Macro name: Give your Macro a name to uniquely identify it.
- Shortcut key: Enter a shortcut that will call the Macro in the Excel workbook. Remember not to use an existing shortcut, as Excel will override that command to run the Macro.
- Store macro in: Choose where you want the recorded macro to work in.
- Description (optional): Although optional, you can add a meaningful comment in this section.
- Carry on with the action you wish to record as a macro.
- After you’re done, select Stop Recording on the Developer ribbon.
Step 3: Edit Your Macro
After you’re done recording your Macro, you may want to tweak a few things in the code. If you’re familiar with the Visual Basics for Application language, you can clean up your code.
- Select the Developer tab.
- Choose Macros from the code section.
- Choose your Macro, then select Edit.
- View the Visual Basic Code and make the necessary changes.
- Run the macro, then see if it works as you like.
How to Run a Macro in MS Excel?
There are two ways you can run your newly created macro.
Remember how we had the option to enter a key combination when creating our macro? You can use the combination as a shortcut to call the macro.
Similarly, you can also run your macro from the Developer tab in Excel.
- Open your Excel file.
- Select the Developer tab from the menu bar.
- Choose Macros from the ribbon in the Code group.
- Select the Macro you wish to run under Macro name.
- Click on the Run button.
How to Delete a Macro in MS Excel?
Macros tend to take up a lot of space. If you no longer need to use a Macro, you can quickly delete it. This way, you can save up some space in Excel to avoid your worksheet freezing.
If you wish to delete a Macro in MS Excel, follow these steps:
- Open your Excel workbook.
- Select the Developer tab.
- Click Macros from the Code section.
- Select your Macro from the box, then select the Delete button.
How to Save Macro-Enabled Workbooks
Macros extend Excel’s functionality. Therefore, you cannot save macros in a normal, .xlsx file.
You can save worksheets containing macros as a .xlsm file, which is a macro-enabled file format.
- Go to File.
- From the sidebar, select Save As > Browse.
- Navigate to the location you wish to save your file.
- Select the fly-out next to Save as type > Excel Macro-Enabled workbook (.xlsm)
- Click Save.