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 the Excel spreadsheet configuration, formatting abilities, and formula functions is that you can easily perform repetitive tasks. Streamline those tasks further by using macros. Learn the basics of writing macros in Excel to become more efficient and improve your skills.

Instructions in this article apply to Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Office 365 for Mac, Excel 2019 for Mac, and Excel 2016 for Mac.

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

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

  1. Go to the File tab, then select Options.

    File > Options in Excel
  2. In the Excel Options dialog box, select Customize Ribbon

    Customize Ribbon tab in Excel Options
  3. In the Customize the Ribbon list, go to the Main Tabs section, then select the Developer check box.

    Developer option in Excel
  4. Select OK to add the Developer tab to the ribbon.

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

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

Macros cannot be created or run in Excel Online. However, Excel Online opens workbooks containing macros. You can make changes to worksheets and save workbooks in Excel Online without affecting macros.

  1. Go to the Developer tab.

  2. In the Code group, select Record Macro.

    Record Macro button in Excel
  3. In the Macro Name text box, enter a descriptive name for the macro.

    Macro name field in Excel
  4. Enter a shortcut key for the macro.

    Shortcut key in Record Macro dialog
  5. Select the Store Macro In drop-down arrow and choose This Workbook.

    Store macro in This Workbook field in Excel
  6. Select OK.

  7. Perform the formatting and commands you want to include in the macro. 

  8. Select Stop Recording when you're finished. 

    Stop Recording button in Excel
  9. Go to the File tab, then select Save As. Or, press F12.

    Save As menu item in Excel
  10. In the Save As dialog box, enter a file name for the workbook.

    Naming the file in Excel
  11. Select the Save as Type drop-down arrow, choose Excel Macro-Enabled Workbook, then select Save.

    Excel Macro-Enabled Workbook option

How to Display the Developer Tab in Excel for Office 365 for Mac, Excel 2019 for Mac, and Excel 2016 for Mac

Before you add macros in Excel for Office 365 for Mac or in Excel 2019 or 2016 on a Mac, display the Developer tab on the ribbon. By default, the Developer tab is not visible.

  1. Go to Excel and choose Preferences.

    Preferences for Excel on Mac
  2. Select Ribbon & Toolbar.

    Ribbon & Toolbar Preferences icon
  3. In the Customize the Ribbon section, go to the Main Tabs list and select the Developer check box.

    Developer checkbox in Excel
  4. Select Save.

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

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

  1. Go to the Developer tab.

  2. In the Code group, select Record Macro.

    Record Macro buton in Excel for Mac
  3. In the Macro Name text box, enter a name for the macro.

  4. In the Shortcut key text box, type the lowercase or uppercase letter you want to use.

  5. Select the Store macro in drop-down arrow and choose This Workbook.

    This Workbook dropdown in Mac Excel
  6. Select OK.

  7. Perform the formatting and commands you want to include in the macro. 

  8. When you're finished, go to the Developer tab and select Stop Recording

  9. Go to the File tab and select Save As. Or, press ⇧⌘S (Shift+Command+S).

    Save As... menu item in Excel for Mac
  10. In the Save As dialog box, enter a file name for the workbook.

  11. Select the File Format drop-down arrow, choose Excel Macro-Enabled Workbook (.xlsm), then select Save.

    Excel Macro-Enabled Workbook (.xlsm) dropdown menu in Excel for Mac

How to Run a Macro

When you want to run a macro you created in Excel, either use the shortcut you assigned to the macro or run the macro from the Developer tab.

To run a macro using a combination shortcut key, open the worksheet containing the macro. Enter or select the data on which you want to apply the formatting or commands included in the macro. Then, press the key combination assigned to the macro.

To run a macro from the Developer Tab, open the worksheet containing the macro, then follow these steps:

  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.

    Developer tab in Excel
  3. In the Code group, select Macros.

    Macros button in Excel Developer tab
  4. In the Macro dialog box, choose the name assigned to the macro, then select Run.

    Run button in Excel Macro dialog

How to Change the Macro Shortcut Key

To add or change a combination shortcut key for a macro:

  1. Go to the Developer tab.

  2. In the Code group, select Macros.

  3. In the Macros dialog box, select the name of the macro for which you want to assign or change the combination shortcut key.

  4. Select Options.

  5. In the Macro Options dialog box, go to the Shortcut key text box, type the lowercase or uppercase letter to use for the combination shortcut, then select OK.