Tutorial with Excel examples about Macros

Many people think that it is extremely hard to learn Excel Macros, but it is not. This article contains some nice and simple Examples to automate your daily tasks in Excel.

A Macro is a program in Visual Basic used to automate Office Tasks. In this sample I am using Excel 2010, but you can use 2007 version as well.

Create Macros using Record Macro Button

  1. To begin with click the View tab on the Ribbon. Choose the Record Macro from the Macros drop-down list:
    Choose the Record Macro from the Macros drop-down list
    You will get the Record Macro window.
    Excel Record Macro window
  2. Write the Macro name (it does not accept spaces or special characters), the shortcut key and select where you want to store your macro in in the corresponding field. Optionally you can write the description.
  3. Now, the macro is recording an action. For example you can write the value Hello in the A1 cell.
  4. Then go to the Macros icon and select the Stop Recording option.
    Go to the Macros icon and select the Stop Recording option

The macros you save are stored on the Macros button. When you click on it you will see the Macro window where you can select the one you need. Double click on the name to execute a command.

You can also associate a Macro with a button:

Click File -> Options -> Quick Access Toolbar category, select All Commands in the Choose commands from list, select Option Button (Form Control) and Click Add.

And assign a Macro to the button:

Click Option Button on the Quick Access Toolbar, and then draw the outline of the button on the sheet, select any cell in the worksheet. In versions of Excel that are for Microsoft Windows, right-click the option button, and then click Assign Macro.

For clauses

The next example shows how to work with the For clauses. The For clauses let us work with multiple values inside a loop. Let's see how you can get numbers from 1 to 5 in the cell A1 until the cell A5.

To do this go to the Developer tab -> Visual Basic. Double click on the Excel Object where you need the Macro to see the Microsoft Visual Basic for applications window. Enter the following:

Sub Macro1 ()
    For n = 1 To 5
        Cells(n, 1) = n
    Next n
End Sub

Click to save the file. To execute the macro you can go to View -> Macros -> select the name with the one you need from the list and click Run.

The following code sends the message Hello World in a windows message box:

Sub MacroName()
   MsgBox ("Hello World!")
End Sub

The following example is a yes/no message box. If you answer yes, the cell value is deleted.

Sub MacroName()
   Dim Answer As String
    Answer = MsgBox("Are you sure you want to delete the cell values ?", 
 vbQuestion + vbYesNo, "Delete cell")
    If Answer = vbYes Then
End If
End Sub

Let's verify the code. Select a cell and execute the macro with the code. You will receive the following message:

Run the macro to see this message

If you press yes, the selected cell values will be deleted. Otherwise, the values will remain.

The If sentence

You can also use the If sentence in Excel. In this code we are going to paint the cells according to the values. If the values are higher than 20 the value is red, otherwise, it is blue.

Sub MacroName()
   Dim CellValue As Integer
CellValue = ActiveCell.Value
If CellValue > 20 Then
 With Selection.Font
        .Color = -16776961
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight2 '
        .TintAndShade = 0
    End With
End If
End Sub

To test this code, let's select a cell with a value higher than 20:

Select a cell with a value higher than 20

When you execute the Macro it is painted red:

Execute the Macro and the value will be painted red

Otherwise, it is painted blue:

Run the Macro to see the value painted blue

The sentence case

You can also use the sentence case in order to do actions according to specific values. The following example shows a message according to the people's age.

Sub MacroName()
   Dim CellValue As Integer
CellValue = ActiveCell.Value
Select Case CellValue
     Case 60 To 200
         MsgBox "The person is old"
    Case 30 To 59
        MsgBox "The person is adult"
    Case 18 To 29
       MsgBox "The person is young"
    Case 0 To 17
    MsgBox "The person is a child"
    Case Else
    MsgBox "Unknown age"
End Select
End Sub

In order to test this example you just need to select a cell value and run the macro. For example, if the selected cell value is 44, the message displayed would be the following:

Do actions according to specific values


Macros are a great way to automate the Excel tasks. This article just shows some few examples of what you can do with it.

See also

Want to find more inventive tips, tricks and tutorials?

Please follow us on Google+:

