Hey Friends,I am back with another article on most easy and yet unpopular excel function-macros.Many of us as soons as hear the word “Macros” ,Only one thing comes into mind-Difficult.But friends believe me it’s too easy.Just keep on practice.So,Let’s get started
What is Excel Macro?
Macro is really great tool. It allows you to perform multiple operations like changing a cell value or opening a workbook,Formatting,Autotext etc. just by clicking a simple button. It enables you to work in a smart and efficient way. In terms of productivity, it is very productive as it reduces lots of manual work and gets the things done very fast.
How to add Developer Tab in Excel-2010 and 2007 Workbook?
Click the Developer tab on the Ribbon at the top of the screen. If the tab is not showing up on your screen, you can add it as follows:
1. For Excel 2010: click the File Tab then select options. On the left hand side of the Excel options dialog box, click Customize Ribbon. Then, on the right hand side box select Developer.
2. For Excel 2007: click the Microsoft Office Button 
, then Excel Options. In the Popular category, under Top Options for Working with Excel, select Show Developer Tab in Ribbon.
How to Change the security settings in Excel-2010 and 2007 Workbook?
In Trust Centre, Under Macro Settings, click Enable All Macros, then click OK.
Note: This security setting is not ideal for basic use. When you've finished creating your macro, go back and un-click Enable All Macros
How to Record Macro in excel
In this section you will learn, how to Record Macro in Excel. Follow the below Steps one by one to record macros in Excel:
1. Open one excel workbook. (Taken example of MS Excel 2007)
2. Go to “Developer Tab” in the ribbon.
3. Click Record Macro. You'll find it on the Developer tab under Code.
4. In Excel 2007 and 2010, Macro recording can be started by clicking on the Red Shape button marked in the bottom bar of the Excel in left hand side.
5. A “ Record Macro” dialog Box will open.
6. In the above dialog box, enter the suitable name for this Macro. You can also specify the shortcut key for running this particular recorded macro. It means on pressing that particular shortcut-key, this macro will automatically run.
7. You can give the location, where you want to store this particular macro. If you want it to be available whenever you run Excel, select Personal Macro Workbook and also you can write some description about this particular to be recorded Macro.Here I have selected “This workbook”
8. Now click OK and do some operation on the excel sheet. Like formatting of the cell or sorting some of the values etc. and click on the Stop button at the same place from where you started recording the Macro.
Before Starting record Macro
After Recording Macro
How to Run Macro in Excel?
You can Run this Recorded Macro by following below steps:
1. By Pressing Macros Button under developer tab or By Pressing Alt+F8.If while recording macro,you had specified shortcut key then pressing that shortcut key would run the macro automatically like “CNTRL+b“ in this example.
2. Select Your Macro from the Drop Down list
3. Click on Run
Note: To run this Macro, I have deleted the formatting done during the recording of this macro. Now once I will run this Macro, then automatically that formatting will be done in that Sheet.
Note: The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.
Note: Macro Enabled Excel workbooks are saved as .XLSM extension file.
Article is lengthy as I have tried to incorporate as many details as possible.Macros can be run through VBA but that’s not important for CA students.
Download the Practice Excel sheet here.
Download the Macro Enabled Excel sheet here.
Read My other articles on:
SUBTOTAL: Excellence in Excel Series- Part 1 using subtotals
SUMIF: Excellence in Excel Series- Part II
VLOOKUP: Excellence in Excel - Part III
For any Query,contact me @aggarwalankush0@gmail.com
CAclubindia
