Excel is common spreadsheet software which has eased our lives by giving us the capability to store data in sheets. If you have been using Excel for your data storage and processing needs then you will agree that there are many tasks that you do daily on the data collected in Excel files. Doing same tasks on different sets of data is monotonous and tedious. If you too have been executing same instructions on your data in routine then it’s time to learn how to record Macros in Excel and use them.
Macro is a tool in Excel that allows you to record all the commands given through your keyboard and/or mouse and save them by giving a suitable name for later use. This recorded macro can be replayed to apply the same formatting and data entry tasks any number of times you wish on different data. All you need to do is run the macro on a new worksheet or excel file and your daily task will be done in seconds.
Let’s take a practical problem
You are working in an educational institute and you need to present a result of students by following rules.
- Add the marks obtained by students in all the subjects and calculate their percentage
- For students who get more than 80%, color the result cell with Yellow
- For students who get between 60% and 80% color the result cell with Purple
- For students who get between 40% and 60% color the result cell with Blue
- For students who get below 40% color the result cell with Red
Step 1: Locate the Macro Tool in Excel
To create a macro, first you have to locate the tool. Click the View Tab in Excel 2007 or Developer Tab in Excel 2010. You will find a tab “Macro” with a small arrow at the bottom.
Step 2: Start recording the macro
When you click small arrow under Macros and click on the “Record Macro” option. You will see the following dialog box asking for a Macro name. You must give an appropriate name to the macro before recording it so that it gives a clue about what the macro will do to your data.
Step 3: Name the macro and start your instructions
Here, I have given the name of the macro as Display-Result since that’s what my macro is going to do. If you want you can assign a shortcut key to your macro if you are habitual of using shortcuts to do your work. Select one option for “Store macro to” from the following options:
- Personal Macro workbook
- This worksheet
- New worksheet
Personal Macro workbook is the preferred option that allows you to store a macro irrespective of the file so that the macro can be run in any file that you create afterwards. You must give a description of the macro as well to recall why you have created it!!
After you press the OK button in the Record Macro dialog box whatever you do in the Excel sheet will be recorded and stored in the macro. You can do formatting, apply a formula and anything that you wish to do with the stored data.
Step 4: Stop the Macro
Once you finish all the instructions and commands you want to store in the macro, click on the Stop recording option as displayed in the option in the image.
As soon you press the “Stop Recording” option, the macro will be stored, ready to be replayed any number of times.
How to run a recorded macro?
Whenever you need to execute the repetitive instructions on the data in another sheet or another Excel file, click on the macros option in view tab and a dialog box will open asking for the macro that you wish to run. All the macros you have already created under “Personal Macro workbook“or “This Worksheet” will appear here. If you have not created any macro for the present file only those macros created under “Personal Macro workbook” will be listed.
All you need to do is to select the required macro from the list and press “Run” button. All the instructions stored in the macro will be executed on the data that you have chosen in a new sheet. Thus, you will reduce your workload manifolds with the help of a recorded macro.
Note: When you decide to record macro make sure that it is generalized in functionality so that it can be used for a number of future tasks.
Practical Problem solved though macro
In the problem that was mentioned earlier after I entered the data, the macro Display-Result is chosen to run. The macro was created to calculate the percentage of all the students and display color coded result.
Before running macro
After running the macro
Use Macros and Stop Wasting Time
For those who value time and need to enhance productivity at work should use Macros since it eases the process of embedding the repetitive instructions in any complex data entry task. So Macros is an effective tool for excel users who use the spreadsheets extensively to store data.