If you use Excel a lot in the workplace or for personal purposes, then you probably have several day to day tasks that are quite tedious and repetitive.
Whether it’s importing and clearing data, or exporting and organizing according to customer needs, you often have to click the same buttons and enter the same information over and over again.
The good news is that most, if not all of what you can do with your mouse and keyboard in Excel can be replicated by Excel itself using macros.
In this article, I’ll show you how to create a simple macro to get started automating a repetitive task you might have.
Record Macro in Excel
Imagine you are getting a data fetch from a client where the fields are not centered. As part of your day to day work, you need to center the margins by clicking the cells in the top left corner and setting the text alignment to center.
Using a macro, you can record the action once and let the macro repeat the same set of commands for you. Here are the steps:
First, enable the Developer tab in Excel by clicking File Options Customize Ribbon Mark Developer and click OK.
Once it’s enabled, go to Developer Click Record Macro.
You can name your macro whatever you want (in this example I named it “CenterTextâ€) and click “OKâ€.
In addition, you will have the option to assign a keyboard shortcut to the macro, making it much easier to run it over and over again without going to the developer tab.
Also, the Save Macro To option is really useful if you want to create a generic macro that will work in any Excel workbook. Instead of choosing the default This Workbook, you can also choose Personal Macro Workbook, which will allow you to use the new macro in any future workbook that you open or create in Excel.
Then start your activity by clicking the cells and centering the fields with your mouse. The macro will record your clicks and the exact cell / icon you click in the Excel window and translates that into special code called VBA.
After that go back to the “Developer” tab and click “Stop Recording”.
Congratulations! You have recorded your first macro. The next time you have a new data extraction that has not been centered, you just need to go to Developer Macros Select Macro Name Run.
The macro will repeat the steps you recorded (for example, centering the text for all the cells in this exercise).
The final step is to save the file with a .xlsm extension (not the standard .xlsx) so that your Excel file will save the macros you recorded for future use. Remember that you do not need to do this if you selected Personal Macro Book when creating your macro.
Once you get the hang of it, a few hours of manual editing in Excel can be done in a much shorter time window using macros. By understanding that Excel can record repetitive actions and replay them, you can create automation as much as your imagination.
As a rule of thumb, anything you can accomplish with your mouse and keyboard can be repeated / automated many times faster with a macro. Enjoy!
–