How to Create Excel Macros

Automate your recurring tasks with ease

A laptop running a spreadsheet program.

rawpixel \ Unsplash

 

One of the benefits of Excel's spreadsheet configuration, formatting abilities and formula functions is you can easily perform repetitive tasks. Here's how you can streamline those tasks even further by using macros.

Excel macros can be quite advanced, but don’t let this idea intimidate you. Learning the basics of writing macros in Excel can help you become more efficient and improve your skills using the application.

This article covers three versions of Excel for Windows and the 2016 version of Excel for the Mac.

Macros in Excel Online

Unfortunately, you cannot create or run macros in Excel Online. You can open a workbook containing macros, make changes and save it again without affecting the existing macros, but you will not be able see or utilize the macros. 

How to Create a Macro in Excel 2016, Excel 2013 and Excel 2010

Screenshot of Excel recording a macro.

The Record Macro dialog box and Developer tab

Before you try to add macros in Excel, you need to display the Developer tab on the ribbon. By default, the Developer tab is not visible. 

  1. Go to the File tab and select Options.
  2. Select Customize Ribbon in the left pane. 
  3. Select the Developer check box under Main Tabs in the Customize the Ribbon list on the right. 
  4. Click OK. The Developer tab appears.

When you are ready to create a macro, start Excel and open the worksheet in which you want to use it to begin.

  1. Go to the Developer tab.
  2. Click on Record Macro in the Code group. 
  3. Enter a name for the macro in the Macro Name box.
  4. Enter the shortcut key you want to use for the macro. All macro shortcut keys begin with Ctrl. You can press and the Shift key when typing the shortcut key you want to use, which will create a Ctrl+Shift shortcut. This is helpful, as so many keyboard shortcuts are already in use in Excel.
  5. Select This Workbook in the Store Macro In drop down list. 
  6. Click OK.
  7. Perform all the formatting and commands you want to include. 
  8. Click Stop Recording when you are finished. 
  9. Go to the File tab and select Save As or press F12. The Save As dialog box will open.
  10. Enter a file name for the workbook.
  11. Select Excel Macro-Enabled Workbook in the Save as Type drop down list and click Save.

How to Create a Macro in Excel 2016 for Mac

Before you try to add macros in Excel, you need to display the Developer tab on the ribbon. By default, the Developer tab is not visible. 

  1. Go to Excel and choose Preferences
  2. Select Ribbon & Toolbar.
  3. Select the Developer check box in the Main Tabs list in the Customize the Ribbon category.
  4. Click Save.

When you are ready to create a macro, start Excel and open the worksheet in which you want to use it to begin.

  1. Go to the Developer tab.
  2. Click on Record Macro in the Code group. 
  3. Enter a name for the macro in the Macro Name box.
  4. Type any lowercase or uppercase letter you want to use in the Shortcut key box.
  5. Select This Workbook in the Store Macro In drop down list. 
  6. Click OK.
  7. Perform all the formatting and commands you want to include. 
  8. Click Stop Recording when you are finished. 
  9. Go to the File tab and select Save As or press ⇧⌘S (Shift-Command-S). The Save As dialog box will open.
  10. Enter a file name for the workbook.
  11. Select Excel Macro-Enabled Workbook in the Save as Type drop down list and click Save.

How to Run a Macro in Excel 2016, Excel 2013, Excel 2010, Excel for Office 365 and Excel 2016 for Mac

Screenshot of Excel about to run a macro.

The Macro dialog box

While there are multiple ways to run a macro you created in Excel, the two most straightforward methods are using the shortcut you assigned to the macro and running a macro from the Developer tab. Both are effective, so use the one that seems easiest to you.

To run a macro using a combination shortcut key, start by opening the worksheet containing the macro.

  1. Enter any data on which you want to apply the formatting or commands you included in the macro. 
  2. Press the key combination you assigned to the macro.

To run a macro from the Developer Tab, start by opening the worksheet containing the macro.

  1. Enter any data on which you want to apply the formatting or commands you included in the macro. 
  2. Go to the Developer tab of the ribbon.
  3. Click Macros in the Code group. The Macro dialog box opens.
  4. Select the name you assigned to the macro and click Run.

Excel Macro Tips

You can add or change a combination shortcut key for a macro at any time.

  1. Go to the Developer tab.
  2. Click Macros in the Code group to open the Macros dialog box.
  3. Click the name of the macro for which you want to assign or change the combination shortcut key. 
  4. Click Options. The Macro Options dialog box will open. 
  5. Type any lowercase or uppercase letter you want to use for the combination shortcut in the Shortcut Key box and click OK.