# 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.

View in gallery

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.

View in gallery

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

View in gallery

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”))))))

View in gallery

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.

View in gallery

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.

View in gallery

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.

View in gallery

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.

• 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.

• 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

• 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

• Vangala Sandeep

Hi
I am totally new to macros and I need some help.
How do I write a macro to open a workbook in some shared location on my local and hide few columns on the opened workbook ?
Would be of great help, if I would get an answer to this.
Thanks
Sandeep