In this tutorial, we will learn to record macros in Excel. Macros are used to record data in order to carry them forward to a new sheet or file. They can help you save a huge amount of time by enabling you to create a data structure or an entire database only once.
Don’t worry if you don’t understand how to use them because, by the end of this article, you will know what a macro is, how to record a macro, and of course, how to use them.
Also read: [Step-by-Step] How to Create a Flowchart in Excel?
What is a Macro in Microsoft Excel?
A macro, as mentioned above, is used to record activity in a sheet which can be run in a different sheet or file again without you having to repeat the activity.
For example, you’re an accountant whose job is to create ledgers and financial accounts, or balance sheets in Excel. You can simply start recording a macro, create the balance sheet structure once, and run that macro again on a fresh sheet or file to apply the same structure you previously created to save time.
There are a lot of such activities undertaken in Excel that involve the repetition of tasks that consume a huge amount of time and effort.
Learn to record and use macros with the help of this tutorial and stop wasting your time again!
Steps to Record Macros in Excel
Let’s get started with learning to record macros in Excel with the help of suitable images and examples.
1. Recording a macro in Excel
- Open an Excel file.
- Click on the record macro icon in the status bar below.
- Or, go to the View tab.
- Pull down on the Macros button and select Record Macro.
- A window named Record Macro opens.
- Name your macro in the Macro Name dialogue box, for example- Attendance Sheet.
- Set a unique shortcut key to run this macro in a new sheet or file, for example- CTRL+SHIFT+U
- In the Store macro in, select Personal Macro Workbook to apply macros in any new Excel file or sheet (Recommended).
- Select New Workbook to apply macros in a specific new Excel file.
- Select This Workbook to apply macros in a new sheet in the same file.
- Click OK to start recording.
- Notice the macro icon in the status bar changes to a square icon.
2. Preparing a data structure while recording macros
Now that a macro has begun recording, it means that whatever activity you do in that Excel sheet is being recorded.
Note- Make sure to start making changes you want to carry forward only after a macro has begun recording and not before or after that.
- Start preparing the data structure you want to record and carry forward.
- You can add border styles, repetitive headings, rows, text or cell colors, and various other formatting options.
- Here is a sample data structure that we created while a macro was recording.
3. Steps to stop recording a macro
To stop the recording, follow the steps below.
- Click on the square icon in the status bar.
- Or, go to the View tab.
- Click Stop Recording in the dropdown options under Macros.
4. Running a macro in a new file or sheet
After you create macros in Excel, it’s time to run this macro in a new file to check if it has properly recorded our data structure.
- Open a new Excel file or press CTRL+N.
- Press the shortcut keys you created to run macros. For example- CTRL+SHIFT+U.
- Or, go to the View tab.
- Pull down on the Macros and select View Macros or press ALT+F8.
- You should be able to see the name of the macros you created here.
- Click Run to run a macro.
Note- If Excel shows an error message on running macros in a new sheet, click Unhide in the View tab under the Window group. You can then run the macro either in the same workbook or in the new workbook named Personal that opens after clicking unhide.
You can see that the macro has run in a new Excel file successfully.
5. Deleting a macro in Excel
To delete a macro in Excel, do as follows next.
- Open an Excel file.
- Go to the View tab.
- Pull down on Macros in the Macro group.
- Select View Macros.
- Select the macro you want to delete in the Macro window.
- Click Delete to delete a macro.
You can see that the selected macro has been deleted permanently from Excel.
Conclusion
This tutorial was all about recording, running, and deleting macros in Microsoft Excel. Drop your comments below if you have any doubts regarding macros in Excel. Stay tuned for more informative tutorials like this!