How to Create a Macro In Excel

Automate your recurring tasks with simple macros

A laptop running a spreadsheet program.

rawpixel \ Unsplash

 

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

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

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, Excel in Office 365, Excel for Mac 2016, and Excel in Office 365 for 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 cannot see or use the macros. 

How to Display the Developer Tab in Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel in Office 365

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. 

  4. Click OK to add the Developer tab to the ribbon.

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

When you are ready to create a macro, start Excel and open a worksheet.

  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.

  5. Select This Workbook in the Store Macro In drop-down list. 

    Screenshot of Excel recording a macro.
  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 to open the Save As dialog box.

  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 Display the Developer Tab in Excel 2019 for Mac, Excel 2016 for Mac, and Excel in Office 365 for Mac

Before you try to add macros in Excel 2019 or 2016 on a Mac or in Excel in Office 365 for Mac, 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.

    Opening Excel preferences on a Mac
  2. Select Ribbon & Toolbar.

    Excel Preferences screen
  3. Select the Developer check box in the Main Tabs list in the Customize the Ribbon category.

    Ribbon and Toolbar selections
  4. Click Save.

How to Create a Macro in Excel 2019 for Mac, Excel 2016 for Mac, and Excel in Office 365 for Mac

When you are ready to create a macro, start Excel and open a worksheet.

  1. Go to the Developer tab.

  2. Click on Record Macro in the Code group. 

    Ribbon view with Developer tab
  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. 

    The Record Macro screen for name and shortcut
  6. Click OK.

  7. Perform all the formatting and commands you want to include. 

  8. Click Stop Recording on the Developer tab when you are finished. 

  9. Go to the File tab and select Save As or press ⇧⌘S (Shift+Command+S) to open the Save As dialog box.

    Save as menu option in Excel
  10. Enter a file name for the workbook.

  11. Select Excel Macro-Enabled Workbook (.xlsm) in the File Format drop-down list and click Save.

    Selecting the macro-enabled format in Excel

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

While there are multiple ways to run a macro you create 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 most natural to you.

To run a macro using a combination shortcut key, start by opening the worksheet containing the macro. Enter or select any data on which you want to apply the formatting or commands you included in the macro. Then, 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.

    Screenshot of Excel about to run a macro.

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.

    Excel ribbon showing Record Macro selection
  3. Click the name of the macro for which you want to assign or change the combination shortcut key. 

    Selection screen for editing macros
  4. Click Options to open the Macro Options dialog box.

  5. Type any lowercase or uppercase letter you want to use for the combination shortcut in the Shortcut key box and click OK.

    Editing screen for macro shortcuts