Excel is a great tool for automating repetitive tasks. The best way to do that is by recording a macro. Once you record it you can use it later for the repetitive tasks.
Although Macros are closely related to VBA, you don’t need any coding skills to create one. In this article, i guide you on how to create a Macro in Excel.
What is an Excel Macro?
An Excel Macro is a set of automated instructions or code written in the Visual Basic for Applications (VBA) programming language. It allows users to automate repetitive tasks, perform complex calculations, and manipulate data within Microsoft Excel. Macros can be recorded or written manually, enabling users to create custom functions and streamline Excel processes.
How to Create a Macro in Excel Using the Developer Tab
Example: In my workbook, I have 4 sets of data in 4 different sheets. The task is to create a chart for each data and customize it. Since the process for creating the charts in all the sheets is similar, it will involve a lot of repetitive tasks.
You can imagine how long it would take if you had more than 50 sheets to do similar tasks. This would take almost a whole day.
Thankfully, with Excel Macro, you can automate and complete this task within 5 Minutes.
How to record a Macro in Excel
1. Enable the Developer Tab:
- If you don’t see the Developer tab on the Excel ribbon, you need to enable it. To do this, go to File > Options.
- In the Excel Options dialog box, select Customize Ribbon.
- Check the box for “Developer” in the right-hand column.
- Click “OK” to save the changes.
- Open the Developer Tab:
Now that you’ve enabled the Developer tab, you should see it on the Excel ribbon. Click on it to access the developer tools.
2. Start Recording:
In the Developer tab, click on the “Record Macro” button. This will open the “Record Macro” dialog box.
3. Name the Macro:
In the “Macro name” field, give your macro a descriptive name. Avoid spaces or special characters in the macro name, and it should start with a letter.
4. Choose Where to Store the Macro:
You can store the macro in either “This Workbook” (accessible only in the current workbook) or in a “New Workbook” or in a “Personal Macro Workbook” (accessible in all workbooks). Select the appropriate option based on your needs.
5. Add a Description (optional):
You can add a description of the macro in the “Description” field to help you or others understand its purpose.
6. Assign a Shortcut Key (optional):
If you want to create a keyboard shortcut to run the macro, click in the “Shortcut key” field and press the key combination you want to use. Ensure it’s not already assigned to other Excel functions.
7. Perform the Actions:
Once you click “OK” in the “Record Macro” dialog box, Excel will start recording your actions. Perform the series of actions you want to automate in your worksheet. Excel will record every action you take.
8. Stop Recording:
After completing the actions, go back to the Developer tab and click on the “Stop Recording” button. This will end the recording process.
How to record a Macro In Excel using Shortcut
Adding the developer tab is a long process. Excel comes with a quick access menu at the bottom left corner that you can take advantage of to record a macro. Here are the steps
1. First, prepare your spreadsheet to record macro
2. Click on the record macro shortcut button on the left bottom corner next to ready
3. If you don’t see the Icon, click on the bottom bar and make sure it is ticked on the list
Once you tick it on the list, you can now record a macro by clicking on the Icon
4. You can now go ahead to record the macro, assign a shortcut, and perform the actions
How to Run a Macro
After recording a Macro, you can run it in two ways. The first is by using the shortcut that you assigned or by using the developer tab.
1. Click on the developer tab, then select Macros
2. Select the macro that you wish to run on the list
3. Lastly, click the run button
How to edit a Macro in Excel
Once you have recorded a macro, you can edit it by changing the VBA code. Here are the steps.
1. Click on the Developer Tab and Select Macros
2. Click on the Macro you wish to edit and select Edit
3. Now you can change the VBA code
How to delete a Macro
You can delete a Macro that you no longer need by following these steps
1. Open the developer tab then click Macros
2. Select the macro that you wish to delete
3. Click delete