48 Responses to "Tutorial with Excel examples about Macros"

  1. Jeffery Ijezie says:

    This information was very helpful, and the examples were great!

  2. farshad says:

    hello , This information was very helpful

  3. Ghufran ahmed khan says:

    Good examples and better description.

  4. Meher says:

    This Information is helpful.need some more details with examples.

  5. kunal says:

    thanks mate,, it ws a very useful post

  6. CPA says:

    Really helpful and a great starting point for someone like me who has programming experience in things like C++ but never used macros before

  7. Anonymous says:

    free downloading of examples must be facilitated

  8. John Craig says:

    Thanks for such an article. It is very helpful.

  9. Rajesh says:

    Good tutorials to learn basic/concept of macros

  10. Prashant Mirkar says:

    This examples are really good for macro learner. Thank you!!!

  11. ang says:

    how would you use excel to separate addresses, especially when it varies for example:
    123 Mily Way
    123 N Mily Way S
    123 E Mily Way

    I have tried the Find,Left, Mid functions and it does not work on splitting the address into columns.

    123 Mily Way
    123 N Mily Way S
    123 E Mily Way

    • Hemanth Kumar says:


      Folow the below steps to find out your output"

      1) Select the cell which you want to split the data
      2) Go the Data tab
      3) There you need to click on Text to columns button
      4) Then according to your requirements like with delimiters or fixed length keep on clicking on next button
      5) Once you think that all your requirements are ok, then click on finish button

      I think it will give you the solution. still if you have any questions, please send it to me

      Thank you

  12. Eric says:

    How do you use the If Sentence example on a range of cells?

  13. Masroor says:

    seems after reading and trying this post my fear is fading away for macros. thanks, was helpful

  14. vijayan says:

    i need more example and more vb codeing

  15. karthikeyan says:

    Please post more examples.....

  16. Gerry O'Brien says:

    This is one of the better examples showing how to build macro code. I have been exploring in Microsoft's web site for info on "how to build a macro" and the macro coding is really weird !!! I have programmed in Cobol and Fortran with some experience in SAS, but the coding in MS macro is truly weird !!! There is NO MANUEL OF CODING OR CODES WHETHER IN EXCEL, WORD OR POWER POINT !!

    I am not a dummy. I am an Economist who has a long experience with excel, word and power point and I give up !!!

    MS: Come up with a manual on macro codes to make the job manageable for users !!! whether it be for word, excel or power point, etc. !!! I only hope that such a manual will be free to users and that it will be updated over time. It can be posted in the MS web site for users.

    Gerry O'Brien
    Ottawa, Canada

  17. Rishi says:

    Can you please tell me some book that will help me to learn the very basics of Macros. I don't know anything about Macro. I am a BASE SAS certified and I am really very keen to learn excel Macros. Please tell me some books with which I can start up with.

  18. Soundarrajan says:


    i need to copy paste from multiple work book to one sheet.

    please provide the coding for it and send to my mail id

  19. Jk says:

    examples are really good thanks

  20. nagaraj says:

    nice examples

  21. sudhu says:

    Could you please explain the code for macro...for the below situation.

    the macro has to open the one excel file and copy the data till the end and same has to be past in the other excel file and after that it has to refresh the pivot table on the same excel file in the other sheet...

    It would a great help to me if explain line by line...and appreciate your efforts.

    Thanks in advance.

  22. prabhakar says:

    hi`` your examples and explanation is very good . i want introduction of macros and usage of macros please upload

  23. Saleem says:

    which book is best one to startup with excel macros?

    • Alexander says:

      There are plenty of books explaining the basics of macros and you can choose any to your liking to start with. You can simply go to a local bookstore, look though a few such books and choose the one you like best of all. The essentials of macros are the same, but each author presents them in a different manner.

  24. Kalaivanan says:

    Dear Alexander,

    Please provide the full screenshot it's better to learn because i don't know macro.


  25. Manibalan says:

    Please ignore

  26. Jaisy says:

    Helpful.. In a very simple way described the basic examples..

  27. Jaisy Johny says:

    More examples also will be more helpful.

  28. Muhammad bin Anis says:

    Its Awesome Man. :)

  29. Dhamu M says:

    Hi ,

    I want record tge timer in excel in the formate of 00:00:00 with the start and stop command button coul u please help me regarding that

  30. Sampada says:

    could you please explain me simple code to create pivot using macro, if you will give me for two pivot then also its fine.....

  31. SHAKKA says:

    I would like to know how to have cell (a1,b1,c1,d1) go from 0000 thru 9999 (each cell independently going from 0-9). If while going thru each number, if cell (k15) = 1 the program pauses. After extracting data I can press a key to continue. Thanks for you help in advance.

  32. Praveen says:

    Macros in excel how to find the party name or file

  33. I'm a person says:

    How do you execute a macro when you have more than one active cell. I have tried 'The If Sentence' example and it only works when you have one active cell. But when you are lazy like me and want it all to be done in one go, the macro doesn't work.

  34. avinash says:

    I want a macro which includes few steps like
    converting a few columns into separate set of rows
    then editing text to it and making it a command line

    i need this to make it an input to my scripts!!

    please help!!

  35. Freda Karu says:

    Iam still confuse about recording macros name.I need some advice on what names that Iam going to put down may you help me out please.??

  36. Ian says:


    How can I select the first names,middle name, family name, age and gender of the person using the macros in excel 2010? For example I will just select the person with the data above in the 100 plus people in excel 2010.

  37. Frank Iyaji says:

    Very wonderful examples. They have really opened my understanding of writing macro programs. Please keep at it. I will be visiting your site from time to time to learn more about Excel and programming with macros.


  38. mohanraj says:

    hi i am mohanraj i want some invoice macro for the entry in excel 2007

    if you want i can send you the format

  39. khan says:

    its really well.

Post a comment

Ultimate Suite for Excel Professionals
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Debra Celmer
Excel is at its best now
Annie C.
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard