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.

Advertisement
  • Mike

    I would appreciate a little help, firstly I would like to point out that I’m a total newby to the creation and saving of Macros.

    I believe I have created a Macro in a Workbook.
    Each day I clean out the workbook of the previous days data.
    I then drop in the new days data and run the Macro.
    All works well and I get the desired result.

    My question is:

    Is there a way that I can run the Macro without having to clean out the data.

    Comments appreciated.

    • Jayme

      Sure mate. Create a Macro where it deletes previous data. After that, drop in new one and run a second macro. This, of course, if you can’t manage to have the macro deleting the previous data and inserting new one at once.

  • Samuel Billie

    good.Its working

  • KRISHNA

    I would very much appreciate,please clear my this dount,
    In my marco if the student get more than 80 but less than 100 then he get grade “A”
    KRISHNA

  • swaroop

    can we write a macro to automatically load data to a front end from excel so that it could be loaded to database
    plz help
    regards
    swaroop

  • lolol

    lol

  • Navz

    Need your help in automation using prodiance add in in excel..

  • Mahesh

    The formulas are not correct…could you please correct the same ?

  • Sekhar

    I have a pivot table taken from connection ‘A’ and I have slicers taken from connection ‘B’. Through macros I need to connect these two. So, that when ever we change the slicer, the pivot table values are changed dynamically using a refresh button. what macro code is required for this…?

  • Leo

    The second formula is wrong , you used F2 instead of E2 but it still helped me a great deal!!!
    Thanks