Creating A Simple Macro In Excel (Step-By-Step)

How to make a simple macro

Creating a simple macro in Excel is not a daunting task.

Now it may seem contradictory, but you do not need to learn Visual Basic for Applications (VBA) to record simple macros in Excel.

VBA does however, come in handy if you would like to write complex macros or edit existing macros.

What is a Macro?

Macros are blocks of code, which assist with automating tasks. The word macro is short for macroinstruction. You can create a simple macro by using the Macro recorder, in which case Excel generates the VBA code for you.

A simple macro, created with the Macro recorder should contain a series of steps that tells Excel how to accomplish a certain task.

 

A Useful Analogy When Conceptualizing Macros

Let’s say every day, a student makes his favourite omelette for breakfast. He first beats three eggs in a bowl. He then adds some butter to a frying pan and waits until the butter is melted.

He pours the eggs into the frying pan. He lets the eggs cook for a little bit. He then adds spinach, garlic and five sundried tomatoes to the eggs.

He lets this mixture cook for a little while and then slides the omelette onto a plate when ready.

Now let’s say this student doesn’t want to make breakfast for himself every morning. He wants someone else to make it for him, so that he can save time in the morning when he gets up, since his favourite omelette is ready.

Now in this context, you can think of a macro like a recipe in a way. Excel is the chef (seasoned professional) following the recipe, in order to create the omelette that this student would usually have to make for himself.

 

Advantages/Disadvantages of Macros

Let’s review some advantages and disadvantages of using macros in your workbooks.

 

Diagram showing the advantages and disadvantages of using Macros in your workbooks

 

Due to the potential security risk, by default macros are not enabled. When opening a workbook containing macros, you will see a Security Warning.

Click on the Enable Content Button in order to enable macros in your workbook.

 

Screenshot showing the Security Warning and the Enable Content button

 

Creating a Simple Macro in Five Easy Steps

The scenario:

In our example, every week an office worker receives an Excel workbook. The workbook contains four worksheets with the weekly sales data of interest, for each salesperson.

 

Screenshot showing the source data. This is the first worksheet for the first salesperson.

 

Screenshot showing the source data. This is the second worksheet for the second salesperson.

 

There are a series of things she has to do formatting wise and it is the same for each sheet:

  • She has to change the font size of the text in cell A1 to 14, and change the colour of the text in cell A1 to blue.
  • She then has to select all the cells in the worksheet and AutoFit the Column Width.
  • She then has to select cells B6:F7 and put all the borders around and inside cells B6:F7.

 

You can read about how to format text in Excel here and how to add borders to cells in Excel in this post.

Since all the worksheets have the same structure and the steps are simple, she can record a macro to do this for her instead of doing it manually every time for each sheet.

  • Note: In order to set up a macro, you first have to make sure that you can see the Developer Tab on the Ribbon.

 

To do this, go to:

  • File and select Options. The Excel Options Window should appear.
  • Choose Customize Ribbon.
  • On the right hand side, in the Main Tabs section check Developer.

 

Screenshot showing how to add the Developer Tab to the Ribbon by using the Excel Options Window and checking the Developer box.

 

  • Click Ok.
  • The Developer Tab should now be added to the ribbon.

 

Screenshot showing the Developer Tab added to the Ribbon.

 

The Steps:

1)    So on the sheet called JaneSmith select cell H10.  Go to the Developer Tab on the Ribbon (Step 1 in the image). In the Code group, click on Record Macro (Step 2 in the image).

 

Screenshot showing the Record Macro option in the Code group on the Developer Tab on the Ribbon

 

2)    The Record Macro Dialog Box will appear.

 

Screenshot showing the Record Macro Dialog Box

 

Enter a name for your macro, which in this case is StandardFormattingRequired.

  • Note: When naming a macro, remember that the name cannot contain spaces. The macro name also has to start with a letter.

 

Assign a shortcut key, use Ctrl-Shift-B in this case.

  • Note: When assigning a shortcut key, do not use existing common shortcuts such as Ctrl-S, Ctrl-Z, Ctrl-A and Ctrl-C or else Excel will overwrite these shortcuts.

 

Choose where you would like to store your macro.

  • Note: You have three options. If you choose This Workbook, you can use the macro in the current workbook you are working in.
  • However, if you choose New Workbook, Excel will store the macro in another new workbook.
  • If you choose your Personal Macro Workbook (personal.xlsb) then your macro will be available in any workbook that you open in Excel.

 

We will choose This Workbook.

 

Enter a description for your macro. This is optional but helpful if you are sharing your workbook with other colleagues. In this case, type “This macro applies the standard formatting needed.”

 

Screenshot showing the Record Macro Dialog Box, with a Macro Name entered, the Shortcut key assigned, the storage assigned and a description added.

 

Click Ok.

 

3)

  • Now select cell A1 and change the font size to 14. Change the text colour to blue.
  • Select all the cells in the worksheet by pressing the Select All button. Then go to the Home Tab and in the Cells group, click Format. Choose AutoFit Column Width.
  • Select cells B6:F7 and go to the Home tab, and in the Font group, select the drop down arrow next to Borders. Select All Borders.

The result is shown below.

 

Screenshot showing the result of the formatting.

 

