How to Create a Simple Macro in Excel

All jobs and fields require some repetitive steps. Even the most creative jobs can usually require some monotonous tasks. Tools like Excel can help us automate some of those repetitive steps and that way makes our lives easier, so we can get the work done quickly and have more leisure time. In essence that is what Macros do; they’re shortcuts for repetitive tasks done within Excel. In this tutorial, I will show you how to create a simple macro that will memorize the steps involved in a simple repetitive task. We are going to be using fictitious information for the Training Department in a restaurant chain company called Happy Tummies. This company has restaurants in five major cities, so a great deal of training is done all year round. All restaurants are assigned a Training Manager – there are five Training Mangers in total. We’ll pretend you are the Training Specialist who works out of the Corporate Office located in Miami, FL and are responsible for analyzing and preparing reports on all the trainings completed. One repetitive task that you always perform is entering the names of all the Training Managers to prepare the daily training reports.

Let’s Get Started!

1. Go to Start>Programs>Microsoft office>Microsoft Excel to open the application. Then, go to Start>New to open a new spreadsheet. Go to File>Save as and save the new file before going further so you don’t loose any work.

Please Note: Macros require you to set a security level of low, medium or high. For the purposes of this tutorial, which is just to show you the simple basic steps to creating a macro, we are going to set the security level to medium since a security level of high will restrict the use of the macro.

2. Go to Tools>Macro>Security, and a pop up window named “Security” will appear. Select the Medium option under the Security Level tab and click OK to continue.

3. Go to Tools>Macro>Record New Macro.

4. A pop up window named “Record Macro” will appear. Here you will n
need to enter a name for the macro under “Macro Name” – I’m calling this macro “ht” (the initials of the company, Happy Tummies). Please note that the first character of the macro name should be a letter, then you can use any letter, number or underscore but with no spaces. Then, enter a symbol from the keyboard under “shortcut key” to be used as a shortcut to activate the macro – I’m entering “h” since it is the initial of the company’s name. Then, you need to select the placement of the macro under “Store macro in:” – I’m saving this macro in the “Personal Macro Workbook” so that it is available when you create new spreadsheets and want to use the macro. Finally, enter a brief description of the macro under “Description” and click OK to continue.

5. In this step, you will record the macro. You will see a tiny pop up window that will appear with a blue button – you can press this blue button at any time to stop recording the steps for the macro. Now, enter the five names of the training managers starting under column A, row 1 – The names are: Mary Smith, Thomas Spicler, Morgan Lopez, Samantha Davies and Nike Temples. Once you are done entering all five names, click the blue button to stop recording the new macro. You are done recording the macro, but please remember that they keyboard shortcut for this macro is Control Key + h, and that you saved the macro in your “personal macro workbook.”

6. In this step will test that the “ht” macro works. Go to File>New to open a new spreadsheet. Then, press the Control key and “H” key at the same to activate the macro. You will see that the names of the five Training Managers have appeared under column A. Please note that you can also go to Tools>Macro>Macros and select this macro from the “Personal Macro Workbook” to run it.

Well Done! You’ve learned some quick simple steps that will help you create macros. But, do keep in mind that macros can be more complex and powerful; so the more you learn about them and practice using them, the better you will get. I will you show more complex functionalities of macros in other separate tutorials.

If this tutorial has helped you, please consider donating.