1. Home
  2. MS Office

How To Create A Simple Macro In Excel 2010

Through Macros you don’t need to perform same task or action on same type of data. Macros are the built-in feature of Excel 2010, that enables user to record different tasks and apply them over on some another portion of the data. It works on the back-end, recording each and every mouse click, keystrokes that you do while performing the task. Down the line if you want to done with the task in a same way, you will just have to play the recorded Macro back.

Supposition: We have two worksheets in excel containing records of students; Name, Course, and Marks Obtained.

Macro sheet

Now we want to put a formula at E2 and F2 Position to calculate the grade according to the Marks secured and Status of the student respectively. While doing that we will be recording a macro for it to apply the same formula on other worksheet.

To start off with first we need to record a Macro. Navigate to View tab and Click Macros drop down button, and hit Record Macro.

macro location

You will reach Record Macro dialog, specify an appropriate name and hot-key for easier access. Hit OK to start recording.

formula

Now in Grade column we will going to put a formula that yields grade secured by the student, according to the marks. In Status column we will add a formula that shows the status of the student according to the Grade secured.

In Grade column at position E2, formula goes like this.

=IF(D2>=80,”A”, IF(D2>=75, “B”, IF(D2>=70, “B+”, IF(D2>=70,”B”, IF(D2>=65,”C+”, IF(D2>=60, “C”,”D”))))))

In Status Column at position F2, it would be like this;

=IF(F2=”A”,”Pass”,IF(F2=”B”,”Pass”,IF(F2=”B+”,”Pass”,IF(F2=”C”,”Pass”,IF(F2=”C+”,”Pass”,IF(F2=”D”,”Fail”))))))

fORMULA PUTTIN

The macro recorder running at the back-end recording all the actions, now find the cross symbol at the end of the both (Marks & Status) cells.  When you will drag down the cross symbol to the end of the respective columns, the formula will be applied to all corresponding cells.

stop macro

Now navigate to View tab, click Macros drop down button, and hit Stop Recording.

If you want to apply the macro which contains formula to evaluate Marks and Status on other portion of data or worksheet, go to the next work sheet of student records.

next sheet

Now Start the macro by either going to Macros drop-down options or by simply pressing hotkey allocated to it (Ctrl+P). you will see that the formula will be immediately applied over the Grade and Status columns.

macro table 2

Using macros can save a lot of time, especially when you’ve got to deal with huge amount of data by creating formula for the specific portion, applying conditional formatting, adjustment of cells, selection of specific piece of data, etc.

Leave a comment