4)   Once you’ve completed the formatting go to the Developer Tab on the Ribbon (Step 1 in the image), in the Code group click on Stop Recording (Step 2 in the image).

 

Screenshot showing the Stop Recording Option in the Code group on the Developer Tab on the Ribbon.

 

5) Now save the file by going to File and clicking on Save As.  Once you have selected a location. Give your workbook a name.

 

Instead of saving the workbook as a standard Excel workbook, choose Excel Macro-enabled Workbook in the Save as type: section and click Save.

Screenshot showing the Save As Window with the Save as type: Excel Macro-Enabled Workbook highlighted.

 

  • Tip: Remember that Excel records all keystrokes and clicks, once you start recording. So it’s advisable to think about what you want to do first, so that Excel does not record unnecessary clicks.

For example, let’s say you changed the font colour to orange first and then blue. Excel would record this and while it won’t affect the functionality of your macro, there will be unnecessary lines of code generated.

Macros can even be created for you to highlight duplicate values in cells.

 

Running a Simple Macro

We want to see if the macro we just created works.

1)    So go to the sheet called JohnAbrams.

 

Screenshot showing the second sheet in the workbook with the sales data from the second salesperson John Abrams

 

2)    On the Developer Tab (Step 1 in the image), in the Code group click Macros (Step 2 in the image).

 

Screenshot showing the Macros option in the Code group on the Developer Tab on the Ribbon.

 

You should see the Macro Dialog Box.

The name of the macro we just created should be there. With this macro selected. Click Run.

 

Screenshot showing the selection of the StandardFormattingRequired macro, and highlighting the run button.

 

Now this sheet should be formatted in exactly the same way that the previous sheet was.

 

Screenshot showing the result of running the macro StandardFormattingRequired on the second sheet.

 

3)    Now go to the sheet called AlisonWillis and this time press the shortcut key we assigned to the macro which was Ctrl-Shift-B.

 

The result is shown below.

 

Screenshot showing the result of pressing the assigned shortcut key Ctrl-Shift-B on the third sheet.

 

Other Ways to Trigger a Macro

  • You can use a form control button to trigger a macro. This is useful if you are sharing your workbooks with other people since it makes your worksheets more user-friendly.

 

Let’s see how this works.

1)    Go to the sheet called IanScott.

 

Screenshot showing the fourth sheet called IanScott.

 

2)   On the Developer Tab on the Ribbon (Step 1 in the image), click on Insert (Step 2 in the image) in the Controls group.

 

Screenshot showing the Insert Option highlighted in the Controls group on the Developer Tab

 

3)   Under Form Controls, select Button.

 

Screenshot showing the Form Controls option with Button highlighted.

 

4)   Click on any place on the worksheet.

5)   The Assign Macro Dialog Box should appear.

 

6)   Select the name of the macro which you’d like to assign to the button, which in this case is StandardFormattingRequired. Click Ok.

 

Screenshot showing the Assign macro dialog box with the StandardFormattingRequired Macro selected.

 

7)   Ensure Design Mode is on.

 

Screenshot showing Design Mode On

 

You can resize the button by dragging the edges. Type the text “Apply Standard Formatting” on the button.

 

Screenshot showing the resized button with the text Apply Standard Formatting added

 

Now make sure Design mode is not selected.

8) Click on the button we just created and the macro will run.

 

Screenshot showing the results of pressing the button we created which has the StandardFormattingRequired macro assigned.

 

  • You can also run a macro from the Quick Access Toolbar.

 

1)   To do this, go to File and then choose Options. Choose Quick Access Toolbar.

 

2)   Scroll down to View Macros.

 

Screenshot showing the View Macros option

 

3)   Click Add.

Screenshot showing the View Macros option.

 

4) Click Ok.

 

5) Go to the button on the Quick Access Toolbar and click to View Macros and then run the macro from there.

 

Screenshot showing the View Macros button on the Quick Access Toolbar

 

Tips for Creating Simple Macros

  • Know what you would like to accomplish upfront so that Excel does not generate unnecessary lines of code.
  • Once you are comfortable with creating macros use relative references rather than absolute references. This means that the keystrokes are recorded based on a cell’s starting position.  The macro will not use exact locations, which is useful if anything changes in the structure. Read more about absolute, relative and mixed references here.

 

Learning Objectives

Let’s review the skills you have learned:

  • You know what a macro is.
  • You have learned to think about the logical steps involved in creating a simple macro.
  • You have learned how to record your own simple macro.
  • You have learned how to run a macro.
  • You have learned how to assign a macro to a button.
  • You have learned how to assign a macro to the Quick Access Toolbar.
  • You know how to save Excel workbooks as macro-enabled workbooks.

 

Conclusion

Macros are extremely useful for speeding up your work flow in Excel and increasing productivity.

2023 update – Recent developments in AI mean that basic macro building can now be carried out by Excel but complex macros will continue to need to be created manually. For more details, see our AI in Excel survey.

If you would like to continue your VBA journey and learn about how to create more complex macros, then please see our Microsoft Excel VBA course which will get you up to speed with VBA.

Special thank you to Taryn Nefdt for collaborating on this article!

Looking for something a little different? Check out our AutoCAD for architects guide here!

 

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.